Importar datos de varios libros de Excel en la hoja Resumen

Por

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.

Contenido relacionado:
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

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