Cómo hacer una macro para consolidar cientos de libros Excel en un único archivo

Introducción

Consolidar cientos de libros Excel en un único archivo puede parecer una tarea sencilla: abrir cada fichero, copiar sus datos y pegarlos en una tabla común. Sin embargo, cuando el volumen crece, aparecen problemas de estructura, calidad de datos, rendimiento, trazabilidad y control de errores que convierten el proceso manual en una fuente constante de retrasos.

Una macro bien diseñada puede recorrer una carpeta completa, identificar los libros válidos, extraer únicamente la información necesaria, normalizar los datos y generar un archivo consolidado preparado para su revisión, análisis o importación en otros sistemas. El objetivo no consiste solo en juntar filas, sino en construir un proceso repetible, rápido y suficientemente fiable para trabajar con decenas, cientos o incluso miles de ficheros.

En este artículo se explica qué significa realmente consolidar libros Excel, cómo debe plantearse la macro, qué problemas de calidad conviene resolver y qué técnicas permiten reducir los tiempos de procesamiento. También se analizan algunos errores frecuentes, como procesar celda por celda, abrir libros innecesariamente o confiar en que todos los archivos tendrán exactamente la misma estructura.

Índice

Qué significa consolidar cientos de libros Excel

Consolidar libros Excel significa reunir información distribuida entre varios archivos y transformarla en un conjunto de datos único, coherente y utilizable. El resultado puede ser una tabla maestra, un libro con varias hojas organizadas, una base histórica o un archivo preparado para alimentar informes, tablas dinámicas, gráficos o aplicaciones externas.

La consolidación no debe confundirse con una simple copia de hojas. Copiar hojas completas puede conservar formatos, fórmulas y objetos, pero no garantiza que la información quede integrada de manera homogénea. En una consolidación real suele ser necesario identificar columnas, seleccionar registros, adaptar tipos de datos, eliminar contenido innecesario y añadir información de procedencia.

Acciones que puede incluir una consolidación

• Apilar las filas de todos los libros en una tabla común.
• Combinar hojas que comparten una estructura equivalente.
• Seleccionar determinadas columnas y descartar las restantes.
• Unificar nombres de campos diferentes que representan el mismo concepto.
• Convertir fechas, números y códigos a un formato común.
• Eliminar filas vacías, subtotales, cabeceras repetidas o notas internas.
• Detectar registros duplicados.
• Añadir el nombre del archivo de origen, la hoja o la fecha de importación.
• Separar los registros válidos de los registros que necesitan revisión.

Por tanto, antes de programar una macro conviene definir qué debe conservarse, qué debe transformarse y qué debe rechazarse. Dos proyectos llamados «consolidación de libros» pueden tener necesidades completamente diferentes.

Cuándo conviene utilizar una macro de consolidación

Una macro resulta especialmente útil cuando la misma tarea se repite con frecuencia, los archivos tienen una estructura razonablemente estable y el tiempo invertido en copiar datos manualmente empieza a ser significativo.

Por ejemplo, una pequeña empresa puede recibir cada semana un libro Excel por cliente, delegación, vendedor, departamento o proyecto. Si una persona debe abrir manualmente doscientos archivos, seleccionar un rango, copiarlo y pegarlo en un libro maestro, el proceso será lento y difícil de controlar. Además, cualquier interrupción puede provocar duplicidades, omisiones o pegados en posiciones incorrectas.

Casos habituales

• Unificación de partes de trabajo enviados por distintos empleados.
• Consolidación de ventas procedentes de varias tiendas o delegaciones.
• Integración de inventarios generados por diferentes almacenes.
• Recopilación de presupuestos, mediciones o certificaciones.
• Combinación de informes mensuales por cliente.
• Integración de formularios internos guardados como libros independientes.
• Preparación de datos históricos para una migración.
• Generación de una tabla única para Power BI, Access, SQL o un sistema de gestión.

La macro no elimina la necesidad de supervisión. Su función es ejecutar de forma consistente las reglas previamente definidas y dejar constancia de los problemas encontrados.

Requisitos que deben definirse antes de programar

