AGRUPAR INFORMACIÓN DE VARIOS LIBROS EN UNA HOJA EXCEL

En esta web ya llevo varios post dedicados al tema de agrupar o consolidar información en Excel, desde agrupar la información de varios archivos a una misma hoja a consolidar la información de todas las pestañas, estos son los post relacionados:

1 – CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNIÓN
2 – CONSOLIDAR INFORMACIÓN DE VARIOS ARCHIVOS EN UNA HOJA EXCEL
3 – CONSOLIDAR INFORMACIÓN DE VARIOS ARCHIVOS EN UNA HOJA EXCEL CON VBA

Y con este post de hoy creo que este tema quedará bastante documentado. Hace unos días, me solicitaron realizar una modificación en la macro del tercer post de la lista que acabo de comentar. La solicitud era que se pudiesen seleccionar los archivos que queremos agrupar en lugar de seleccionar una carpeta para agrupar todo el contenido. Aunque parezca un tema menor creo que es necesario comentar estas modificaciones.

El razón de realizar el post con la carpeta como fuente de los datos, fue por dos motivos: No hay muchas macros publicadas que lo hagan así y por otra parte, en procesos automatizados, es una solución más eficiente que tener que ir seleccionando los archivos. Pero, claro, se puede dar el caso que la información, precisamente, necesite ser agrupada seleccionando individualmente cada archivo que queremos agrupar.

Aprovecharé los archivos del post motivo de la consulta e introduciré algunos cambios en la programación.

Como siempre, imaginad que tenemos la siguiente carpeta con estos 3 archivos y queremos consolidar o agrupar toda la información en una hoja:

consolidar-informacion-de-varios-archivos-en-una-hoja-excel-con-vba1

Para hacerlo vamos a utilizar la siguiente macro:

