CONSOLIDAR INFORMACIÓN DE VARIOS ARCHIVOS EN UNA HOJA EXCEL

Es muy habitual que cuando trabajamos con Excel, muchos de nuestros colaboradores nos envíen información a través de varios archivos. Muchas veces no queda más remedio que agrupar los datos en una carpeta y tenerlo todo bien organizado. Pero cuando la información que nos envían tiene la misma estructura, es decir, se usa una misma plantilla para recabar datos, es posible realizar un ejercicio de programación que pueda consolidar todos estos datos en una única hoja.

Pero vamos al ejemplo que seguro nos lo aclarará mucho mejor el ejercicio. Imaginad que este blog, de repente, se hace bastante conocido en la comunidad de usuarios de Excel y algunas personas deciden hacer un donativo para que el propietario lo siga manteniendo 🙂  Para tener un control sobre las donaciones realizadas, lo ideal sería colgar un impreso de donación genérico donde poder anotar las aportaciones, algo así:

Cada impreso lo vamos guardando en una carpeta en archivos separados de forma que podamos tener toda información ubicada en un único lugar. Esto es práctico cuando tenemos diez o veinte archivos, pero si tenemos mil sería difícil poder trabajar con la información, es decir, imaginad que queréis saber cuantos códigos postales hay, o cuantas personas de x ciudad ha dado un donativo … sería un trabajo enorme tener que abrir los mil archivos para contar los datos.

Pero se podría hacer de una forma mucho más eficaz. La idea es que usando un poco de código podamos reunir todos los datos que contienen cada uno de los archivos en una sola hoja, es decir, que podamos consolidar la información con un botón.

Para ello debemos utilizar el siguiente código:

