23 octubre, 2021

CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNIÓN

Hola a todos:

Hace unos días recibí una consulta acerca de cómo agrupar la información de varias hojas en una hoja específica del mismo libro.

Aunque tengo publicaciones con macros para agrupar información de varios libros,  el método utilizado fue mediante bucles (For – next). Para esta petición utilizaré otro método, trabajaré con ADO y realizaré una consulta de UNION para consolidar la información de las hojas seleccionadas.

Dado que el usuario tendrá siempre la misma estructura en la información de esas hojas y además serán tres, esta macro será perfecta para este trabajo. Con ADO conseguiremos mayor rapidez en la ejecución del código y, si cabe mayor claridad.

Veamos las tres hojas que vamos a consolidar, las llamaré TABLA1, TABLA2 y TABLA3 y la hoja que recibirá toda la información se llamará CONSOLIDADO:

La estructura sería esta:

CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNION

Tal como podéis ver, cada una de las tablas tiene la misma estructura que la que muestro arriba. Un ID, Nombre, Estudios, Inglés, Vehículo, Provincia y Edad.

Antes de poner la macro, me gustaría volver a incidir en que es imprescindible que las tablas tengan las mismas columnas con los mismos nombres. Si esto no es así, la macro no funcionará.

Ahora sí, ya podéis pegar el siguiente código en un módulo estándar:

Sub CONSULTA_SQL_UNION()
'Definimos las variables
Dim Dataread As ADODB.Recordset, obSQL As String, Res As String
Dim cnn As ADODB.Connection, i As Integer, MiLibro As String
'Limpiamos hoja que consolida, CONSOLIDADO
Eliminar = Application.CountA(Worksheets("CONSOLIDADO").Range("A:A"))
If Eliminar > 0 Then Worksheets("CONSOLIDADO").Range("A1:GG" & Eliminar).ClearContents
'realizamos consulta SQL de UNION y no tenemos en cuenta los registros nulos
obSQL = "SELECT * FROM [TABLA1$] WHERE NOT [TABLA1$].[ID] IS NULL UNION " & _
"SELECT * FROM [TABLA2$] WHERE NOT [TABLA2$].[ID] IS NULL UNION " & _
"SELECT * FROM [TABLA3$] WHERE NOT [TABLA3$].[ID] IS NULL"
'Guardamos el nombre del libro activo
MiLibro = ActiveWorkbook.Name
'Realizamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Procedemos a grababar los datos de la consulta
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
'Copiamos los datos a la hoja CONSOLIDADO
With Worksheets("CONSOLIDADO").Select
Worksheets("CONSOLIDADO").Cells(2, 1).CopyFromRecordset Dataread
End With
'Grabamos los nombres de cada encabezado de columna
For i = 0 To Dataread.Fields.Count - 1
If IsDate(Dataread.Fields(i).Name) Then
dfecha = CDate(Dataread.Fields(i).Name)
Else
dfecha = Dataread.Fields(i).Name
End If
Worksheets("CONSOLIDADO").Cells(1, i + 1) = dfecha
Next
End Sub

Antes de ejecutar el código debéis verificar que tenéis la referencia: Microsoft ActiveX Data Object 2.8 Library. En este archivo no es necesario que la marquéis (ya la he marcado yo), pero si pegáis el códido en un libro nuevo, sí que la tendréis que marcar.

exportar-una-tabla-o-consulta-de-access-a-excel-con-ado2

El resultado de aplicar la macro es el siguiente:

CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNION1

Y ya tenemos los registros de las hojas especificadas en la hoja CONSOLIDADO. En este código si existen duplicados, es decir dos filas o más idénticas, importará solo uno, y también si existen elementos nulos (null) no los tendrá en cuenta. Si queremos que tuviese en cuenta los duplicados, es decir, que los importase, debemos poner «ALL» después de UNION, de esta forma estaremos especificando que unimos todo (incluidos duplicados):

obSQL = "SELECT * FROM [TABLA1$] WHERE NOT [TABLA1$].[ID] IS NULL UNION ALL " & _ "SELECT * FROM [TABLA2$] WHERE NOT [TABLA2$].[ID] IS NULL UNION ALL " & _
"SELECT * FROM [TABLA3$] WHERE NOT [TABLA3$].[ID] IS NULL"

Obviamente, sobre los nulos, no vamos a querer que se cuelen filas en blanco o con caracteres extraños, por eso resulta imprescindible incluir en la sentencia el famoso Not Is Null 🙂

Y esto ha sido todo, os dejo el archivo de ejemplo:

Descarga el archivo de ejemplo pulsando en: CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNIÓN

Comparte este post

16 comentario en “CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNIÓN

    1. Hola Gustavo:

      Para que seguir vinculando hojas en la macro, tienes que hacer referencia a ellas en la estructura en la que ya aparecen reflejadas las anteriores, por ejemplo si quisieras incluir una TABLA4, sería así:

      obSQL = "SELECT * FROM [TABLA1$] WHERE NOT [TABLA1$].[ID] IS NULL UNION " & _
      "SELECT * FROM [TABLA2$] WHERE NOT [TABLA2$].[ID] IS NULL UNION " & _
      "SELECT * FROM [TABLA3$] WHERE NOT [TABLA3$].[ID] IS NULL UNION " & _
      "SELECT * FROM [TABLA4$] WHERE NOT [TABLA4$].[ID] IS NULL"

      Observa lo que he añadido y compáralo con la macro original y lo reproduces las tantas veces como hojas quieras poner. Y aunque lo comento en el post, no olvides que la estructura de las tablas (las columnas) han de ser las mismas.

      Saludos

    1. Hola Pablo,

      No merece la pena programar para obtener esta información, simplemente con una tabla dinámica aplicándola a la hoja final, te mostrará toda la información necesaria.

      Saludos,

  1. HOLA! Muy buena macro... Consulta: hay forma de aplicar un filtro a ciertos valores (por ejemplo, los que tienen 25 años o más) y copiar esa información, pegando como valores en la hoja consolidado?

    Gracias.

    1. Si claro, únicamente tienes que modificar la consulta SQL:

      obSQL = "SELECT * FROM [TABLA1$] WHERE [TABLA1$].[EDAD]>=25 AND NOT [TABLA1$].[ID] IS NULL UNION " & _
      "SELECT * FROM [TABLA2$] WHERE [TABLA2$].[EDAD]>=25 AND NOT [TABLA2$].[ID] IS NULL UNION " & _
      "SELECT * FROM [TABLA3$] WHERE [TABLA3$].[EDAD]>=25 AND NOT [TABLA3$].[ID] IS NULL"

  2. Gracias, excelente. Cuando hablas de ADO, que debo tener para que funcione? porque marca error al momento de poner mis datos. Marca error en la linea .OPEN

    1. encontre el error, no habia cambiado el campo ID. Me surge una duda, si es posible solo obtener algunos campos y no todos (select *), intente con ajustando el select, marca error la macro.
      obSQL = "SELECT [TABLA1$].[user_id] , [TABLA1$].[APLICA], [TABLA1$].[PERFIL], [TABLA1$].[ESTRUCTURA],[TABLA1$].[RESP_ESTATUS], [TABLA1$].[NOMBRE]

      lo puse para cada una de las tablas

Si te ha gustado o tienes alguna duda, puedes dejar aquí tu comentario.

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información.plugin cookies

ACEPTAR
Aviso de cookies