Una macro de consolidación no debería comenzar a programarse sin disponer de ejemplos reales de los archivos de entrada y de una definición clara del resultado esperado. Cuando estos requisitos no se concretan, el desarrollo suele llenarse de excepciones improvisadas y correcciones posteriores.

Información mínima necesaria

• Carpeta o carpetas donde se encuentran los libros.
• Extensiones que deben admitirse: XLSX, XLSM, XLSB o XLS.
• Nombres posibles de las hojas que contienen los datos.
• Fila en la que comienzan los encabezados.
• Columnas obligatorias y opcionales.
• Criterio para determinar la última fila válida.
• Reglas de transformación y limpieza.
• Tratamiento de archivos protegidos, dañados o abiertos por otro usuario.
• Criterio para identificar duplicados.
• Estructura exacta del archivo consolidado.
• Volumen aproximado de libros y registros.
• Tiempo máximo aceptable de ejecución.

También es importante disponer de archivos anómalos, no solo de ejemplos perfectos. Los ficheros con columnas desplazadas, datos incompletos, fechas incorrectas o fórmulas con errores permiten diseñar una solución más resistente.

Arquitectura general de una macro de consolidación

Una solución mantenible debe dividir el trabajo en fases. Concentrar toda la lógica en un único procedimiento largo dificulta las pruebas, la corrección de errores y las futuras ampliaciones.

Fases recomendadas

1. Inicializar la ejecución y guardar la configuración actual de Excel.
2. Seleccionar la carpeta de origen o leerla desde una hoja de parámetros.
3. Crear la lista de archivos candidatos.
4. Excluir el propio libro consolidador y los archivos temporales.
5. Abrir cada libro en modo de solo lectura.
6. Localizar la hoja y el rango de datos.
7. Validar la estructura.
8. Leer los datos válidos en bloque.
9. Transformar o normalizar la información.
10. Escribir el resultado en bloque en el libro de destino.
11. Registrar incidencias y estadísticas.
12. Cerrar el archivo de origen sin guardar cambios.
13. Restaurar la configuración de Excel.
14. Presentar un resumen final.

Esta separación permite sustituir una parte del proceso sin reescribir toda la macro. Por ejemplo, puede cambiar la forma de identificar la hoja de origen sin afectar al módulo encargado de escribir el resultado.

Localización y selección de los archivos

La macro debe saber exactamente qué libros forman parte de la consolidación. Recorrer indiscriminadamente todos los archivos de una carpeta puede provocar errores si existen copias antiguas, documentos auxiliares, archivos temporales o libros generados por ejecuciones anteriores.

Filtros habituales

• Extensión del archivo.
• Prefijo o sufijo del nombre.
• Fecha de creación o modificación.
• Subcarpeta de procedencia.
• Periodo incluido en el nombre del fichero.
• Lista autorizada de clientes, proyectos o departamentos.

Los archivos temporales de Excel suelen comenzar por los caracteres ~$ y deben excluirse. También conviene impedir que el libro que ejecuta la macro sea tratado como archivo de origen.

Para recorridos sencillos puede utilizarse la función Dir de VBA. Si se necesita recorrer subcarpetas, leer propiedades o aplicar reglas más complejas, puede emplearse FileSystemObject.

Validación de la estructura de los libros

No debe asumirse que todos los libros contienen la hoja correcta, los encabezados esperados y las columnas en la misma posición. Incluso cuando existe una plantilla común, los usuarios pueden insertar columnas, cambiar títulos, borrar fórmulas o renombrar hojas.

Comprobaciones recomendadas

• La hoja de datos existe.
• La fila de encabezados puede localizarse.
• Los campos obligatorios están presentes.
• No existen encabezados duplicados.
• El rango contiene al menos una fila de datos.
• Las columnas clave no están completamente vacías.
• Los tipos de datos son razonables.
• El número de columnas no supera el previsto.
• Las celdas combinadas no interfieren con la lectura.

Siempre que sea posible, las columnas deben localizarse por el texto de sus encabezados y no únicamente por su posición. Una macro que presupone que «Cliente» siempre está en la columna B puede fallar si alguien inserta una nueva columna al principio. En cambio, una búsqueda por encabezado permite soportar variaciones controladas.

