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 FunctionSigue 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ámicas
junio 26th, 2019 a las 9:00 am
Hola buenos dias… me gusto la aplicacion funciona super :).
Tengo una pregunta si se puede lograr por medio de este foro.
Al mismo ejercicio…. Se puede en vez de consolidar la información de los tres libros en la hoja resumen … que en esta hoja (resumen ) haga la suma manteniendo el formato?
Porque a lo que he logrado observar la macros desarrolla muy bien su función en recopilar los datos, pero los muestra en hoja resumen por separado.. Que puedo hacer para que me consolide la información pero de los 3 libros me sume todo …
saludos y gracias por ayudarnos a nosotros quienes recién estamos partiendo con el tema de la computación. un abrazo
julio 18th, 2019 a las 6:01 pm
Muchas gracias por tu aportación; el macro funciona excelente, Saludos.
agosto 4th, 2019 a las 3:26 pm
Gracias estimado, muy buena macro!
Solo tengo una duda, si quiero pegar la información a partir de la celda A7 en lugar de la celda A2, como hago el cambio?
Saludos
octubre 2nd, 2019 a las 10:54 am
Después de esta línea:
u2 = h2.Range(“A” & Rows.Count).End(xlUp).Row + 1
Pon esta línea
if u2 < 7 then u2 = 7
octubre 10th, 2019 a las 4:15 am
Muchas gracias por esta magnífica macro!
Hay una cosa que desearía modificar, y es en vez de copiar filas-columnas, es poder seleccionar varias celdas y copiarlas en una fila.
Existen muchos ficheros excel con la misma plantilla, pero quiero copiar varias celdas en una fila (cada excel en una fila diferente)
Gracias
octubre 10th, 2019 a las 10:13 am
Buenas.
He conseguido pegar celdas, pero me lo sobrescribe en la misma línea.
If existe Then
‘u22 = h22.Range(colu & Rows.Count).End(xlUp).Row
u22 = h22.Range(“A” & Rows.Count).End(xlUp).Row + 1
‘If u22 < 1 Then u22 = 1
c = 1
For j = LBound(ori) To UBound(ori)
h22.Range(ori(j)).Copy
h2.Cells(u22, c).PasteSpecial Paste:=xlPasteValues
c = c + 1
Next
'h22.Rows(fila & ":" & u22).Copy
'h2.Range("A" & u2).PasteSpecial xlValues
End If
octubre 19th, 2019 a las 5:50 pm
Hola funciona perfecto ..ahora si quisiera solo traer de todos los libros solo las 4 primeras columnas como deberia hacer …o sea que pueda traer la columna A – B – C y D
Gracias
noviembre 20th, 2019 a las 12:56 pm
Hola buenas, muchas gracias por tu aporte. Pero sucede que al correr la macro siempre me corre la primera hoja, el cual tiene una macro de login, como puedo hacer para que abra la hoja.
Workbooks.Open(Directorio & arch)
noviembre 22nd, 2019 a las 8:19 am
Buen día, espero estas bien,
Estoy intentando cambiar el codigo para un rango definido por las siguentes condiciones:
Cells.Select
Selection.UnMerge
With Selection
.WrapText = False
End With
Range(“A8:O8”).Select
Range(Selection, Selection.End(xlDown).Offset(-2, 0)).Select
Ya que todas las hojas con las condciones anterioes me dan los valores que necesito, por lo tanto estoy eliminando las variables de colum y fila, y estoy asumiendo que la parte del código que debe cambiar es esta:
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
Por esta que estoy arreglando para mi conveniencia:
If existe Then
Cells.Select
Selection.UnMerge
With Selection
.WrapText = False
End With
Range(“A8:O8”).Select
u22 = h22.Range(colu & Rows.Count).End(xlUp).Row
u2 = Range(Selection, Selection.End(xlDown).Offset(-2, 0)).Select
‘u2 = h2.Range(“A” & Rows.Count).End(xlUp).Row + 1
‘h22.Rows(fila & “:” & u22).Copy
‘h2.Range(“A” & u2).PasteSpecial xlValues
End If
Aún estoy intentando hacer pruebas, bueno espero me puedas ayudar ya que solo tengo conociminetos básicos de programación.
De igual forma muchas gracias por el tiempo que le dedicas a hacer estas macros para ayudar a personas como yo.
Saludos
diciembre 10th, 2019 a las 11:33 am
Muy agradecido por su aporte. La macro cumplió su cometido al primer intento y me ahorró mucho que hacer. Saludos.
enero 21st, 2020 a las 5:13 pm
Me da un error:
No existen archivos de excel en la ruta seleccionada
uso mac y copio exactamente la ruta de acceso con la tecla alt
tendre que modificar el codigo ?
de antemano gracias
agosto 30th, 2020 a las 8:37 pm
Hola,
debes confirmar las celdas donde estas poniendo la ruta
Para el ejemplo él las pone en la casilla B5
ruta = h1.[B5]
hoja = h1.[B6]
fila = h1.[B7]
colu = h1.[B8]
septiembre 18th, 2020 a las 10:23 pm
MIsmo error que no encuentra archivos en la ruta (es un MAC) que puedo hacer para lograr que la macro identifique la ruta y se ejecute?
febrero 10th, 2020 a las 2:16 pm
LA macro funciona perfecto, solo me gustaría saber si se puede agregar un distintivo para saber a que libro pertenecen los datos
febrero 12th, 2020 a las 2:45 pm
alguien sabe como modificar esta macro para importas de varias hojas tengo al rededor de 45 hojas en 20 libros de excel
gracias
febrero 27th, 2020 a las 11:01 am
Muchachos, sólo me agrega los datos de un libro
marzo 18th, 2022 a las 4:29 am
A mí me ocurre lo mismo, ¿encontraste la solución?
junio 27th, 2022 a las 8:50 am
A mí me ocurre lo mismo, ¿encontraron la solución?
julio 26th, 2022 a las 9:15 am
A mi me ocurre igual, encontraron la solucion?
marzo 31st, 2020 a las 7:37 am
Hola, si quiero poner en lista los nombres de las hojas(o platillos) y 4 datos específicos de cada celda (Precio, costo, ganancia, % costo) y en la primer columna el nombre del platillo.
A B C D E
1 Platillo $ Menu Costo ganancia % Costo
2 [Nombre de platillo1]!B3 [“”]!C7 [“”]!G7 [“”]!K7 [“”]!N7
3 “”2]B3 etc..
abril 15th, 2020 a las 5:14 pm
Excelente
macro, una pregunta, como puedo haver que funcione con archvios *.csv?
noviembre 4th, 2020 a las 11:00 am
yo cambie esta linea y funcionó:
If Right(ruta, 1) “\” Then ruta = ruta & “\”
arch = Dir(ruta & “*.csv*”)
junio 4th, 2020 a las 2:28 am
Primero puse los valores en la hoja de valores y al correr la macro me dice “escriba la carpeta donde están los archivos”
donde tengo que escribir eso…por que ya lo puse en la hoja de valores como lo indican .
gracias
septiembre 18th, 2021 a las 12:12 am
Te recomiendo entrar a tu Explorador de Archivos, llegar hasta la carpeta en donde tienes los archivos y luego copiar y pegar la ruta que te aparece en la barra de la ruta, aquí puedes hacer un click derecho y te saldrá la opción copiar dirección o copiar ruta.
Con la ruta copiada, abres tu Excel, vas a la hoja Valores y pegas la ruta en la columna siguiente a don de dice Carpeta de archivos. Con eso debería funcionar, he probado esta macro en varias hojas diferentes en equipos diferentes y siempre funciona bien. Suerte!
agosto 20th, 2020 a las 5:00 pm
Buen dia, no sé porque cuando hago correr me sale error y me resalta el sgte párrafo: h2.Range(“A” & u2).PasteSpecial xlValues
agosto 30th, 2020 a las 8:34 pm
Excelente, muchas gracias, tengo dos dudas, alguno me podría ayudar?
Como puedo hacer para poder los títulos? o en su defecto hacer que la base empiece desde la primera fila y no desde la segunda?
gracias.
septiembre 2nd, 2020 a las 2:01 pm
Estimado, muchas gracias por el trabajo, tengo un solo problema, cuando toma la primera base de datos no hay problema, digamos que llega hasta la fila 15, pero la segunda base de datos, de 30 datos, me corta los primeros 15, y la tercera de 40 datos, me corta los primeros 30 y la cuarta al tener menos de 40 datos, no alcanza a aparecer. Si me pudieras ayudar te lo agradecería.
octubre 26th, 2022 a las 12:47 pm
Hola me pasa lo mismo, pudiste solucionarlo?
octubre 4th, 2020 a las 7:33 am
Hola, genial trabajo. ahora ando viendo como poner el nombre del libro en como un campo más porque lastimosamente no existia y los libros se llaman ene, feb, mar,…
octubre 19th, 2020 a las 10:38 am
hola, tengo dos matrices distintas, para dos tipos de formularios distintos, una es de 11 columnas y otra de 96 columnas. como podria solucionar esto, por otro lado no me quedo claro como especificar los nombres de los archivos donde tiene que buscar la info, ya que cada quincena se crea uno nuevo.
de ser necesario podría enviarte los archivos y pagarte para solucionar el problema.
[email protected]
Saludos.
noviembre 4th, 2020 a las 10:54 am
Como hago para que en la hoja resumen no me borre la cabecera cuando realiza la copia?
febrero 3rd, 2021 a las 8:50 am
Hola les hago una pregunta, ya que intente pero no logro hacerlo. no soy un especialista del tema.
lo que necesito es lo siguiente
tengo 2 libros uno que es algo asi
ID descripcion descripcion larga stock
1
4
5
8
y otro similar
ID precio precio 2 ubicación
4
5
1
los datos en ambos libros estan asociados al ID, lo que necesito es hacer un tercer libro con todas las columnas de ambos libros del id 1, 5, etc.
febrero 24th, 2021 a las 3:46 pm
Buenas tardes:
Como podria hacer el cambio pero solo para que copie solo 2 celdas diferentes de 2 pestañas, pero del mismo archivo???
febrero 27th, 2021 a las 5:57 pm
Buenas tardes, si me pueden colaborar, esta misma macro pero aplicarla a varios archivos Excel en diferentes carpetas.
febrero 28th, 2021 a las 10:31 am
Buenas!
Muchas gracias por la gran ayuda!
Tengo un problema, es que me sobreescribe la información en la hoja de Resumen. Es decir, no me queda como a tí en la imagen de la hoja de Resumen que tenés la información de los tres meses, en mi caso sólo veo la información del último archivo Excel.
marzo 22nd, 2021 a las 3:18 pm
Buenas tardes, me gusto mucho tu Macro, muchas gracias por el aporte, sin embargo tengo un pequeño Issue, corro la macro y me extrae los datos de un solo archivo de los tres que tengo en la carpeta,los archivos tienen la misma estructura y la sheet donde esta la info se llama igual sin embargo no logro hacer que traiga la info de los otros archivos, ¿tienes idea de por donde esta el issue?.
junio 27th, 2022 a las 8:54 am
Me sucede lo mismo. ¿pudiste solucionarlo?
agosto 31st, 2021 a las 3:19 pm
Muy buena macro, hay posibilidad de que solo traiga la sheet entera indicada, por ejemplo traiga todas las sheet1 del directorio que uno indique?
septiembre 2nd, 2021 a las 12:44 pm
Estimados ayuda la macros que se expuso funciona perfecto pero necesito extraer solo algunas columnas no toda la hoja, alguien tiene alguna idea como modificar la macro ya existente y agregar esa orden, lo necesito de esa forma ya que la base de datos de donde se extrae la información es muy pesada por ende se queda pegado
gracias
octubre 8th, 2021 a las 6:46 am
Hola, la macro funciona a la perfección, cumple perfectamente lo que dice. Me gustaria saber que parte del codigo debo modificar para que solo extraiga una celda, por ejemplo solo la celda w2 de cada archivo en la ruta.
Muchas gracias
enero 31st, 2022 a las 2:00 pm
Ante mano muchas gracias!!!
Me pasa que cuando copia los datos me borra la primera línea. Mi idea es transportarlo a una tabla con filtros. Pero como ya digo, desaparece la primera fila
marzo 17th, 2022 a las 11:48 am
Hola!
Me ha funcionado bien para 12 libros. Y tengo 91.
Me pueden decir qué modificar para que melea los 91?
Gracias!
octubre 24th, 2022 a las 9:19 am
ALGUIEN QUE ME AYUDE A SABER COMO LLENARLO? SOY NUEVA EN ESTO Y OCUPO PROGRAMAR
diciembre 29th, 2022 a las 11:13 am
Buenos dias estimado,
a mi no me permite el macro ya que regstra en amarillo:
For Each h In l2.Sheets
su ayuda porfavor
enero 24th, 2023 a las 3:56 pm
buena tarde como puedo hacer para que la lectura de los datos inicen en la fila x y termine en la fila z
Gracias