30 noviembre, 2023

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í:

CONSOLIDAR_INFORMACION_1

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».

CONSOLIDAR_INFORMACION_2

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

CONSOLIDAR_INFORMACION_4

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:

CONSOLIDAR_INFORMACION_3

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

Comparte este post

8 comentarios en «CONSOLIDAR INFORMACIÓN DE VARIOS ARCHIVOS EN UNA HOJA EXCEL»

  1. Excelentes aportes los de esta comunidad , pero tengo una inquietud , es posible que se copie al archivo consolidar , las pestañs de los otros archivos ? es decir en lugar de copiar los valores que se copien las pestañas de los otros.

    1. Hola Moises:

      Utiliza mejor esta macro: https://drive.google.com/file/d/0B2MZVcPxjhyPeDM5c18zdE5jOUk/view?usp=sharing

      Solo tienes que pulsar en el botón y seleccionar los archivos a los que quieras copiar el nombre de todas las pestañas.

      La he modificado de esta otra versión de este post: https://excelsignum.com/2017/03/28/agrupar-informacion-de-varios-libros-en-una-hoja-excel/ dado que existen varias formas de consolidar datos.

      Espero que te sea de utilidad.

      Saludos.

  2. Hola, he leido tu post y realmente me ha parecido excelente y es exactamente el tipo de información que necesito para consolidar los datos de los archivos de informes que recibo todos los meses y los cuales necesito consolidar en una sola plantilla.
    he intentado hacerlo con la macro que he bajado cambiando el parametro de busqueda de archivos pero noto que solo me trae la información de la columna B de manera vertical… solo que la plantilla que uso recopila los datos de manera inversa o sea horizontal utilizando la linea 1 como títulos, los cuales suman 30 columnas (de A hasta AD)
    Será que puedes auxiliarme… gracias

  3. Hola César:

    Tendría que ver un ejemplo del archivo que quieres consolidar y el archivo en el que vas a volcar toda la información. De todas formas, en esta web hay varias macros para realizar ese trabajo, quizás te puedan resultar de interés, en este post hago referencia a todos los artículos:

    https://excelsignum.com/2017/03/28/agrupar-informacion-de-varios-libros-en-una-hoja-excel/

    Si no consigues adaptarlos, envía archivos de ejemplo a excelsignum@yahoo.es

    Saludos

    1. Hola, Buen día… primeramente quiero darte las gracias, por la atención, y por responder a mi cuestionamiento… voy a examinar el link e intentar para ver lo que consigo, caso no lo consiga te envio el archivo de modelo…

      Grato

      César Eduardo Cano Prasca

  4. Hola a todos,
    soy novato en esto, y me gustaría saber si se puede crear una macro que me permita lo siguiente:
    1. Indicarle mediante un cuadro de diálogo, en que hoja u hojas de los libros se encuentra la información que quiero consolidar.
    2. Otra macro, que me permita ordenar la información, según la columna que yo le indique en un cuadro de diálogo, y que además, haga un recuento de los datos, según la columna que yo le indique para dicha tarea.
    Quizás es mucho pedir, pero como repito, soy nuevo en esto, y me toca consolidar informes, y lo estoy haciendo de manera manual.

    1. Hola Edwin, eso que comentas, todo se puede hacer, pero el problema es que yo no tengo tiempo para realizarlo Actualmente tengo un volumen muy elevado de consultas y no tengo tiempo para realizar programas a medida.

      Trata de estudiar detenidamente las macros que existen en esta web relacionadas con la consolidación y la ordenación de datos.

      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