Por alguna necesidad, es común crear un libro nuevo con la misma estructura de columnas, por ejemplo, tener un libro por cada mes o un libro por cada proveedor, o por cliente, en fin, las razones pueden ser variadas, y luego necesitamos hacer un resumen de esos libros.
Extraer datos de varios archivos Excel.
Excel permite resumir el contenido de varios libros en una sola hoja de uno de los libros, lo que se puede hacer mediante macros.
Como pueden observar en la imagen anterior, los archivos de agosto, septiembre y octubre, tienen las mismas columnas, el nombre de la hoja “Hoja1” es el mismo, y en las 3 hojas, los datos empiezan en la fila 2.
Importar datos a una hoja resumen con una macro.
En el libro donde vas a poner la macro, debes tener 2 hojas con los nombres “Valores” y “Resumen”. La macro se encarga de leer los libros y de importar los datos en la hoja “Resumen”. Para lograr el objetivo, previamente, en la hoja llamada “Valores”, se deben capturar los siguientes datos:
- El nombre de la carpeta, donde se encuentran los archivos a importar.
- El nombre de la hoja que contiene los datos.
- La fila inicial de los datos.
- La columna principal.
Cada uno de los datos se debe capturar en su celda correspondiente, como se muestra en la siguiente imagen:
Nota: En la hoja de Excel van unos comentarios para hacer más comprensible la captura de los valores.
Macro para importar libros en Excel.
Sub Importar_Datos()
Set l1 = ThisWorkbook
Set h1 = l1.Sheets("Valores")
Set h2 = l1.Sheets("Resumen")
h2.Cells.ClearContents
'
ruta = h1.[B5]
hoja = h1.[B6]
fila = h1.[B7]
colu = h1.[B8]
'
mensaje = validaciones(ruta, hoja, fila, colu)
If mensaje <> "" Then
MsgBox mensaje, vbExclamation, "IMPORTAR ARCHIVOS"
Exit Sub
End If
'
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.StatusBar = False
Application.Calculation = xlCalculationManual
'
If Right(ruta, 1) <> "\" Then ruta = ruta & "\"
arch = Dir(ruta & "*.xls*")
i = 0
Do While arch <> ""
i = i + 1
Application.StatusBar = "Importando Libro : " & i & " de : " & n
Set l2 = Workbooks.Open(ruta & arch)
existe = False
If IsNumeric(hoja) Then
If l2.Sheets.Count >= hoja Then
existe = True
Set h22 = l2.Sheets(hoja)
Else
End If
Else
For Each h In l2.Sheets
If LCase(h.Name) = LCase(hoja) Then
existe = True
Set h22 = l2.Sheets(hoja)
Exit For
End If
Next
End If
'
If existe Then
u22 = h22.Range(colu & Rows.Count).End(xlUp).Row
u2 = h2.Range("A" & Rows.Count).End(xlUp).Row + 1
h22.Rows(fila & ":" & u22).Copy
h2.Range("A" & u2).PasteSpecial xlValues
End If
'
l2.Close False
arch = Dir()
Loop
'
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.StatusBar = False
Application.Calculation = xlCalculationAutomatic
'
MsgBox "Proceso terminado, archivos importados a la hoja resumen", vbInformation, "IMPORTAR ARCHIVOS"
End Sub
'
Function validaciones(ruta, hoja, fila, colu)
validaciones = ""
If ruta = "" Then
validaciones = "Escribe la Carpeta donde están los archivos"
Exit Function
End If
If Dir(ruta, vbDirectory) = "" Then
validaciones = "No existe la Carpeta"
Exit Function
End If
If hoja = "" Then
validaciones = "Escribe el nombre o número de hoja"
Exit Function
End If
If fila = "" Or Not IsNumeric(fila) Or fila < 1 Then
validaciones = "Escribe la fila inicial"
Exit Function
End If
If colu = "" Or IsNumeric(colu) Then
validaciones = "Escribe la columna principal"
Exit Function
End If
'
If Right(ruta, 1) <> "\" Then ruta = ruta & "\"
arch = Dir(ruta & "*.xls*")
n = 0
Do While arch <> ""
n = n + 1
arch = Dir()
Loop
If n = 0 Then
validaciones = "No hay archivos de excel a importar en la carpeta : " & ruta
Exit Function
End If
End Function
Sigue las Instrucciones para un botón y ejecutar la macro.
- Abre tu libro de Excel
- Para abrir Vba-macros y poder pegar la macro, Presiona Alt + F11
- En el menú elige Insertar / Módulo
- En el panel del lado derecho copia la macro
- Ahora para crear un botón, puedes hacer lo siguiente:
- Inserta una imagen en tu libro, elige del menú Insertar / Imagen / Autoformas
- Elige una imagen y con el Mouse, dentro de tu hoja, presiona click y arrastra el Mouse para hacer grande la imagen.
- Una vez que insertaste la imagen en tu hoja, dale click derecho dentro de la imagen y selecciona: Tamaño y Propiedades. En la ventana que se abre selecciona la pestaña: Propiedades. Desmarca la opción “Imprimir Objeto”. Presiona “Cerrar”
- Vuelve a presionar click derecho dentro de la imagen y ahora selecciona: Asignar macro. Selecciona: Importar_Datos
- Aceptar.
- Para ejecutarla dale click a la imagen.
Una vez capturados los valores puedes ejecutar la macro. El resultado lo verás en la hoja “Resumen”
Eliminar filas en Excel que cumplan una condición
Consolidar datos en Excel con tablas dinámicasForma de citar este artículo (APA):
Gerencie.com. (2021, abril 3). Importar datos de varios libros de Excel en la hoja resumen [Entrada de blog]. Recuperado de https://www.gerencie.com/importar-datos-de-varios-libros-de-excel-en-la-hoja-resumen.html
Liquidador de intereses moratorios Dian
Qué hacer si se incumple la promesa de compraventa
Prescripción de multas y comparendos de tránsito
Propiedad horizontal en Colombia
Aportes voluntarios a pensión
Intereses de cesantías
Únete a nuestro canal de WhatsApp para recibir las últimas actualizaciones
Extraordinario aporte. Muchas gracias por publicarlo. Yo lo utilicé para obtener información de 80 archivos Excel y sin contratiempos.
Gracias por su comentario. Nos alegra saber que la herramienta le fue útil y cumplió con sus expectativas.
Buena tarde. ¿Cómo puedo hacer para que la lectura de los datos inicie en la fila X y termine en la fila Z?
Gracias.
Para que la lectura de los datos inicie en una fila X y termine en una fila Z, debe utilizar un bucle for con el método getRow() para determinar las filas a leer. Supongamos que queremos iniciar la lectura desde la fila 5 hasta la 10. El código sería algo así: En este caso se leen las columnas A, B y C (índices de columna 0 a 2) entre las filas 5 y 10 (índices de fila de 4 a 9).
Para que la lectura de los datos inicie en una fila X y termine en una fila Z, puede utilizar el método range() del objeto DataFrame. El rango se define con las coordenadas de inicio y fin:
df = pd.read_excel('archivo.xlsx', sheet_name='Hoja1', header=0, skiprows=X-1, nrows=Z-X+1)Donde:Buenos días, estimado:
A mí no me permite el macro, ya que registra en amarillo:
For Each h In l2.Sheets
Su ayuda, por favor.
El error que reporta puede deberse a que la variable l2 no está definida correctamente. Asegúrese de tenerla declarada y asignada correctamente, ya sea como un objeto o una cadena con el nombre del libro.
Hola, a mí me pasa lo mismo. ¿Pudiste solucionarlo?
¿Alguien que me ayude a saber cómo llenarlo? Soy nueva en esto y ocupo programar.
Claro, ¿qué necesita saber?
A mí me ocurre igual. ¿Encontraron la solución?
Me sucede lo mismo. ¿Pudiste solucionarlo?
A mí me ocurre lo mismo. ¿Encontraste la solución?
A mí me ocurre lo mismo. ¿Encontraron la solución?
¡Hola!
Me ha funcionado bien para 12 libros, y tengo 91. ¿Me pueden decir qué modificar para que me lea los 91?
¡Gracias!
Hola, por favor revisa la estructura de los archivos que no te considera. El nombre de la pestaña que defines en los parámetros debe ser el mismo en todos los archivos. Además, debe iniciar en la columna y renglón que también definas en dichos parámetros.
De antemano, ¡muchas gracias!
Me pasa que, cuando copio los datos, se borra la primera línea. Mi idea es transportarlo a una tabla con filtros, pero, como ya mencioné, desaparece la primera fila.
Cuando copias datos a Excel y la primera fila desaparece, puede deberse a que esa primera fila ya está ocupada por otra cosa. Si es el caso, puedes copiar los datos en una hoja nueva o insertar filas para evitar que se sobrescriban las celdas de la primera fila.