Sin categoría

Actualización selectiva de tablas dinámicas de Excel conectadas a cubos de datos

Dentro de las características de Inteligencia de Negocio (BI) incluidas en Excel para el análisis de información, nos encontramos con la posibilidad de tener varias tablas dinámicas conectadas a diferentes cubos de datos, tanto en una misma hoja como repartidas en diversas hojas de un mismo archivo, permitiéndonos de esta forma disponer de múltiples vistas sobre nuestro sistema de información de forma centralizada.

Cuando los cubos que están conectados a dichas tablas dinámicas se procesan, existe la posibilidad de actualizar de forma independiente una de estas tablas mediante la opción Actualizar, perteneciente al grupo de opciones Datos, de la pestaña Analizar en la cinta de opciones de Excel; o bien usar la opción Actualizar todo, en el mismo grupo, que actualizará todas las tablas dinámicas existentes en el archivo Excel.

Sin embargo puede haber situaciones en las que solamente queramos actualizar algunas de las tablas dinámicas que componen nuestro archivo Excel, por lo que resultaría de gran ayuda disponer de un mecanismo que nos permitiera seleccionar las tablas y actualizarlas en un solo paso, evitándonos el tedioso trabajo de tener que recorrer el archivo hoja por hoja para actualizar independientemente cada tabla.

La finalidad del presente artículo será precisamente esa: el desarrollo mediante VBA y el sistema de macros de Excel de una pequeña utilidad, que ofrezca al usuario una lista de las tablas dinámicas existentes en el archivo Excel, permitiéndole seleccionar las que quiere actualizar, y realizando dicha operación de actualización en un único paso.

Para ello necesitamos en primer lugar algunos cubos de datos con los que realizar esta prueba, así que emplearemos las bases de datos AdventureWorksDW2012 y ContosoRetailDW, disponibles en CodePlex y el centro de descargas de Microsoft, creando con cada una de ellas un proyecto en SQL Server Data Tools de tipo Analysis Services Multidimensional, a los que daremos los nombres AdventureWorks y ContosoRetail respectivamente. Cada uno de estos proyectos contendrá un par de sencillos cubos de datos, tal y como muestra la siguiente figura.

ActualizacionSelectivaTablasDinamicasConectadasCubosDatos_01

 

A continuación crearemos un nuevo archivo Excel, al que añadiremos varias tablas dinámicas repartidas en diferentes hojas, conectadas a los cubos que hemos desarrollado. Este archivo, al que daremos el nombre ActualizarTablasDinamicas.xlsm, lo guardaremos con formato de Libro de Excel habilitado para macros.

Para identificar adecuadamente las tablas dinámicas que necesitemos actualizar en el proceso que vamos a desarrollar, asignaremos a cada una de ellas un nombre de la siguiente manera: posicionados en una tabla dinámica, en la pestaña de nivel superior Herramientas de tabla dinámica de la cinta de opciones, seleccionaremos la pestaña Analizar y desplegaremos la opción Tabla dinámica (primera opción a la izquierda en esta pestaña) que mostrará un cuadro de texto en el que escribiremos el nombre para dicha tabla, como vemos en la siguiente figura.

ActualizacionSelectivaTablasDinamicasConectadasCubosDatos_02

 

Seguidamente, en la pestaña Desarrollador de la cinta de opciones, dentro del grupo Código, haremos clic en la opción Visual Basic, entrando en el entorno de desarrollo de VBA.

ActualizacionSelectivaTablasDinamicasConectadasCubosDatos_03

 

Si la pestaña Desarrollador no estuviera visible tendremos que ir a la pestaña Archivo y hacer clic en Opciones. En la ventana Opciones de Excel haremos clic en Personalizar cinta de opciones, y pasaremos la pestaña Desarrollador desde la columna Comandos disponibles en hasta la columna Personalizar la cinta de opciones, lo que hará visible a esta pestaña.

ActualizacionSelectivaTablasDinamicasConectadasCubosDatos_04

 

Volviendo a la ventana principal de VBA, seleccionaremos la opción de menú Insertar | UserForm, para añadir a nuestro archivo Excel un formulario al que daremos el nombre frmActualizarTablasDinamicas, y en el que agregaremos los controles que vemos en la siguiente figura.

ActualizacionSelectivaTablasDinamicasConectadasCubosDatos_05

 

Para iniciar este formulario añadiremos ahora un módulo mediante la opción de menú Insertar | Módulo, en cuyo editor de código escribiremos el siguiente procedimiento.

 

Sub ActualizarTablasDinamicas()

