24 septiembre, 2023

CONSOLIDAR INFORMACIÓN DE VARIOS ARCHIVOS EN UNA HOJA EXCEL CON VBA

Hola todos, ¿qué tal vais? ¡Seguro que bien! 🙂

Hoy me gustaría tratar un tema bastante recurrente cuando en nuestros procesos diarios trabajamos con cantidades bastante elevadas de archivos. Me refiero, por ejemplo, a que seamos los receptores de reportes diarios o mensuales de informaciones tipo, ventas, clientes, ingresos, etc.

Cuando sucede esto, solemos acabar con varias carpetas donde vamos almacenando la información por mes o por día y a la que habitualmente acudimos y confeccionamos nuestros reportes COPIANDO Y PEGANDO A MANO.

Esta situación la puedes tener un día, pero no debe darse habitualmente, por ello, he estado trabajando en un macro para resolver este problema.

Vamos a realizar un caso práctico, y vamos a utilizar nuestra base de datos de ejemplo, la del personal ficticio de unos grandes almacenes. En total son 180 empleados:

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

Para realizar un ejemplo, vamos a crear tres libros con tres hojas cada uno y en cada pestaña pegaremos 20 empleados y luego guardamos los archivos en una carpeta (CONSOLIDADO):

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

La idea es que con mediante un proceso podamos juntar de nuevo todas las hojas en un único archivo.

Para configurar nuestra hoja vamos a crear dos pestañas, una que vamos a denominar “CONSOLIDAR”,  y otra, en la segunda hoja que vamos a denominar “AGRUPADO”.

En la primera hoja colocaremos un botón de acción para ejecutar la macro y la segunda nos servirá para agrupar toda la información.

Una vez realizado esto, ya podemos pegar la siguiente macro:

Sub CONSOLIDAR()
Dim path As String, MiLibro As String
Dim FilaInicio As Integer
Dim i As Integer
Dim iArchivo As String
Dim iRango As Range, dRango As Range
Dim Hoja_Destino As Worksheet, iLibro As Workbook
'Creamos ventana de diálogo para seleccionar ruta al directorio o carpeta
On Error Resume Next
With CreateObject("shell.application")
path = .browseforfolder(0, Titulo, 0).Items.Item.path
End With: On Error GoTo 0
If path = Empty Then
Exit Sub
End If
'Determinamos a partir de que fila vamos a consolidar los datos
'Normalmente fila 2 si tenemos encabezados de columna
FilaInicio = 2
'Desactivamos actualizacion de pantalla y eventos
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Identificamos el nombre de nuestro libro
MiLibro = ThisWorkbook.Name
'Limpiamos datos en la hoja "AGRUPADO" para actualizar la informacion
ThisWorkbook.Sheets("AGRUPADO").Range("A2:H65000").ClearContents
'Indicamos la hoja de destino de los datos que queremos consolidar
Set Hoja_Destino = ThisWorkbook.Sheets("AGRUPADO")
'Identificamos y listamos los archivos Excel en el directorio (por orden de fecha de modificación)
iArchivo = Dir(path & "\*.xl*", vbNormal)
'Si la longitud del archivo es cero, salimos del proceso (no existe archivo para consolidar)
If Len(iArchivo) = 0 Then Exit Sub
' Mientras el largo del archivo sea mayor de 0 iniciamos el proceso
Do While Len(iArchivo) > 0
'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:=path & "\" & iArchivo)
'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
dRango.PasteSpecial xlPasteValues
Next
'Cerramos el libro y continuamos el proceso
Application.CutCopyMode = False
iLibro.Close False
End If
iArchivo = Dir()
Loop
' Si queremos podemos ordenar la información consolidada, en este caso por ID.
' Podemos obviar esta parte si no necesitamos ordenar los datos.
With ThisWorkbook.Sheets("AGRUPADO")
D_fin = Application.CountA(.Range("A:A"))
.Range("A1:G" & D_fin).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
.Range("A1").Select
.Columns.AutoFit
End With
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 observar, he incluido un buscador de directorios o carpetas de forma que no sea necesario modificar la macro para hacer referencia a la ruta de los archivos.

With CreateObject("shell.application")
path = .browseforfolder(0, Titulo, 0).Items.Item.path

Tan solo hay que seleccionar la carpeta donde los hemos guardado. El resto de la macro, lo voy explicando en el propio archivo.

Sí que me gustaría comentar lo siguiente, para indexar los archivos tipo Excel (*xl*) que vamos a consolidar he utilizado la función Dir().

iArchivo = Dir(path & "\*.xl*", vbNormal)

