Recientemente tuve que automatizar la creación de gráficas en todas las solapas de un libro de Microsoft Excel, así que he decidido publicar el “cómo lo hago” mediante #VBA. El fichero Excel de trabajo requiere que todas las solapas sean “tablas“, en mi caso, proceden de consultas a una base de datos, y cómo eran muchas solapas y una tarea repetitiva, que mejor que automatizarlo.
Para completar la tarea, primero crearemos una función para trabajar con todas las solapas, y después aplicaremos una función a todas ellas.
Solapas de un libro
Empezamos por conocer las solapas de un libro Excel, para poder aplicar la rutina a todas.
Public Sub Trabaja()
Dim i As Integer, sNomb As String
For i = 1 To Sheets.Count
sNomb = Sheets.Item(i).Name
Sheets(sNomb).Select ' Seleccionamos solapa
EliminaColumnasSinDatosGrafica
Next
End Sub
La rutina Trabaja es la que se encarga de ir seleccionando todas las solapas, y aplicarle el mismo procesamiento EliminaColumnasSinDatosGrafica.
Para conocer las dimensiones de la tabla, haremos uso de las funciones ColumnaProcesar y FilasProcesar, ya explicadas en la entrada anterior Dimensiones de tablas en VBA Excel.
Eliminar columnas sin datos
Cómo el resultado de la consulta, a veces implica que haya columnas sin datos, vamos a incluir una pequeña función que detecte que la columna no tiene datos a procesar. Es necesario que busquemos la última fila a procesar, para todas las columnas. Para ello, volvemos a utilizar FilasProcesar. Si el número es bajo, entonces eliminamos dicha fila.
Para eliminar una fila, creamos la siguiente función:
Public Sub EliminaColumna(ByVal numCol As Integer)
Columns(numCol).Select
Selection.Delete Shift:=xlToLeft
End Sub
Código de crear gráfica
Para crear la gráfica, tenemos que seleccionar los datos, por lo que para conocer el rango en la forma que la gusta a Excel, requerimos la función de conocer la letra de una columna, utilizando el código ASCII de los caracteres, para lo que requerimos de CHR y de ASC:
Public Function Letra(ByVal numCol) As String
Letra = Chr(64 + numCol)
End Function
Por último, el código para generar la gráfica de un modo muy simple es:
Range("A1:" & Letra(numCols) & numRows).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'" & nombresolapa & "'!$A$1:$" & Letra(numCols) & "$" & numRows)
ActiveChart.ChartType = xlLine
Y la función completa es:
Public Sub EliminaColumnasSinDatos(nombresolapa)
Dim numCols As Integer, numRows As Integer, intI As Integer, numR2P As Integer
numCols = mFunciones.ColumnaProcesar(1, 1, 1)
numRows = mFunciones.FilasProcesar(1, 1, 1)
For intI = numCols To 2 Step -1
numR2P = mFunciones.FilasProcesar(1, intI, 1)
If numR2P < kLIMITE_Elimina_COLUMNA Then EliminaColumna (intI)
Next
numCols = mFunciones.ColumnaProcesar(1, 1, 1) ' Recalcula columnas tras eliminación
If numCols > 1 Then
Range("A1:" & Letra(numCols) & numRows).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'" & nombresolapa & "'!$A$1:$" & Letra(numCols) & "$" & numRows)
ActiveChart.ChartType = xlLine
End If
End Sub
Aquí os dejo el fichero xlsm Excel para crear gráficas automáticamente para que lo utiliceis, y si teneis alguna duda o mejora, me escribís un email o dejarme un comentario!