Esta flexibilidad debe tener límites. Si el archivo se aparta demasiado del formato esperado, es preferible registrarlo como incidencia y continuar con el siguiente libro antes que importar datos de forma incorrecta.

Cómo tratar la falta de calidad de los datos

La calidad de los datos suele ser el principal problema de una consolidación. Una macro puede copiar información rápidamente, pero no puede decidir por sí sola qué significa un dato ambiguo si no se han definido reglas concretas.

Problemas frecuentes

• Fechas guardadas como texto.
• Números con separadores decimales diferentes.
• Importes que incluyen símbolos o espacios.
• Códigos que han perdido ceros iniciales.
• Nombres escritos de formas distintas.
• Celdas con espacios invisibles.
• Valores obligatorios vacíos.
• Errores de fórmula como #N/A, #VALUE! o #REF!.
• Filas duplicadas.
• Encabezados con faltas de ortografía.
• Datos mezclados con subtotales o comentarios.
• Fórmulas donde se esperaban valores fijos.

Normalizar sin ocultar problemas

La macro puede aplicar transformaciones seguras, como eliminar espacios sobrantes, unificar mayúsculas y minúsculas, convertir fechas reconocibles o sustituir valores equivalentes previamente definidos. Sin embargo, no debería inventar datos ni corregir silenciosamente situaciones dudosas.

Una práctica recomendable consiste en clasificar los registros en tres grupos:

• Registros válidos: cumplen todas las reglas y pueden incorporarse al consolidado.
• Registros corregibles: contienen problemas que la macro puede resolver de manera inequívoca.
• Registros rechazados: necesitan revisión humana porque falta información o existe ambigüedad.

Los registros rechazados pueden guardarse en una hoja específica junto con el archivo de origen, el número de fila y una descripción del error. De esta forma, la consolidación puede finalizar sin ocultar las incidencias.

Diccionarios de equivalencias

Cuando existen valores diferentes para una misma categoría, puede mantenerse una tabla de equivalencias en una hoja de configuración. Por ejemplo, «Madrid», «MADRID», «Mad.» y «Mdrid» podrían convertirse en un único valor normalizado si la empresa ha aprobado expresamente esa equivalencia.

Las equivalencias no deberían quedar dispersas dentro del código VBA. Guardarlas en una tabla facilita su revisión y permite modificar reglas sin alterar la programación.

Estrategias posibles de consolidación

No existe un único método válido. La estrategia depende de la homogeneidad de los archivos, el volumen de datos y el destino final.

Apilado directo de filas

Es la opción más sencilla. Cada libro contiene las mismas columnas y sus registros se añaden al final de una tabla común. Resulta adecuada cuando todos los ficheros proceden de una plantilla controlada.

Consolidación mediante mapeo de columnas

Los archivos contienen campos equivalentes, pero las columnas pueden aparecer en distinto orden o con nombres diferentes. La macro identifica cada campo y lo coloca en la columna correspondiente del resultado.

Consolidación de varias hojas por libro

Cada archivo puede contener varias hojas relevantes. La macro debe recorrerlas, aplicar filtros por nombre o estructura y añadir la procedencia de cada registro.

Consolidación con agregación

En lugar de conservar cada fila, la macro calcula totales por cliente, producto, fecha, proyecto u otra dimensión. Esta opción reduce el volumen del resultado, pero elimina detalle y exige definir cuidadosamente las reglas de agrupación.

Consolidación incremental

Solo se importan archivos nuevos o modificados desde la última ejecución. Para ello debe mantenerse un registro de ficheros procesados, fechas, tamaños o huellas de contenido. Es útil cuando el volumen histórico es grande y cada ejecución incorpora únicamente una pequeña cantidad de información nueva.

Rendimiento y crecimiento de los tiempos de procesamiento

Al aumentar el número de libros, el tiempo de ejecución no siempre crece de forma estrictamente proporcional. En una macro mal diseñada puede parecer que el tiempo aumenta de manera exponencial, especialmente cuando cada nueva fila provoca búsquedas sobre todo el consolidado, recálculos repetidos o escrituras constantes en la hoja.