Sub AGRUPAR_ARCHIVOS()
'Definimos variables
Dim i As Integer, j As Integer, FilaInicio As Integer
Dim iArchivo As String, nArchivo As String, MiLibro As String
Dim dir_Archivo As Variant
Dim iRango As Range, dRango As Range
Dim Hoja_Destino As Worksheet, iLibro As Workbook
'Creamos ventana de diálogo para seleccionar los archivos que queremos agrupar
On Error Resume Next
dir_Archivo = Application.GetOpenFilename(Title:="SELECCIONA ARCHIVOS PARA CONSOLIDAR", MultiSelect:=True, filefilter:="Excel files (*.xls*), *.xls*")
On Error GoTo 0
'Si no seleccionamos archivos, salimos del proceso
If Not IsArray(dir_Archivo) Then
Exit Sub
End If
'Si existen datos en la hoja AGRUPADO, los eliminamos
With ThisWorkbook.Sheets("AGRUPADO")
elimina = Application.CountA(.Range("A:A")) + 1
If elimina > 0 Then .Range("A1:A" & elimina).EntireRow.Delete
End With
'Iniciamos un for con para identificar los archivos seleccionados
If IsArray(dir_Archivo) Then
For j = LBound(dir_Archivo) To UBound(dir_Archivo)
nArchivo = dir_Archivo(j)
'Determinamos a partir de que fila vamos a consolidar los datos
FilaInicio = 1
'Desactivamos actualizacion de pantalla y eventos
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Identificamos el nombre de nuestro libro
MiLibro = ThisWorkbook.Name
'Indicamos la hoja de destino de los datos que queremos consolidar
Set Hoja_Destino = ThisWorkbook.Sheets("AGRUPADO")
'Listamos los archivos Excel a consolidar
iArchivo = nArchivo
'Si la longitud del archivo es cero, salimos del proceso (no existe archivo para consolidar)
If Len(iArchivo) = 0 Then Exit Sub
'Si el nombre del archivo no es igual a nuestro libro seguimos el proceso
If Not iArchivo = MiLibro Then
'Capturamos ruta al iarchivo
Set iLibro = Workbooks.Open(Filename:=nArchivo)
'Contamos las hojas que tiene
fin = iLibro.Sheets.Count
'Iniciamos un bucle por cada hoja, donde seleccionamos los datos desde la fila 2
'hasta el final de la hoja (siempre sin filas en blanco)
'Luego copiamos los datos en la Hoja_Destino, que es la Hoja "AGRUPADO"
'Colocándolos al final de los rangos que se vayan pegando
For i = 1 To fin
iLibro.Sheets(i).Select
Set iRango = iLibro.Sheets(i).Range(Cells(FilaInicio, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
Set dRango = Hoja_Destino.Range("A" & Hoja_Destino.Cells(Rows.Count, 1).End(xlUp).Row + 1)
iRango.Copy
With dRango
.PasteSpecial xlPasteValues
.PasteSpecial xlFormats
End With
Next
'Cerramos el libro y continuamos el proceso
Application.CutCopyMode = False
iLibro.Close False
End If
Next j
End If
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
' Una vez finalizado, lanzamos mensaje de finalización.
MsgBox ("EL PROCESO HA FINALIZADO CORRECTAMENTE"), vbInformation, "PROCESO DE CONSOLIDACIÓN"
End Sub

Como podéis ver, hemos realizado algunas modificaciones en la macro anterior, entre ellas el tipo de cuadro de diálogo para seleccionar los archivos, con el método .GetOpenFilename y con la propiedad MultiSelect:=True, fundamental para seleccionar varios archivos:

AGRUPAR INFORMACION DE VARIOS LIBROS EN UNA HOJA EXCEL

Dado que estamos seleccionando varios archivos debemos especificar en el código el archivo qué vamos a procesar en cada momento, de ahí que debamos introducir un pequeño bucle indicando esta información, (utilizando matrices).

If IsArray(dir_Archivo) Then
For j = LBound(dir_Archivo) To UBound(dir_Archivo)
nArchivo = dir_Archivo(j)

El resto de la macro es idéntica a la que ofrecí en el post anterior al que nos estamos refiriendo.

Para este ejemplo, no he puesto encabezados de columna en la hoja “AGRUPADO”, de forma que los datos que vayamos a agrupar aparecerán con su encabezado en el resumen final (tal y como podéis ver aquí) este es el resultado de la macro:

AGRUPAR INFORMACION DE VARIOS LIBROS EN UNA HOJA EXCEL1

Cada encabezado representa la información extraída en cada hoja. Para eliminar los encabezados sobrantes con un simple filtro los podremos borrar (dejando el primero) y tendremos toda la información agrupada.

Si por el contrario, sabemos que todas las hojas tienen la misma estructura, con los mismos campos, podemos indicar el encabezado en la hoja “AGRUPADO”, pero entonces tendríamos que realizar las siguientes modificaciones en la macro (os dejo solo la parte que se modifica):

'Si existen datos en la hoja AGRUPADO, los eliminamos
With ThisWorkbook.Sheets("AGRUPADO")
elimina = Application.CountA(.Range("A:A")) 'eliminamos el +1
If elimina > 1 Then .Range("A2:A" & elimina).EntireRow.Delete
End With
'Iniciamos un for con para identificar los archivos seleccionados
If IsArray(dir_Archivo) Then
For j = LBound(dir_Archivo) To UBound(dir_Archivo)
nArchivo = dir_Archivo(j)
'Determinamos a partir de que fila vamos a consolidar los datos
FilaInicio = 2

El resultado sería el siguiente:

AGRUPAR INFORMACION DE VARIOS LIBROS EN UNA HOJA EXCEL2

Es decir, que podemos configurar el proceso según nuestras necesidades.

Es una macro con la que he disfrutado bastante escribiendo el código, ahora solo espero que os resulte de utilidad.

Descarga el archivo de ejemplo pulsando en: AGRUPAR INFORMACIÓN DE VARIOS LIBROS EN UNA HOJA EXCEL

Y la carpeta de ejemplo con los 3 archivos para realizar pruebas:  ARCHIVOS DE EJEMPLO PARA PRUEBAS

 

Anuncios

10 pensamientos en “AGRUPAR INFORMACIÓN DE VARIOS LIBROS EN UNA HOJA EXCEL

  1. Pingback: CONSOLIDAR VARIOS ARCHIVOS CSV O TXT USANDO CONEXIÓN DE DATOS EXTERNOS Y VBA | EXCEL SIGNUM

  2. Hola me encanto la macro es super util!! una duda se puede hacer que solo te consolide una hoja supongamos que tengo 5 archivos pero solo quiero consolidar la hoja 2 eso es posible?

    Muchas gracias

    Me gusta

  3. Hola Carlos:

    Sí, es posible, solo tienes que modificar el código e indicar la hoja que quieres consolidar de cada archivo:

    Sub AGRUPAR_ARCHIVOS()
    'Definimos variables
    Dim i As Integer, j As Integer, FilaInicio As Integer
    Dim iArchivo As String, nArchivo As String, MiLibro As String
    Dim dir_Archivo As Variant
    Dim iRango As Range, dRango As Range
    Dim Hoja_Destino As Worksheet, iLibro As Workbook
    'Creamos ventana de diálogo para seleccionar los archivos que queremos agrupar
    On Error Resume Next
    dir_Archivo = Application.GetOpenFilename(Title:="SELECCIONA ARCHIVOS PARA CONSOLIDAR", MultiSelect:=True, filefilter:="Excel files (*.xls*), *.xls*")
    On Error GoTo 0
    'Si no seleccionamos archivos, salimos del proceso
    If Not IsArray(dir_Archivo) Then
    Exit Sub
    End If
    'Si existen datos en la hoja AGRUPADO, los eliminamos
    With ThisWorkbook.Sheets("AGRUPADO")
    elimina = Application.CountA(.Range("A:A")) + 1
    If elimina > 0 Then .Range("A1:A" & elimina).EntireRow.Delete
    End With
    'Iniciamos un for con para identificar los archivos seleccionados
    If IsArray(dir_Archivo) Then
    For j = LBound(dir_Archivo) To UBound(dir_Archivo)
    nArchivo = dir_Archivo(j)
    'Determinamos a partir de que fila vamos a consolidar los datos
    FilaInicio = 1
    'Desactivamos actualizacion de pantalla y eventos
    With Application
    .ScreenUpdating = False
    .EnableEvents = False
    End With
    'Identificamos el nombre de nuestro libro
    MiLibro = ThisWorkbook.Name
    'Indicamos la hoja de destino de los datos que queremos consolidar
    Set Hoja_Destino = ThisWorkbook.Sheets("AGRUPADO")
    'Listamos los archivos Excel a consolidar
    iArchivo = nArchivo
    'Si la longitud del archivo es cero, salimos del proceso (no existe archivo para consolidar)
    If Len(iArchivo) = 0 Then Exit Sub
    'Si el nombre del archivo no es igual a nuestro libro seguimos el proceso
    If Not iArchivo = MiLibro Then
    'Capturamos ruta al iarchivo
    Set iLibro = Workbooks.Open(Filename:=nArchivo)
    iLibro.Sheets(2).Select
    Set iRango = iLibro.Sheets(2).Range(Cells(FilaInicio, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))
    Set dRango = Hoja_Destino.Range("A" & Hoja_Destino.Cells(Rows.Count, 1).End(xlUp).Row + 1)
    iRango.Copy
    With dRango
    .PasteSpecial xlPasteValues
    .PasteSpecial xlFormats
    End With
    'Cerramos el libro y continuamos el proceso
    Application.CutCopyMode = False
    iLibro.Close False
    End If
    Next j
    End If
    With Application
    .ScreenUpdating = True
    .EnableEvents = True
    End With
    ' Una vez finalizado, lanzamos mensaje de finalización.
    MsgBox ("EL PROCESO HA FINALIZADO CORRECTAMENTE"), vbInformation, "PROCESO DE CONSOLIDACIÓN"
    End Sub

    Me gusta

  4. Muy buen aporte amigo! Mi pregunta es si en vez de copiar los datos hacia abajo copie una columna determinada y la agrupe en la hoja consolidado, ejemplo: n archivos, todos con la misma estructura, tome la columna 3 de cada archivo y la copie en el libro nuevo hacia el lado, en el libro de destino columna B, C, D, etc… No sé si fui muy enredado en explicarlo.

    Me gusta

  5. Muy buen aporte! Me sirvió muchísimo. Tengo una pregunta, si tengo columnas y filas que tienen celdas vacías la macro se queda en esas celdas y no trae la otra información que se encuentra en filas siguientes. Como se podría cambiar esto? intente incluir la siguiente macro pero siempre con errores.

    Application.ScreenUpdating = False
    u = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
    uc = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column
    For i = u To 1 Step -1
    cr = WorksheetFunction.CountA(Rows(i))
    If cr = 0 Then
    Rows(i).Delete
    End If
    Next
    For i = uc To 1 Step -1
    cc = WorksheetFunction.CountA(Columns(i))
    If cc = 0 Then
    Columns(i).Delete
    End If
    Next
    End with

    Me gusta

  6. Hola!

    La macro funciona genial! Mi pregunta es si es posible que se copie la información, no en una misma hoja, sino que cada archivo de excel que copie lo haga en una hoja distinta con el nombre del archivo correspondiente. Muchas gracias!

    Me gusta

    • Hola Sannnn,

      Sería posible, aunque sería otro tipo de programación y temática. Dado que esta macro es para consolidar datos (por eso debe estar en un único lugar). Intento estos días publicar algo al respecto.

      Saludos

      Me gusta

¿Te ha gustado?, Realiza un comentario.

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.