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.

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.

Recomendados.

Compártalo en Facebook Compártalo en Twitter
Gerencie.com en su correo.

Suscríbase y enviaremos a su correo nuestra información mas reciente.


Déjenos su opinión

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