Efectivamente, lista los archivos que iremos procesando y lo hace correctamente. Sin embargo, en caso que necesitásemos que se fueran procesando ordenados por nombre, no podríamos hacerlo, la función ordena según la fecha de modificación del archivo, de la más antigua a la más reciente. Por ello, si los archivos que queremos consolidar han de llevar un cierto orden se debe tener en cuenta la fecha de modificación.

Para solucionar esta limitación de la función DIR(), al final de la macro, he añadido una pequeña instrucción que nos ordenará la hoja “AGRUPADO” por, por ejemplo, el ID. Y así tendremos los datos en orden.

Si no necesitáis que estén ordenados, simplemente elimináis la última parte:

D_fin = Application.CountA(.Range("A:A"))
.Range("A1:G" & D_fin).Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Pues esta ha sido la entrada de hoy, espero que os resulte de tanta utilidad como a mi (y os ahorre tiempo).

Importante: Este archivo de ejemplo está guardado como .xls y consolidará archivos que sean .xls. Si los archivos a consolidar son .xlsx es necesario guardar este archivo como .xslm. Esto es debido a la diferencia entre el número de filas permitido entre la versión 2003 y 2007 en adelante.

Os dejo el archivo de la macro.

Descarga el archivo de ejemplo pulsando en: CONSOLIDAR INFORMACIÓN DE VARIOS ARCHIVOS EN UNA HOJA EXCEL CON VBA

Y aquí la carpeta de ejemplo con los 3 archivos (esta en Drive, dado que en WordPress no se permite subir carpetas al servidor):  ARCHIVOS DE EJEMPLO PARA PRUEBAS

Comparte este post

16 comentarios en «CONSOLIDAR INFORMACIÓN DE VARIOS ARCHIVOS EN UNA HOJA EXCEL CON VBA»

    1. Hola Magnolia:

      La macro consolida los archivos que hayas introducido en la carpeta, tres son los que he puesto en el ejemplo, pero pueden ser muchos más 20 o 50, solo has de incluirlos en la carpeta y que todos tengan la misma estructura de datos.

      Hay varios post en la web sobre macros para consolidar, solo tienes que utilizar el buscador incluyendo consolidar o agrupar y te saldrán los post.

      Saludos.

  1. Hola, quisiera usar esta macro, pero me sale un error ‘1004’ en tiempo de ejecución definido por la aplicación o el objeto. Al depurar marca esto: Set dRango = Hoja_Destino.Range(«A» & Hoja_Destino.Cells(Rows.Count, 1).End(xlUp).Row + 1)

    Ojalá me pudieras ayudar ya que tengo que combinar 150 archivos excel y después tengo que limpiar la info de las demás hojas ya que sólo me sirve una, pero si me funciona bien la macro voy a tener más tiempo para hacerlo. Hasta ahora lo he hecho en forma manual, copiando y pegando, pero de verdad es demasiado, cada planilla tiene en algunos casos más de 200 registros. Te agradecería muchísimo me pudieras decir cómo arreglo este error.

    Clau.

  2. Hola Claudia:

    La macro está programada para consolidar hojas con datos a partir de la celda A1 y donde todas las filas tienen datos. Descarga mi ejemplo y realiza varias pruebas para verificar que te funciona, SI lo hace es un problema de la estructura de tus datos y que deberías adecuar a la macro.

    Actualmente tengo un volumen muy elevado de consultas y no tengo tiempo para realizar programas a medida.

    Saludos

    1. mmmm eso puede ser, porque mis datos comienzan en la B12, la columna A no tiene datos, pero igual no sé dónde cambiarlos, entré a la macro pero no me manejo en esto mucho. Bueno, muchas gracias igual por su tiempo y genialidad, buen día.

  3. Muy buena funcionalidad amigo. Una pregunta haz probado realizar la lectura en un Sharepoint. ?Funciona a nivel local pero el Dir falla en Sharepoint

    1. Hola, buenas tardes, disculpa, ¿Qué debo modificar para que solo me haga el proceso para ciertas hojas con cierto nombre de un libro?

      1. Debes incluir un if condicional después del for indicando la condición:

        Por ejemplo:

        For i = 1 To fin

        if iLibro.Sheets(i).name=»milibro» then

        iLibro.Sheets(i).Select

        Saludos

  4. Hola, es excelente la macro, quisiera saber como agregar en la primera celda a que libro pertenece cada fila. ¿Como puedo hacerlo?

    1. Hola federico, debes capturar el nombre del archivo y colocarlo en, por ejemplo la primera columna de cada consolidación.

      El nombre del archivo lo capturas así nombrelibro= ilibro.name

      Saludos.

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