Dinamizando las tablas (V): Como crear una Tabla dinámica con Visual Basic

En esta entrada vamos a aprender a crear una Tabla dinámica con VisualBasic. He creado un programa sencillo con la base de datos de pesca que ya hemos utilizado anteriormente para analizarlo con detalle y así poder modificar lo que sea necesario en nuestra tabla en concreto.

En primer lugar tenemos que seleccionar el rango de celdas para introducir en la Tabla dinámica. Para eso utilizamos la orden Range().Select.

En nuestro ejemplo el rango seleccionado son las celdas A1 a E1494.

   Range(«A1:E1494»).Select

Para crear una tabla dinámica con VisualBasic tenemos que crear la siguiente línea de código:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ «Hoja1!R1C1:R1494C5″, Version:=xlPivotTableVersion12).CreatePivotTable _TableDestination:=»Hoja1!R3C6″, TableName:=»Tabla dinámica1», _DefaultVersion:=xlPivotTableVersion12

Ahora pasaremos a analizarla. El primer segmento:    ActiveWorkbook.PivotCaches.Create() es la función de Crear la tabla dinámica en el libro de Excel donde estamos trabajando. El libro activo.

Dentro de .Create() encontramos:

• SourceType:=xlDatabase, nos indica qué tipo de fuente se trata. Al ser datos de una tabla de Excel encontramos xlDatabase.
• SourceData:= «Hoja1!R1C1:R1494C5», nos indica donde se encuentran los datos a recoger en la tabla dinámica. En nuestro caso en la Hoja1 de las celdas A1 a E1494. Las celdas están representadas con el código fila, columna (Row, Column) R1C1 fila 1 columna 1 es A1 y R1494C5 es la fila 1494 y columna 5 que se corresponde a E5
• Version:=xlPivotTableVersion12; recoge la versión de tabla dinámica que se crea.

Con TableDestination:=»Hoja1!R3C6″, indicamos en que celda queremos que coloque nuestra tabla dinámica. En este caso será la celda F3, fila (R) 3 y columna (C) 6 de la Hoja1, que es la misma hoja donde tenemos los datos.

Para dar un nombre a la tabla dinámica que creamos utilizamos TableName:=»Tabla dinámica1″,

Por último tenemos otro indicador de la versión: DefaultVersion:=xlPivotTableVersion12

Ahora ya tenemos creada la tabla dinámica. Pero ahora debemos seleccionar que variables queremos colocar en las columnas y en las filas y que valores representar en la tabla dinámica:

Sheets(«Hoja1»).Select

Cells(3, 6).Select  

ActiveWorkbook.ShowPivotTableFieldList = True

Con estas tres órdenes de código indicamos que se coloque en la primera celda de la tabla dinámica (F3). Y que se active el listado de campos a introducir en la tabla dinámica.

Para colocar la variable Especie como etiquetas de filas debemos utilizar los siguientes comandos:

With ActiveSheet.PivotTables(«Tabla dinámica1»).PivotFields(«ESPÈCIE»)

   .Orientation = xlRowField

   .Position = 1

   End With

PivotFields() es el campo que deseamos colocar. Para que se coloque en las filas debemos poner en .Orientation = xlRowField , si solo colocamos una variable en las filas deberemos colocar un 1 en .Position.

Si quisiéramos colocar una segunda variable en las filas ocuparía la posición 2.

De forma equivalente para colocar una variable en las columnas debemos escribir la misma instrucción pero en .Orientation debemos colocar xlColumnField.

With ActiveSheet.PivotTables(«Tabla dinámica1»).PivotFields(«Port»)

   .Orientation = xlColumnField

   .Position = 1

   End With

Para colocar la variable de kilogramos (KG) en el interior de la tabla como la variable a calcular tenemos que escribir la siguiente instrucción:

     ActiveSheet.PivotTables(«Tabla dinámica1»).AddDataField ActiveSheet.PivotTables _

       («Tabla dinámica1»).PivotFields(«KG»), «Suma de KG», xlSum

El titulo con el que se introduce es “Suma de KG” (se corresponde con el parámetro .Caption) ya que se ejecuta xlSum (se corresponde con el parámetro .Function), el valor interior es la suma de los valores donde coincidan las etiquetas de las filas y de las columnas.

Una vez seleccionadas todas las variables. Escribimos la siguiente instrucción para hacer desaparecer la tabla con la lista de variables:

   ActiveWorkbook.ShowPivotTableFieldList = False

De esta forma podemos crear una tabla dinámica con VisualBasic. Espero que las explicaciones hayan sido lo suficientemente claras, igualmente si tenéis alguna duda, problema o sugerencia al respecto estaré encantado de escucharlas e intentar resolverlas.