Option Explicit
Sub Consolidar()
'Definimos las variables
Dim Misdatos(), i, Matriz_Archivo, Ruta_archivo, Base_archivos
Dim Libro_nuevo As Workbook
'Controlamos posibles errores al introducir los datos de la matriz
On Error GoTo Control_e
'Refresco de pantalla desactivado
Application.ScreenUpdating = False
'Hacemos que la hoja2 (donativos) sea la hoja activa
Sheets(2).Select
'Igualamos el destino del archivo base con el resto de archivos a consolidar
Ruta_archivo = ThisWorkbook.Path & "\"
'Buscamos los archivos que sean Excel (xls, xlsx, xlsm ...)
Base_archivos = Dir(Ruta_archivo & "*.xl*")
'Empezamos a contar archivos desde 0
Matriz_Archivo = 0
'Creamos bucle asignando los nuevos archivos a la matriz creada (siempre que existan archivos)
Do While Base_archivos <> Empty
'Si el nombre de los archivos a consolidar no son iguales al
'nombre del archivo principal entonces empezar a contar
If Base_archivos <> ThisWorkbook.Name Then
'procedemos a contar igualando y sumando 1
Matriz_Archivo = Matriz_Archivo + 1
'controlamos el tamaño de la matriz
ReDim Preserve Misdatos(1 To Matriz_Archivo)
Misdatos(Matriz_Archivo) = Base_archivos
End If
'Usamos la variable base archivo para realizar la búsqueda
Base_archivos = Dir()
Loop
If Matriz_Archivo > 0 Then
'Definimos el tamaño de la matriz
ReDim Matriz(1 To Matriz_Archivo, 1 To 10)
'Especificamos el mayor y menor subíndice en la matriz de datos
For i = LBound(Misdatos) To UBound(Misdatos)
'Asignamos cada uno de los archivos a vble Libro_nuevo
Set Libro_nuevo = Workbooks.Open(Ruta_archivo & Misdatos(i))
With Libro_nuevo.Worksheets(1)
'Asignamos datos a la matriz
'desde cada uno de los archivos que se van consolidando
Matriz(i, 1) = Libro_nuevo.Worksheets(1).Range("B5")
Matriz(i, 2) = Libro_nuevo.Worksheets(1).Range("B6")
Matriz(i, 3) = Libro_nuevo.Worksheets(1).Range("B7")
Matriz(i, 4) = Libro_nuevo.Worksheets(1).Range("B8")
Matriz(i, 5) = Libro_nuevo.Worksheets(1).Range("B9")
Matriz(i, 6) = Libro_nuevo.Worksheets(1).Range("B10")
Matriz(i, 7) = Libro_nuevo.Worksheets(1).Range("B11")
Matriz(i, 8) = Libro_nuevo.Worksheets(1).Range("B14")
Matriz(i, 9) = Libro_nuevo.Worksheets(1).Range("B15")
Matriz(i, 10) = Libro_nuevo.Worksheets(1).Range("B16")
End With
'cerramos el libro
Libro_nuevo.Close
Next i
End If
'Asignamos en la hoja2 los datos introducidos en la matriz.
With ThisWorkbook.ActiveSheet
'Limpiamos contenidos de la hoja hoja
ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.Offset(1).ClearContents
'Pegamos datos respetando la cabecera que hemos colocado como fija,
'y especificamos que las columnas que utilizamos son iguales a las definidas en las matrices,
'en este caso son 10 columnas
ThisWorkbook.ActiveSheet.Range("A2").Resize(Matriz_Archivo, 10) = Matriz
'Justificamos celdas
ThisWorkbook.ActiveSheet.Range("A1").CurrentRegion.EntireColumn.AutoFit
End With
'En caso de que en la carpeta los archivos sean 0 cerramos proceso
If Matriz_Archivo = 0 Then
MsgBox "NO HAY ARCHIVOS PARA CONSOLIDAR EN LA CARPETA ACTUAL", vbExclamation, "CARPETA VACÍA"
Exit Sub
'Si los datos de la matriz no son correctos saltará mensaje
Control_e:
MsgBox "VERIFICA QUE HAS INTRODUCIDO CORRECTAMENTE LOS PARAMETROS DE LA MATRIZ EN EL CODIGO VBA", vbExclamation, "CARPETA VACIA"
Exit Sub
End If
'Liberamos memoria datos nueva hoja
Set Libro_nuevo = Nothing
'Liberamos matriz de datos
Erase Misdatos
'Refresco de pantalla activo
Application.ScreenUpdating = True
End Sub

Como podéis ver, el código lo que hace es generar un nuevo libro que vamos a utilizar como contenedor de la matriz de datos y donde vamos a copiar los datos de todos los archivos antes de volcarlos en nuestra hoja.

Dadas las limitaciones de WordPress a la hora de adjuntar archivos (no deja subir .zip, .rar ni archivos de Access) he tenido que recurrir a google Drive, y es ahí donde os dejo el archivo de ejemplo.

El ejemplo se compone de una carpeta en la que he guardado cuatro archivos, el archivo denominado «CONSOLIDAR» es donde tenemos la macro que va a hacer posible recabar la información de los otros tres archivos, denominados numéricamente «Recibo de donativo1 … 2 y 3».

Debéis descargar la carpeta comprimida en ZIP y luego en vuestro equipo extraer la carpeta «Consolidar».

Una vez en vuestro equipo extraéis la carpeta «Consolidar» y abrís el archivo que tiene el mismo nombre «Consolidar».

En el momento de abrir veréis lo siguiente:

Una vez que pulséis el botón «CONSOLIDAR DATOS» la macro buscará archivos de Excel en la carpeta y consolidará la información de cada uno de los recibos. Para que os pueda ser útil en vuestros ejercicios solo tenéis que modificar el código con los parámetros de los archivos a consolidar (que vuelvo a repetir, han de tener la misma estructura).

PD: Ya se que usar «On error goto» para el control de posibles errores no es muy ortodoxo, pero en este caso, el código está muy bien estructurado y no crea confusión. Creo que procede usarlo    🙂

Descarga el archivo pulsando en: CONSOLIDAR INFORMACION