Por ejemplo, si después de importar cada registro la macro recorre todas las filas anteriores para detectar duplicados, el trabajo acumulado crece rápidamente. Lo mismo sucede si se utilizan fórmulas que recalculan tras cada pegado o si se busca repetidamente la siguiente fila libre examinando columnas completas.

Factores que aumentan el tiempo total

• Número de archivos.
• Tamaño de cada libro.
• Cantidad de hojas procesadas.
• Número de filas y columnas.
• Presencia de fórmulas complejas.
• Vínculos externos.
• Archivos almacenados en red o servicios sincronizados.
• Apertura de libros con macros o eventos.
• Lectura y escritura celda por celda.
• Búsquedas repetidas sobre rangos crecientes.
• Recálculo automático.
• Actualización continua de la pantalla.
• Uso excesivo de Select, Activate y portapapeles.

Por este motivo, no basta con medir cuánto tarda la macro con cinco archivos. Debe probarse con un volumen cercano al real y, cuando sea posible, con escenarios superiores para anticipar el crecimiento futuro.

Operaciones con bloques de celdas frente a bucles interminables

Una de las optimizaciones más importantes consiste en reducir el número de comunicaciones entre VBA y las hojas de Excel. Leer o escribir una celda en cada iteración genera una gran sobrecarga. Por ello, es preferible trabajar con rangos completos y efectuar el menor número posible de operaciones sobre la hoja.

Enfoque lento

Un diseño poco eficiente puede abrir un libro, recorrer cada fila, leer cada celda por separado y escribir inmediatamente el resultado en otra celda del consolidado. Aunque el código parezca sencillo, puede realizar cientos de miles o millones de accesos individuales al modelo de objetos de Excel.

Enfoque eficiente

Un diseño mejor identifica el rango completo, carga sus valores de una sola vez, realiza únicamente las transformaciones necesarias y vuelca el bloque resultante en una única operación.

La asignación directa entre un rango y una matriz de tipo Variant suele ser muy rápida:

datos = hojaOrigen.Range("A2:H5000").Value2
hojaDestino.Range("A2").Resize(UBound(datos, 1), UBound(datos, 2)).Value2 = datos

Sin embargo, debe matizarse una idea frecuente: los arrays en memoria no son lentos por naturaleza. De hecho, suelen ser muy eficientes para transformar datos. Lo que resulta lento es utilizar bucles excesivos, anidados o repetitivos cuando la misma operación podría resolverse mediante una transferencia completa de bloques, filtros, búsquedas indexadas o estructuras más adecuadas.

La estrategia óptima suele ser:

1. Leer un bloque de celdas en una sola operación.
2. Procesar en memoria solo aquello que realmente necesita transformación.
3. Evitar recorridos repetidos sobre los mismos datos.
4. Escribir el bloque final en una sola operación.

Cuando no se necesita modificar los valores, puede copiarse directamente el rango completo. Cuando sí se requiere normalización, el uso de arrays sigue siendo adecuado, siempre que los algoritmos no introduzcan búsquedas o bucles innecesarios.

Cómo reducir el tiempo de procesamiento por fichero

La mejora del rendimiento debe abordarse en cada etapa. Ahorrar unos segundos por libro puede representar muchos minutos cuando se procesan cientos de archivos.

Abrir los libros en modo de solo lectura

Si la macro únicamente necesita extraer datos, los archivos deben abrirse con ReadOnly:=True. Esto reduce riesgos y evita bloqueos innecesarios.

Evitar la actualización de vínculos

Los libros pueden contener vínculos externos. Actualizarlos durante la apertura puede provocar esperas, mensajes o conexiones a rutas que ya no existen. Cuando no son necesarios para la consolidación, conviene abrir los libros sin actualizar vínculos.

Desactivar temporalmente funciones de Excel

Durante la ejecución pueden desactivarse:

• La actualización de pantalla.
• El cálculo automático.
• Los eventos.
• Los avisos interactivos que no sean necesarios.
• La barra de estado, salvo que se utilice para mostrar progreso.

Estas opciones deben restaurarse siempre al finalizar, incluso si se produce un error.

No copiar formatos innecesarios

Si el consolidado solo necesita valores, no deben copiarse formatos, anchos de columna, validaciones, comentarios, objetos o fórmulas. La propiedad Value2 suele ser adecuada para transferir datos sin formatos.

