Importar datos de varios libros de Excel en la hoja Resumen

Por 19 noviembre, 2017

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.

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.

La macro


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.

Temas relacionados

Gerencie.com en su correo.

Suscríbase y nosotros colocaremos en su bandeja de entrada la mejor información que generamos diariamente.


Déjenos su opinión

21 Opiniones
  1. alexander orrego Dice:

    Buenas noches,
    Quisiera saber en que parte del código hago la modificación para que en la hoja «resumen» no me sobreescriba la primera fila, ya que la tengo con unos encabezados.
    De antemano muchas gracias y quedo atento.

    Responder
    • Dante Amor Dice:

      La macro empieza a escribir en la fila 2.
      Tienes celdas combinadas en la primer fila?

      Responder
      • Petricio Dice:

        La macro esta genial, solo que siempre borra los datos de la fila 1, la cual quiero usar para titulos fijos.
        por favor me podrias indicar como lograr esto?
        gracias.-

        Responder
        • Dante Amor Dice:

          Cambia esta línea
          h2.Cells.ClearContents
          Por esta:
          h2.rows(«2:» & rows.count).ClearContents

  2. Patricio Dice:

    Buenas Tardes.-
    Tengo la misma consulta de arriba descripta.-
    muchas gracias.-

    Responder
    • Dante Amor Dice:

      Cambia esta línea
      h2.Cells.ClearContents
      Por esta:
      h2.rows(«2:» & rows.count).ClearContents

      Responder
      • Maximo Manuel Dice:

        por alguna razon al sustituir esta linea la macro no corre y da error de sintasys.

        otra cosa, si bien pide la columna principal donde hay siempre datos, por ejemplo la columna B, a la hora es agregarlos a Resumen después de el primer archivo agregado, y esta primera hoja tiene en la columna A campos vacíos, comienza a escribir la próxima hoja, después de la ultima celda vacía de A, perdiendo datos en la columna B del archivo anterior.

        por lo demás la macro funciona muy bien. Gracias por el Aporte.

        Responder
        • Chriz Dice:

          Buen dia Maximo,

          De casualidad pudiste reparar el error de sintasys?

  3. Steven Dice:

    Cordial saludo,
    la macro es perfecta pero cuando los archivos a descargar son del mismo nombre no procede con la descarga
    ¿Cómo lograr que descargue todos los archivos sin importar que sean o no del mismo nombre?

    Un abrazo para todos

    Responder
    • Dante Amor Dice:

      A qué te refieres con la descarga?
      Lo que hace la macro es leer archivos de una carpeta, no puedes tener en una misma carpeta 2 archivos con el mismo nombre.

      Responder
  4. Novato Dice:

    Buenas tarde, que parte de la macro tengo que cambiar para copiar solo un rango específico de todas las hojas, por ejemplo solo el rango A3:B6.

    Gracias por la ayuda

    Responder
  5. Sebas Dice:

    Buenas noches.

    ¿Como tendría que modificar la macro si en vez de ir agregando los datos por fila lo hiciera por columna?

    Responder
  6. Zamu Dice:

    Hola!! Oye hice el código tal cual esta y no me agrega los datos como debería. Sólo sube una hoja.

    Responder
  7. Cindy Dice:

    segui los pasos y a la hora de validar, en la hoja resumen no aparece nada!!

    Responder
  8. Roy Dice:

    Como podria sacar info de mas de 3 libros.
    Saludos

    Responder
  9. Roy Dice:

    Dejo mi correo por si me pueden ayudar acerca de la pregunta anterior .
    Cito.
    Como podria sacar información de mas de 3 Archivos?

    Saludos

    Responder
  10. Mabel Dice:

    Hola! estoy intentando utilizar la macro, pero me marca este error Set h2 = l1.Sheets(«Resumen»)

    Responder
  11. Ray Mendoza Dice:

    Muchas gracias por tu aportación la probé y funciona Excelente, Saludos.

    Responder
  12. 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
  13. Carlos MD Dice:

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

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

Conozca sus obligaciones tributarias con nuestro Procedimiento tributario.