Importar datos de varios libros de Excel en la hoja resumen

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.

Extraer datos de varios archivos Excel.

También es común que te pidan el concentrado de todos esos libros en una hoja resumen.

importar-tados-varios-libros-excel-1

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:

  1. El nombre de la carpeta, donde se encuentran los archivos a importar.
  2. El nombre de la hoja que contiene los datos.
  3. La fila inicial de los datos.
  4. La columna principal.

Cada uno de los datos se debe capturar en su celda correspondiente, como se muestra en la siguiente imagen:

importar-datos-varios-libros-excel-2

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.

  1. Abre tu libro de Excel
  2. Para abrir Vba-macros y poder pegar la macro, Presiona Alt + F11
  3. En el menú elige Insertar / Módulo
  4. En el panel del lado derecho copia la macro
  5. Ahora para crear un botón, puedes hacer lo siguiente:
  6. Inserta una imagen en tu libro, elige del menú Insertar / Imagen / Autoformas
  7. Elige una imagen y con el Mouse, dentro de tu hoja, presiona click y arrastra el Mouse para hacer grande la imagen.
  8. 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”
  9. Vuelve a presionar click derecho dentro de la imagen y ahora selecciona: Asignar macro. Selecciona: Importar_Datos
  10. Aceptar.
  11. 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”

Descargar archivo de ejemplo.

Recomendados.

Compártalo en Facebook Compártalo en Twitter

Déjenos su opinión

36 Opiniones
  1. PANCHIKUS Dice:

    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

    Responder
  2. Carlos MD Dice:

    Muchas gracias por tu aportación; el macro funciona excelente, Saludos.

    Responder
  3. Luis Tapia Dice:

    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

    Responder
    • Dante Amor Dice:

      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

      Responder
  4. Jose Luis Dice:

    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

    Responder
    • Jose Luis Dice:

      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

      Responder
  5. Juan Dice:

    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

    Responder
  6. ivan Dice:

    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)

    Responder
  7. Cambio de rango Dice:

    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

    Responder
  8. Nelson Dice:

    Muy agradecido por su aporte. La macro cumplió su cometido al primer intento y me ahorró mucho que hacer. Saludos.

    Responder
  9. Esteban Dice:

    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

    Responder
    • Juan Rueda Dice:

      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]

      Responder
      • Johnwa Dice:

        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?

        Responder
  10. Dwane Dice:

    LA macro funciona perfecto, solo me gustaría saber si se puede agregar un distintivo para saber a que libro pertenecen los datos

    Responder
  11. SERGIO RODRIGUEZ Dice:

    alguien sabe como modificar esta macro para importas de varias hojas tengo al rededor de 45 hojas en 20 libros de excel

    gracias

    Responder
  12. Pablo Dice:

    Muchachos, sólo me agrega los datos de un libro

    Responder
  13. LVillagra Dice:

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

    Responder
  14. ulises Dice:

    Excelente
    macro, una pregunta, como puedo haver que funcione con archvios *.csv?

    Responder
    • gtavo Dice:

      yo cambie esta linea y funcionó:

      If Right(ruta, 1) “\” Then ruta = ruta & “\”
      arch = Dir(ruta & “*.csv*”)

      Responder
  15. Isabel Dice:

    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

    Responder
    • Byron Dice:

      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!

      Responder
  16. jose Dice:

    Buen dia, no sé porque cuando hago correr me sale error y me resalta el sgte párrafo: h2.Range(“A” & u2).PasteSpecial xlValues

    Responder
  17. Juan Rueda Dice:

    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.

    Responder
  18. Marcelo Navarro Dice:

    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.

    Responder
  19. Mike H Dice:

    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,…

    Responder
  20. Jorge Sentis Dice:

    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.

    Responder
  21. gtavo Dice:

    Como hago para que en la hoja resumen no me borre la cabecera cuando realiza la copia?

    Responder
  22. emiliano Dice:

    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.

    Responder
  23. Nery Ramirez Dice:

    Buenas tardes:

    Como podria hacer el cambio pero solo para que copie solo 2 celdas diferentes de 2 pestañas, pero del mismo archivo???

    Responder
  24. AlexGar Dice:

    Buenas tardes, si me pueden colaborar, esta misma macro pero aplicarla a varios archivos Excel en diferentes carpetas.

    Responder
  25. Agustina Dice:

    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.

    Responder
  26. Erik G Dice:

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

    Responder
  27. JuanMa Dice:

    Muy buena macro, hay posibilidad de que solo traiga la sheet entera indicada, por ejemplo traiga todas las sheet1 del directorio que uno indique?

    Responder
  28. roberto Dice:

    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

    Responder
En Gerencie.com está permitido opinar, criticar, discutir, controvertir, disentir, etc., pero debe hacerlo con respeto, sin insultar y sin ofender a otros.

Información legal aplicable para Colombia.

Este sitio web utiliza cookies propias y de terceros para ofrecer un mejor servicio. Al seguir navegando acepta su uso.