Determinar correctamente el rango utilizado

Utilizar columnas completas o la propiedad UsedRange sin comprobar su contenido puede incluir miles de filas vacías debido a formatos antiguos. Es preferible localizar la última fila y la última columna con criterios basados en campos fiables.

Reducir búsquedas repetidas

Las posiciones de las columnas deben calcularse una vez por archivo, no una vez por fila. Del mismo modo, las tablas de equivalencias pueden cargarse al inicio en un diccionario para evitar búsquedas continuas en hojas.

Escribir los datos de cada libro en una sola operación

La macro debe acumular el bloque transformado y volcarlo de una vez en la siguiente fila disponible. La posición de destino debe mantenerse en una variable, en lugar de recalcularse examinando la hoja tras cada importación.

Separar la importación de los cálculos finales

Las fórmulas, tablas dinámicas o informes deben actualizarse después de completar la consolidación. Recalcular el informe tras cada archivo multiplica innecesariamente el tiempo de proceso.

Evitar seleccionar hojas y celdas

Las instrucciones Select y Activate rara vez son necesarias. Los objetos deben referenciarse directamente mediante variables de tipo Workbook, Worksheet y Range.

Control de errores y continuidad del proceso

Cuando se procesan cientos de archivos, es probable que alguno esté dañado, protegido, incompleto o bloqueado. La macro no debería detener toda la consolidación por un único fichero defectuoso.

Incidencias que deben contemplarse

• Archivo que no puede abrirse.
• Contraseña de apertura.
• Libro dañado.
• Hoja inexistente.
• Encabezados no reconocidos.
• Rango sin datos.
• Errores de lectura.
• Fichero abierto por otro usuario.
• Ruta demasiado larga.
• Falta de permisos.
• Espacio insuficiente en disco.

Cada error debe registrarse con el nombre del archivo, la fase en la que se produjo, una descripción y, cuando sea útil, el número de error de VBA. Después, la macro puede cerrar los objetos abiertos y continuar con el siguiente fichero.

No conviene utilizar un On Error Resume Next general para toda la macro, porque ocultaría errores importantes. Esta instrucción puede usarse de forma muy limitada para comprobaciones concretas, restableciendo inmediatamente el control normal de errores.

Trazabilidad de los datos consolidados

Una tabla consolidada debe permitir averiguar de dónde procede cada registro. Sin trazabilidad, corregir una incidencia posterior puede obligar a revisar cientos de archivos.

Columnas de control recomendadas

• Nombre del archivo de origen.
• Ruta o subcarpeta.
• Nombre de la hoja.
• Número de fila original.
• Fecha y hora de importación.
• Identificador de ejecución.
• Estado de validación.
• Descripción de correcciones automáticas aplicadas.

Estas columnas pueden mantenerse en el consolidado principal o en una tabla auxiliar. Su presencia es especialmente útil cuando los archivos originales pueden cambiar después de la importación.

Registro de ejecución

Además de la trazabilidad por fila, conviene generar un resumen con los siguientes datos:

• Hora de inicio y finalización.
• Número de archivos detectados.
• Número de archivos procesados.
• Número de archivos rechazados.
• Total de registros incorporados.
• Total de registros descartados.
• Duración total.
• Tiempo medio por archivo.

Diseño del archivo de salida

El libro final debe estar preparado para trabajar con volúmenes grandes y para facilitar futuras actualizaciones. Una estructura excesivamente decorada puede ralentizar el proceso y dificultar la automatización.

Hojas posibles

• Configuración: rutas, nombres de hojas, columnas y parámetros.
• Consolidado: tabla principal con los registros válidos.
• Incidencias: archivos o filas que necesitan revisión.
• Log: estadísticas y resultados de cada ejecución.
• Equivalencias: reglas de normalización.
• Informe: tablas dinámicas, indicadores o gráficos.

La tabla consolidada debe tener una fila de encabezados estable, sin celdas combinadas y sin filas vacías intermedias. Si se utiliza un objeto Tabla de Excel, puede facilitar filtros, fórmulas estructuradas y conexión con otras herramientas, aunque su redimensionamiento continuo durante la importación puede añadir sobrecarga. En procesos grandes puede ser más eficiente escribir primero el bloque completo y convertirlo o redimensionarlo al finalizar.

