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:
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.
El resultado de aplicar la macro es el siguiente:
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):
ALLobSQL = "SELECT * FROM [TABLA1$] WHERE NOT [TABLA1$].[ID] IS NULL UNION
ALL " & _ "SELECT * FROM [TABLA2$] WHERE NOT [TABLA2$].[ID] IS NULL UNION
" & _
"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
Excelente Macro e información, solo una duda, en el caso de que tenga en mi libro mas de 3 hojas como se le haria en el codigo para que las reconociera?
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
tienes algo asi, pero que lo que vaya en la hoja 4 sea un resumen, para saber cuantos hay por provincia, edad, etc
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,
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.
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"
Excelente. Gracias por compartir sus conocimientos.
Muchas gracias Edilberto. Saludos
Hola, hay alguna opcion de que pueda aparecer una columna indicando el nombre de la hoja de donde se pego la informacion??
Hola Alejandra:
Se podría hacer, pero es necesario hacer algunas modificaciones en la programación, si tengo tiempo lo desarrollaré.
Saludos
buen día. puedo hacer esto pero no de hojas del mismo libro si no a hojas de libros difrentes, para trer los datos a mi archivo central.
Hola Edward:
Tienes varios post que hacen lo que necesitas. En el buscador, indica «Consolidar» o «Agrupar» y encontrarás la información, Saludos
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
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
Hola Xochitl: Efectivamente, puedes traer campos independientes, según tus necesidades. Trabajas con SQL.
Saludos.