Dim ofrmActualizarTablasDinamicas As frmActualizarTablasDinamicas
Set ofrmActualizarTablasDinamicas = New frmActualizarTablasDinamicas
Load ofrmActualizarTablasDinamicas
ofrmActualizarTablasDinamicas.Show

End Sub

Este procedimiento quedará guardado como una macro del archivo Excel, que ejecutaremos haciendo clic en la opción Macros, disponible tanto en la pestaña Desarrollador como Vista de la cinta de opciones.

ActualizacionSelectivaTablasDinamicasConectadasCubosDatos_06

 

La siguiente figura muestra el formulario en ejecución.

ActualizacionSelectivaTablasDinamicasConectadasCubosDatos_07

 

Aunque nuestro formulario ya dispone de interfaz de usuario, todavía carece de la funcionalidad necesaria para realizar las tareas que necesitamos, por lo que entraremos en su editor de código y nos situaremos en el evento Initialize del objeto UserForm, que representa al formulario, y que se produce al iniciarse su ejecución, escribiendo el siguiente bloque de código.


Private Sub UserForm_Initialize()
Dim oSheet As Object
Dim oPivotTable As PivotTable
Me.lstTablasDinamicas.SpecialEffect = fmSpecialEffectSunken
Me.lstTablasDinamicas.MultiSelect = fmMultiSelectMulti
Me.lstTablasDinamicas.ListStyle = fmListStyleOption
For Each oSheet In ActiveWorkbook.Sheets
    oSheet.Select
    For Each oPivotTable In ActiveSheet.PivotTables
        Me.lstTablasDinamicas.AddItem (oSheet.Name & "-" & oPivotTable.Name)
    Next
Next
End Sub

Como vemos en el código anterior, después de configurar algunas de las propiedades del control ListBox utilizamos un bucle For Each para recorrer las hojas del archivo, y dentro de este, otro bucle del mismo tipo para obtener los nombres de las tablas dinámicas que pueda haber, con la finalidad de rellenar el ListBox con el nombre de la hoja y tabla dinámica.

ActualizacionSelectivaTablasDinamicasConectadasCubosDatos_08

 

Al hacer clic en el botón Actualizar, ejecutaremos el siguiente bloque de código para el evento Click de dicho botón, en el que recorreremos los elementos contenidos en el ListBox, y en aquellos en los que su casilla esté marcada, accederemos, a través de la colección PivotTables del objeto ActiveSheet, a la tabla dinámica correspondiente para actualizarla mediante el método Refresh, perteneciente a la propiedad PivotCache de la tabla dinámica.


Private Sub cmdActualizar_Click()
Dim nIndice As Integer
Dim sElementoLista As String
Dim aHojaTabla() As String
Dim sHoja As String
Dim sTabla As String
For nIndice = 0 To Me.lstTablasDinamicas.ListCount – 1
    If Me.lstTablasDinamicas.Selected(nIndice) Then
        sElementoLista = Me.lstTablasDinamicas.List(nIndice)
        aHojaTabla = Split(sElementoLista, "-")
        sHoja = aHojaTabla(0) 
        sTabla = aHojaTabla(1)         
        ActiveWorkbook.Sheets(sHoja).Select
        ActiveSheet.PivotTables(sTabla).PivotCache.Refresh
    End If
Next
MsgBox "Actualización completada"
Unload Me
End Sub

Si realizamos cambios en las tablas de hechos de los cubos desarrollados para este ejemplo, como pueda ser la inserción de nuevas filas, y después de procesarlos ejecutamos el formulario, marcando algunas de las casillas del ListBox; al hacer clic en el botón Actualizar observaremos cómo dichas tablas seleccionadas actualizan sus valores.

ActualizacionSelectivaTablasDinamicasConectadasCubosDatos_09

 

En el caso de que no queramos realizar la actualización, haciendo clic en el botón Cancelar cerraremos el formulario.


Private Sub cmdCancelar_Click()
Unload Me
End Sub

Y de esta forma concluimos este pequeño truco, que esperamos pueda resultar de utilidad para todos aquellos que necesiten trabajar con diferentes vistas de cubos de datos dentro en un mismo archivo Excel.

4 Comentarios

  1. kowal

    Muy buen artículo! Por favor siga enviando más trucos Luis. Saludos!

  2. lmblanco

    Hola kowal

    Muchas gracias por tu interés en el artículo, celebro que te haya parecido interesante.

    Un saludo,
    Luis

  3. Muchas Gracias por su ayuda, voy ha probarlo

Deja un comentario

Tema creado por Anders Norén