Ejemplo de estructura VBA para consolidar libros

El siguiente código es un ejemplo simplificado. Supone que todos los libros contienen una hoja llamada Datos, que los encabezados están en la primera fila y que deben consolidarse las columnas A:H. En un proyecto real deberían añadirse validaciones, mapeo de campos, tratamiento de incidencias y reglas específicas de calidad.

Option Explicit

Public Sub ConsolidarLibros()

    Dim libroDestino As Workbook
    Dim hojaDestino As Worksheet
    Dim libroOrigen As Workbook
    Dim hojaOrigen As Worksheet
    
    Dim carpeta As String
    Dim archivo As String
    Dim rutaCompleta As String
    
    Dim ultimaFilaOrigen As Long
    Dim siguienteFilaDestino As Long
    Dim numeroFilas As Long
    Dim datos As Variant
    
    Dim calculoAnterior As XlCalculation
    Dim pantallaAnterior As Boolean
    Dim eventosAnteriores As Boolean
    Dim alertasAnteriores As Boolean
    
    Set libroDestino = ThisWorkbook
    Set hojaDestino = libroDestino.Worksheets("Consolidado")
    
    carpeta = libroDestino.Worksheets("Configuracion").Range("B2").Value2
    
    If Len(carpeta) = 0 Then
        MsgBox "No se ha indicado la carpeta de origen.", vbExclamation
        Exit Sub
    End If
    
    If Right$(carpeta, 1) <> Application.PathSeparator Then
        carpeta = carpeta & Application.PathSeparator
    End If
    
    pantallaAnterior = Application.ScreenUpdating
    eventosAnteriores = Application.EnableEvents
    alertasAnteriores = Application.DisplayAlerts
    calculoAnterior = Application.Calculation
    
    On Error GoTo GestionErrorGeneral
    
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
    
    siguienteFilaDestino = hojaDestino.Cells(hojaDestino.Rows.Count, "A").End(xlUp).Row + 1
    archivo = Dir$(carpeta & "*.xls*")
    
    Do While Len(archivo) > 0
    
        If Left$(archivo, 2) <> "~$" _
           And StrComp(archivo, libroDestino.Name, vbTextCompare) <> 0 Then
            
            rutaCompleta = carpeta & archivo
            
            Set libroOrigen = Nothing
            Set hojaOrigen = Nothing
            
            On Error GoTo GestionErrorArchivo
            
            Set libroOrigen = Workbooks.Open( _
                Filename:=rutaCompleta, _
                UpdateLinks:=0, _
                ReadOnly:=True, _
                AddToMru:=False)
            
            Set hojaOrigen = libroOrigen.Worksheets("Datos")
            
            ultimaFilaOrigen = hojaOrigen.Cells(hojaOrigen.Rows.Count, "A").End(xlUp).Row
            
            If ultimaFilaOrigen >= 2 Then
                
                datos = hojaOrigen.Range("A2:H" & ultimaFilaOrigen).Value2
                numeroFilas = UBound(datos, 1)
                
                hojaDestino.Cells(siguienteFilaDestino, "A") _
                    .Resize(numeroFilas, 8).Value2 = datos
                
                hojaDestino.Cells(siguienteFilaDestino, "I") _
                    .Resize(numeroFilas, 1).Value2 = archivo
                
                siguienteFilaDestino = siguienteFilaDestino + numeroFilas
            
            End If
            
CerrarArchivo:
            On Error Resume Next
            
            If Not libroOrigen Is Nothing Then
                libroOrigen.Close SaveChanges:=False
            End If
            
            On Error GoTo GestionErrorGeneral
        
        End If
        
Continuar:
        archivo = Dir$()
    
    Loop
    
SalidaSegura:
    Application.ScreenUpdating = pantallaAnterior
    Application.EnableEvents = eventosAnteriores
    Application.DisplayAlerts = alertasAnteriores
    Application.Calculation = calculoAnterior
    
    MsgBox "La consolidación ha finalizado.", vbInformation
    Exit Sub

