Obtener un consolidado, extrayendo los datos de diferentes ficheros

Hace poco me preguntaba una amiga, si era posible obtener en un fichero único de Excel, una serie de registros procedentes de otros ficheros, de los cuales desconocemos un montón de cosas, incluido el propio nombre de estos ficheros. Es decir, se trataba de obtener en ese fichero único de Excel, los registros de forma concatenada (o agregada, unos detrás de los otros), de una serie de archivos, de los que desconocemos tanto su número, como su nombre, e incluso, la subcarpeta en la que están estos propios ficheros que serán la fuente desde la que vamos a obtener la información.

Mi respuesta fue: “Ufffffff, no entiendo nada de lo que me planteas. Hazme un ejemplo de lo que necesitas, que soy muy torpe.”

Su explicación fue más o menos así: Imagina que tengo una carpeta donde tengo alojadas una serie de subcarpetas (desconozco su número), y dentro de estas subcarpetas hay una serie de ficheros (desconozco tanto el número de ficheros como el nombre que tienen los mismos), …pues bien, lo que quiero, es que mire en todas esas subcarpetas, y en todos los ficheros que haya dentro, y como todos tienen un esquema similar, que coja esos datos, y me los ponga en un único fichero de Excel, colocando cada bloque de registros de cada fichero, uno detrás de otro, dentro de este único fichero que recoge la información.

Y ante esa respuesta, yo pensé: “¿tú lo que quieres es trabajar poco, y que Excel lo haga todo, einghhhh?”. Pues sí, no estaba equivocado, quería que Excel le hiciese el trabajo tedioso, recopilar los datos, sin tener que abrir cada fichero de Excel, y hacer un “copypaste” en el fichero de destino, que ella ya se encargaría de analizar toda esa información.
Vamos, que la idea era pasar de esto:

A esto otro:

Pues nada, me puse manos a la obra, y tras un buen rato de de análisis, trabajo y depuración de código, conseguimos un macro que haría todo eso en un abrir y cerrar de ojos.

La idea parte de esto que podéis ver en esta imagen, que contiene una serie de carpetas, que a su vez están alojadas en una carpeta que será la que dediquemos a albergar los ficheros desde los que obtendremos la información:

Y dentro de cada carpeta, tenemos diferentes ficheros (en cada carpeta podemos tener un número diferente de ficheros), y con diferentes nombres (aunque puede que haya varios ficheros con el mismo nombre, pero en carpetas diferentes, claro). Este sería un ejemplo de lo que tenemos en la carpeta llamada "Carpeta 4":

Como podéis ver se trata de ficheros de Excel, que en mi caso he hecho con la versión 2003, para que los usuarios con una versión antigua de esta popular hoja de cálculo, vean que no van a tener ningún problema a la hora de utilizar el macro que recopilará la información de los diferentes ficheros. Evidentemente aquellos usuarios que utilicen versiones posteriores de Excel (2007, 2010, 2013, o las que estén por llegar), no van a tener el más mínimo problema, pues nos da igual el nombre que tenga el fichero, o incluso su extensión, pues solo necesitamos que sean ficheros de Excel.
Si abrimos por ejemplo el fichero llamado “Usuarios zona A.xls”, nos encontramos con esto (la estructura o registros de todos los ficheros es similar, aunque los datos contenidos evidentemente no, pues cada fichero tiene datos diferentes, los cuales queremos extraer, para pegarlos de forma concatenada, en un único fichero que nos servirá de agregado para poder analizar los datos, a partir de él):

Algo muy importante, a lo que tengo que hacer mención es que:

• Da igual el nombre que tengan nuestras carpetas, y el nombre que tengan los ficheros que cuelguen de cada carpeta, y no tienen por qué tener un nombre correlativo. Incluso pueden tener nombre absurdos :-)
• Da igual tanto el número de carpetas, y de ficheros que cuelguen de cada carpeta, aunque si tenemos más de la cuenta, puede que Excel no pueda procesar tanta información y se nos cuelgue la aplicación (no he probado el límite en el cual se cuelga, porque no soy masoquista).
• Los datos los obtendremos de la primera hoja de cada uno de los libros.
• Los datos de los libros deben tener todos la misma estructura (mismo número de campos), pues consolidaremos peras con peras, y no peras con manzanas. No obstante, el número de registros de cada fichero puede ser distinto (en el ejemplo de este artículo trabajamos con registros diferentes en cada fichero, para que veáis como funciona).

Vamos allá. Para hacer todo esto, lo primero que haremos, será crear un fichero que llamaremos “Consolidado.xls”, que contendrá dos hojas. La primera será donde consolidaremos los datos (hemos puesto los encabezados de fila, para ahorrarnos el trabajo en el macro). A esta primera hoja la hemos llamado “Consolidado”. La segunda hoja llamada “Ficheros”, contendrá nos nombres de las carpetas en una columna, y en la adyacente tendremos los archivos que contiene cada carpeta, pero claro, esto no lo haremos manualmente, porque sino no tendría sentido crear un macro. Esta segunda hoja también nos la “rellenará” nuestro macro, pues él solito obtendrá los nombres de las carpetas, y los ficheros que contiene cada una de ellas.

Es decir, en la hoja “Consolidado” de nuestro libro tendremos esta plantilla:

Y en la “Ficheros” tendremos esto (ojo, que esto que veis, es tras la ejecución del macro, pues ha recorrido cada carpeta y cada fichero “fichando” el nombre de todo lo que encuentra dentro, así que para evitar problemas, trabajad solo con ficheros Excel, y no los mezcléis con ficheros Word, o con imágenes, por ejemplo):

Bueno, pues para hacer todo esto, tan solo deberemos copiar y pegar este macro, en un módulo:

