lunes, 11 de mayo de 2015

VBA: Consolidando datos con SQL dentro de Excel.

Un caso muy solicitado es conseguir mediante macros la unión de información procedente de diferentes rangos, para obtener en un solo origen todos los datos consolidados...
Hoy emplearemos el lenguaje de SQL para conseguir unir información de diferentes hojas.. en este caso la clave será la instrucción SQL: UNION ALL.
Obviamente conocer algo las estructuras de este lenguaje de programación ayuda mucho a construir nuestras macros en VBA para Excel.


El trabajo consistirá en generar una conexión sobre los diferentes rangos y aplicarle una sentencia de SQL que realice y mantenga una conexión entre los orígenes de datos.
OJO!! una vez lanzada nuestra macro, la conexión quedará hecha y viva, por lo que bastará Actualizar datos para refrescar nuestro resultado consolidado....


Supongamos el siguiente caso: disponemos información de nuestra empresa con datos del 2014 y 2015, cada conjunto de datos en una hoja distinta: 'Datos14' y 'Datos15':

VBA: Consolidando datos con SQL dentro de Excel.



El objetivo es conseguir toda la información en el mismo lugar, en la hoja 'Consolidado'.
Para ello insertamos el código de la función en un módulo estándar de nuestro proyecto de VBA desde el editor de VB.
Sin olvidar previamente habilitar la Referencia: Microsoft ActiveX Data Objects 6.x Library.

Sub QueryUNION_SQL_Excel()
'Abrimos el editor de Visual Basic y en el menú de Herramientas clic en Referencias
'añadimos: Microsoft ActiveX Data Objects 6.x Library.
Dim sConn As String
Dim sSQL As String
Dim oQt As QueryTable
Dim sh As Worksheet
Dim origen As String

'limpiamos la hoja destino
Sheets("Consolidado").Cells.ClearContents
'obtenemos la ruta completa y nombre del fichero de trabajo
'donde se encuentran los datos!!!
origen = Application.ThisWorkbook.FullName
'establecemos la Sentencia de conexión para ficheros de Excel
sConn = "ODBC;DSN=Excel Files;DBQ=" & origen & ";"
'escribimos el código SQL necesario para UNIR la información de ambas tablas
sSQL = "SELECT [Datos14$].[Fechas], [Datos14$].[Comercial], [Datos14$].[Unidades], [Datos14$].[Precio] FROM [Datos14$] [Datos14$]" & _
    " UNION ALL " & _
    "SELECT [Datos15$].[Fechas], [Datos15$].[Comercial], [Datos15$].[Unidades], [Datos15$].[Precio] FROM [Datos15$] [Datos15$]" & _
    " ORDER BY Fechas ASC;"

'Generamos la Tabla resultante en la hoja final 'Consolidado'
Set sh = Sheets("Consolidado")
Set oQt = sh.QueryTables.Add(sConn, sh.Range("A1"), sSQL)
'Refresca la conexión y vuelca los datos...
oQt.Refresh

End Sub



Tras ejecutar una sola vez nuestra macro QueryUNION_SQL_Excel podemos comprobar el resultado:

VBA: Consolidando datos con SQL dentro de Excel.



El resultado se comporta, a efectos de refresco, como cualquier otra Tabla...
Puedes comprobar que añadiendo nueva información en nuestros dos orígenes de información (Hojas Datos14 y/o Datos15) y presionando el botón de Actualizar (Ficha Datos > grupo Conexiones > botón Actualizar todo) los nuevos registros se incorporarán al resultante...

También podemos comprobar la acción realizada visualizando las Conexiones existentes:



En alguna futura entrada expondré la solución, con SQL, a la doble búsqueda.

2 comentarios:

  1. Hola que tal, si quisiera hacer la conexión con una autenticacion windows, que debería hacer. Gracias

    ResponderEliminar
    Respuestas
    1. Hola Diego,
      en teoría bastaría añadir a la cadena de conexión sConn las variables del usuario
      "User ID=excelforo;" & "Password=contraseña;"

      debería funcionar
      Saludos

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.