GestionErrorArchivo:
    RegistrarIncidencia archivo, Err.Number, Err.Description
    Err.Clear
    Resume CerrarArchivo

GestionErrorGeneral:
    MsgBox "Se ha producido un error: " & Err.Description, vbCritical
    Resume SalidaSegura

End Sub

Private Sub RegistrarIncidencia( _
    ByVal nombreArchivo As String, _
    ByVal numeroError As Long, _
    ByVal descripcionError As String)

    Dim hojaLog As Worksheet
    Dim fila As Long
    
    Set hojaLog = ThisWorkbook.Worksheets("Incidencias")
    
    fila = hojaLog.Cells(hojaLog.Rows.Count, "A").End(xlUp).Row + 1
    
    hojaLog.Cells(fila, "A").Value2 = Now
    hojaLog.Cells(fila, "B").Value2 = nombreArchivo
    hojaLog.Cells(fila, "C").Value2 = numeroError
    hojaLog.Cells(fila, "D").Value2 = descripcionError

End Sub

Este ejemplo utiliza transferencias por bloques y mantiene en una variable la siguiente fila disponible. Sin embargo, todavía sería necesario comprobar los encabezados, evitar duplicidades, normalizar datos y controlar otros tipos de archivo antes de considerarlo una solución completa.

Mejoras avanzadas para grandes volúmenes

Consolidación incremental

Cuando la carpeta contiene un histórico muy grande, puede evitarse la lectura completa en cada ejecución. La macro puede mantener una tabla con el nombre, tamaño y fecha de modificación de cada archivo procesado. Solo los ficheros nuevos o modificados se incorporan de nuevo.

Uso de diccionarios para duplicados

Un objeto Scripting.Dictionary puede almacenar claves únicas y detectar duplicados con mayor eficiencia que una búsqueda repetida sobre la hoja. La clave puede construirse combinando varios campos, como cliente, fecha, número de documento y línea.

Procesamiento por lotes

Si el resultado supera la capacidad práctica de una sola matriz, puede procesarse en lotes. Por ejemplo, la macro puede acumular cincuenta mil filas, escribirlas en bloque y continuar con el siguiente lote.

Lectura sin abrir visualmente los libros

En algunos casos puede utilizarse ADO, Power Query u otras tecnologías para leer información de libros cerrados. Esto puede resultar más eficiente, pero introduce requisitos adicionales y no es adecuado para todas las estructuras.

Separación entre extracción y transformación

Una arquitectura más sólida puede guardar primero los datos originales en una zona temporal y aplicar después las reglas de limpieza. Esto facilita la auditoría y evita que un cambio en las reglas obligue a volver a leer todos los archivos.

Pruebas necesarias antes de utilizar la macro

Una macro que funciona correctamente con tres libros de muestra puede fallar con el conjunto real. Las pruebas deben cubrir tanto archivos válidos como situaciones anómalas.

Pruebas funcionales

• Libro con una sola fila.
• Libro con miles de filas.
• Archivo vacío.
• Hoja inexistente.
• Columnas en distinto orden.
• Encabezado mal escrito.
• Fechas como texto.
• Importes con formatos diferentes.
• Filas duplicadas.
• Errores de fórmula.
• Archivo protegido.
• Libro dañado.
• Archivo temporal.
• Interrupción de la ejecución.

Pruebas de rendimiento

Debe medirse el tiempo con distintas cantidades de archivos y registros. Una tabla de resultados puede incluir:

• Número de libros.
• Número total de filas.
• Tamaño acumulado.
• Tiempo de apertura.
• Tiempo de transformación.
• Tiempo de escritura.
• Duración total.
• Memoria utilizada.

Estas mediciones permiten identificar si el cuello de botella está en el disco, la red, la apertura de archivos, el algoritmo de limpieza o la escritura en Excel.

Límites de una consolidación basada en Excel y VBA

Excel y VBA pueden resolver muchos procesos internos, pero no son una base de datos ni una plataforma de procesamiento ilimitada. El diseño debe considerar el volumen previsto y su crecimiento.

Límite de filas

Una hoja de las versiones modernas de Excel admite 1.048.576 filas. Si la suma de todos los libros puede acercarse a ese límite, el resultado tendrá que dividirse en varias hojas, varios archivos o almacenarse en otro sistema.