Sub Consolidado() '-------------------------------------------------------- 'Lo primero que haremos, será recorrer los directorios 'seleccionando los ficheros que haya en cada uno de ellos '-------------------------------------------------------- 'Si hay errores, que continúe On Error Resume Next 'seleccionamos la hoja1 Hoja1.Select 'Seleccionamos el botón cuyo nombre queramos cambiar ActiveSheet.Shapes("Botón 1").Select 'le cambiamos el nombre, y lo ponemos en rojo Selection.Characters.Text = "Espera unos segundos..." With Selection.Font .ColorIndex = 3 End With 'Ocultamos el procedimiento Application.ScreenUpdating = False 'borramos todo lo que haya de datos anteriores en la Hoja1 Range("A6").Select If ActiveCell <> "" Then     Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select     Selection.EntireRow.Delete     Selection.ClearContents     Selection.ClearFormats End If 'Nos situamos en la hoja 2 Windows(ThisWorkbook.Name).Activate Hoja2.Select 'borramos lo que haya de otras veces Range("A2").Select If ActiveCell <> "" Then     Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select     Selection.EntireRow.Delete End If 'Creamos el objeto FileSystemObject Set Fso = CreateObject("Scripting.FileSystemObject") 'Informamos de la ruta de donde vamos a obtener 'los ficheros, en este caso, el mismo directorio 'donde tengamos grabado este fichero con el macro ruta = ActiveWorkbook.Path 'definimos dos variables que necesitaremos, 'para recuperar el nombre de la carpeta, y 'los subdirectorios y ficheros que haya dentro Set directorio = Fso.GetFolder(ruta) Set Subdirectorios = directorio.SubFolders Set ficheros = directorio.Files 'escribimos un encabezado en la celda A2 Range("A2").Select 'escribimos los subdirectorios For Each Subdirectorio In Subdirectorios Hoja2.Select 'escribimos el nombre del subdirectorio ActiveCell = Subdirectorio.Name     'entramos en el subdirectorio     Set directorio = Fso.GetFolder(Subdirectorio)     Set Subdirectorios = directorio.SubFolders     Set ficheros = directorio.Files     ActiveCell.Offset(0, 1).Select     For Each Archivo In ficheros     'escribimos el nombre del fichero     ActiveCell = Archivo.Name     'bajamos una fila     ActiveCell.Offset(1, 0).Select     Next 'bajamos una fila y volvemos una columna atrás ActiveCell.Offset(1, -1).Select Next 'Limpiamos los objetos Set Fso = Nothing Set directorio = Nothing Set Subdirectorios = Nothing Set ficheros = Nothing '--------------------------------------------------------------- 'Abrimos los ficheros uno a uno, y vamos copiando y escribiendo 'los datos en el fichero del consolidado en la hoja1 (no importa 'el nombre que tenga esta hoja) '--------------------------------------------------------------- 'abrimos las carpetas desde la 1 a la 50, para no hacer esto interminable carpeta = Range("A2").Address For i = 1 To 50 'nos situamos en la celda A2, de la hoja 2, que era donde estábamos, 'creando unas variables que nos serán de ayuda fichero = Range(carpeta).Offset(0, 1) j = 1     Do While Not IsEmpty(fichero)         Workbooks.Open Filename:=ruta & "\" & Range(carpeta) & "\" & fichero         'copiamos los datos que tenga desde A2 hasta el final         Range("A2").Select         Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select         Selection.Copy         'los pegamos en nuestro libro         Windows(ThisWorkbook.Name).Activate         Hoja1.Select         If Range("A6") = "" Then             Range("A6").Select         Else             Range("A6").Select             Selection.End(xlDown).Select             ActiveCell.Offset(1, 0).Select         End If         ActiveSheet.Paste         Application.CutCopyMode = False         'nos situamos al final de la lista consolidada         Selection.End(xlDown).Select         'bajamos una celda         ActiveCell.Offset(1, 0).Select         'cerramos el libro         Workbooks("" & fichero & "").Close savechanges:=False         'volvemos a la hoja2         Hoja2.Select         'creamos una variable para ir bajando a los ficheros         fichero = Range(carpeta).Offset(j, 1)         j = j + 1         Loop Range(carpeta).Select Selection.End(xlDown).Select carpeta = ActiveCell.Address Next 'volvemos a la Hoja2 Hoja2.Select Range("A2").Select 'volvemos a la Hoja1 Hoja1.Select 'Seleccionamos el botón ActiveSheet.Shapes("Botón 1").Select 'le cambiamos el nombre al botón, poniéndole el que tenía 'inicialmente y lo ponemos en negro Selection.Characters.Text = "Consolidar" With Selection.Font .ColorIndex = xlAutomatic End With Range("A6").Select 'Mostramos el procedimiento Application.ScreenUpdating = True 'mostramos un mensaje al finalizar CreateObject("wscript.shell").Popup _ " Ya hemos finalizado. ¿Ves como tardaría poco?. " + _ Chr(13) + " Los datos han sido consolidados, y demasiado sin esfuerzo ;-) ", _ 4, " Consolidación completada" End Sub

Cuando ejecutéis el macro, lo primero que hace, es eliminar los datos que haya previamente (si es que los hay). Luego, simplemente consolida los datos de los diferentes ficheros. Os dejo que lo probéis, y veáis que he puesto cada bloque de datos de diferentes colores en los ficheros de origen, para que se vea claramente que en el consolidado se “pegan” de la misma forma, es decir, manteniendo su formato (en este caso el color de origen, para que se vean bien los datos “copypasetados”).

Descomprimid el fichero comprimido de este enlace, en una carpeta, y probadlo. Veréis como el contenido de cada uno de los ficheros, de todas y cada una de las carpetas, pasa a formar parte de un único fichero, que es este desde el que ejecutamos el macro (Consolidado.xls).