Consumo de memoria

Las matrices grandes, los libros abiertos y las fórmulas pueden consumir una cantidad considerable de memoria. El riesgo aumenta en instalaciones de Office de 32 bits y cuando se procesan muchas columnas.

Estabilidad en procesos largos

Una ejecución de varias horas es más vulnerable a interrupciones, bloqueos, cierres accidentales o problemas de red. Para procesos muy extensos conviene implementar puntos de control, ejecución incremental y capacidad de reanudación.

Trabajo concurrente

Un libro Excel no es la herramienta ideal cuando varias personas necesitan ejecutar consolidaciones simultáneas, modificar reglas o consultar el resultado al mismo tiempo.

Mantenimiento

Si la estructura de los archivos cambia constantemente, la macro puede requerir revisiones frecuentes. La estandarización de las plantillas de origen es tan importante como el propio código.

Cuándo conviene desarrollar una solución a medida

Una macro sencilla puede ser suficiente cuando los archivos son homogéneos y la consolidación se realiza de forma ocasional. Sin embargo, conviene plantear un desarrollo a medida cuando el proceso afecta a información importante, incluye cientos de libros, debe ejecutarse periódicamente o necesita un control detallado de incidencias.

Una solución profesional puede incorporar:

• Panel de configuración.
• Selector de carpetas y periodos.
• Mapeo flexible de columnas.
• Validación de estructuras.
• Normalización mediante tablas de equivalencias.
• Detección eficiente de duplicados.
• Registro completo de incidencias.
• Barra de progreso.
• Procesamiento incremental.
• Generación automática de informes.
• Exportación a CSV, base de datos u otros formatos.
• Documentación y procedimiento de recuperación.

Antes de programar, es recomendable analizar una muestra representativa de los libros reales. Esto permite estimar el esfuerzo, detectar excepciones y decidir si VBA es la tecnología adecuada o si resulta preferible utilizar Power Query, Python, una base de datos u otra solución.

Errores frecuentes al programar la consolidación

• Suponer que todas las hojas tienen el mismo nombre.
• Confiar exclusivamente en posiciones fijas de columnas.
• Copiar y pegar celda por celda.
• Utilizar Select y Activate de forma continua.
• Recalcular el libro después de cada fila.
• No excluir el propio consolidado.
• No cerrar los libros cuando se produce un error.
• No restaurar el cálculo y los eventos de Excel.
• Ignorar archivos defectuosos sin registrarlos.
• Eliminar duplicados sin definir una clave fiable.
• Corregir datos ambiguos automáticamente.
• No conservar la procedencia de cada registro.
• Probar únicamente con archivos perfectos.
• No medir el rendimiento con el volumen real.

Una macro rápida pero incapaz de explicar qué ha procesado y qué ha rechazado puede generar más trabajo que el procedimiento manual. La calidad de la solución depende tanto de sus controles como de su velocidad.

Conclusión

Consolidar cientos de libros Excel en un único archivo requiere mucho más que repetir una operación de copiar y pegar. Es necesario definir la estructura esperada, validar cada fichero, tratar la falta de calidad de los datos, registrar incidencias y preservar la procedencia de cada registro.

El rendimiento depende en gran medida de reducir las interacciones individuales con las hojas. Leer y escribir rangos completos, evitar recálculos repetidos, mantener referencias directas a los objetos y procesar únicamente las columnas necesarias puede reducir de forma notable el tiempo empleado por archivo.

Los arrays en memoria son útiles y rápidos cuando se emplean para procesar bloques completos. El problema aparece cuando se diseñan bucles interminables, búsquedas repetidas o algoritmos que recorren una y otra vez datos ya procesados. La combinación adecuada consiste en transferir bloques, transformar en memoria solo lo necesario y escribir el resultado de una sola vez.

Cuando la consolidación es crítica para la actividad de la empresa, debe tratarse como un pequeño sistema de procesamiento de datos: con configuración, validaciones, trazabilidad, control de errores, pruebas de rendimiento y documentación. De esta forma, la macro puede ahorrar muchas horas sin convertir el archivo final en una caja negra difícil de verificar.

Preguntas frecuentes