Abrir varios libros Excel para extraer la información y guardarlos en mi libro Excel mediante VB
Necesito crear un código para abrir varios archivos de Excel, extraer la información y copiarla en un archivo con un nombre especifico. El tema es que no siempre son la misma cantidad de archivos ni tampoco la ubicación de estos es la misma. Sí puedo renombrar los archivos con Excel1, Exel2, Excel3 etc. Pensé en utilizar un contador de archivos que empiecen con Excel... Y un bucle que repita la acción tantas veces como de el contador, pero no tuve éxito. ¿Podrían por favor darme pistas de como realizarlo?.
1 Respuesta
H o l a:
No es necesario que los renombres, lo más conveniente es que pongas todos los archivos en una sola carpeta. Después de procesar la macro podrás borrar los archivos de esa carpeta, y para la siguiente ejecución de la macro, colocar nuevamente en la carpeta los archivos que quieres procesar.
El archivo con la macro puede estar en otra carpeta.
El siguiente paso, después de colocar los archivos en una carpeta, es que me digas:
1. Cuál hoja de cada archivo es la que se tiene que leer.
2. De esa hoja u hojas, desde cuál celda y hasta cuál celda se tiene que leer.
3. Una vez que copié la información en dónde la voy a pegar:
3.1. ¿En un libro? ¿Cómo se llama el libro?
3.2. ¿En una hoja? ¿Cómo se llama la hoja?
3.3. ¿En cuál celda?
3.4. La siguiente información del siguiente libro, ¿en dónde se va a pegar? ¿En la misma hoja? En una hoja nueva.
Faltan varios datos que tienes que proporcionar para hacer la macro.
No te preocupes si se hace con un bucle. Lo importante es que proporciones la información con lujo de detalle para realizar la macro.
s a l u d o s
Muchas gracias Dante.
Te paso la información que faltaría.
Los archivos corresponden a informes que voy bajando de un sistema de la empresa y por tal motivo los voy renombrando con un nombre de tres letras y un número.
El tema es que son dos los tipos de informes y de sistemas distintos, y los debo bajar en dos hojas separadas para cada tipo y uno a continuación de otro.
Es decir:
Los archivos SAP1.xls, SAP2.xls, SAP3.xls etc. van en la Hoja SAP de MiArchivo.xls
Los archivos OPE1.xls, OPE2.xls, OPE3.xls etc. van en la Hoja OPE de MiArchivo.xls
Cada vez que realizo la operación utilizo un nuevo archivo (MiArchivo.xls) con el formato correcto, títulos y demás pero sin datos cargados dado que la información de este archivo la lee un Access que realiza cruces de información y por lo tanto el formato de los datos es crucial para que pueda leerlos correctamente. Este archivo lo cargo en una carpeta en el escritorio de mi PC llamada "Informes" junto con los archivos que debo cargar.
Cada uno de los archivos contiene datos encolumnados
En el caso de los Archivos que comienzan con SAP: Las columnas B:H a partir de la línea 6 hasta el final del reporte (pueden variar entre los 200 y 500 registros) se deben copiar en la Hoja SAP de MiArchivo.xls a partir de la celda A2 solo valores.
En el caso de los Archivos que comienzan con OPE: Las columnas A:M a partir de la línea 2 hasta el final del reporte (pueden variar entre los 1000 y 5000 registros) Se deben copiar en la Hoja OPE de MiArchivo.xls solo valores.
Desde ya agradezco tu buena voluntad.
Saludos. Jorge.
H o l a:
Te anexo la macro. La macro la puedes poner en un libro nuevo. El libro nuevo lo puedes guardar en otra carpeta.
Sub AbrirLibros()
'Por.Dante Amor
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set l1 = ThisWorkbook
ruta = "C:\Trabajo\Informes\"
miarch = Dir(ruta & "MiArchivo.xls*")
If miarch = "" Then
MsgBox "No existe MiArchivo, en la carpeta", vbCritical, "ERROR"
Exit Sub
Else
Set l2 = Workbooks.Open(ruta & miarch)
End If
'
arch = Dir(ruta & "*.xls*")
Do While arch <> ""
rango = ""
Select Case UCase(Left(arch, 3))
Case "SAP"
rango = "B6:H"
fil = 6
col = "B"
Set h1 = l2.Sheets("SAP")
Case "OPE"
rango = "A2:M"
fil = 2
col = "A"
Set h1 = l2.Sheets("OPE")
End Select
If rango <> "" Then
Set l3 = Workbooks.Open(ruta & arch)
Set h3 = l3.Sheets(1)
u3 = h3.Range(col & Rows.Count).End(xlUp).Row
If u3 < fil Then u3 = fil
h3.Range(rango & u3).Copy
u1 = h1.Range("A" & Rows.Count).End(xlUp).Row + 1
If u1 < 2 Then u1 = 2
h1.Range("A" & u1).PasteSpecial xlValues
l3.Close
End If
arch = Dir()
Loop
l2.Save
MsgBox "Copias terminadas", vbInformation, "ABRIR LIBROS"
End SubCambia en la macro en esta línea, la carpeta en donde pondrás los archivos:
ruta = "C:\Trabajo\Informes\"
El archivo "MiArchivo.xls" deberá estar en la misma carpeta Informes con los otros archivos, puedes tenerlo abierto o cerrado.
Crea en el nuevo archivo un botón para ejecutar la macro.
Sigue 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: AbrirLibros
- Aceptar.
- Para ejecutarla dale click a la imagen.
Dante:
La macro funciona en verdad excelente. La única dificultad que tengo es que estos archivos SAP que baja nuestro sistema tienen formato Texto (delimitado por tabulaciones), pero al abrirlos los muestra en un Excel. Esto no te lo había especificado en el mensaje anterior. Cuando ejecuto el código tira un error. Probé guardando los archivos como Excel 2007 y funciona perfecto.
¿Hay alguna manera de hacer que reconozca este tipo de extensión?
Desde ya y como siempre muy agradecido por tu tiempo. Admirable!
¿En qué línea de la macro se detiene y qué mensaje de error te aparece?
¿Qué extensión tienen los archivos?
Se detiene cuando comienza a cargar los archivos SAP. Estos archivos tienen extensión .xls pero son Texto delimitado por tabulaciones. Por lo tanto al abrirlos los muestra en un Excel. Si intento guardarlos me indica que son texto y pregunta si quiero mantener ese formato. En cambio los archivos OPE los carga sin problemas.
Saludos
¿Pero qué error te aparece?
¿Y si presionas depurar en qué fila de la macro se pinta de amarillo?
Buenos días Dante:
La línea que aparece en amarillo es la Nro 83:
u1 = h1.Range("A" & Rows.Count).End(xlUp).Row + 1
Te cuento un poco mas de este tipo de archivos: Para poder reproducir el archivo debes hacer un TXT delimitado por tabulaciones. una vez grabado le cambias la extensión por .xls. obviamente te mostrara una advertencia que podría quedar inutilizado. En resumen: Es un archivo de texto con extensión xls. ¿Loco no? pero asi lo baja el sistema.
Si intentas abrir este archivo haciendo DbleClick en él, te mostrara un cuadro de dialogo con el siguiente texto:
El archivo que intenta abrir, ‘SAP1.XLS’, tiene otro formato que el especificado por la extensión del archivo. Compruebe que el archivo no está dañado y procede de una fuente de confianza antes de abrirlo. ¿Desea abrir el archivo ahora?
Si le pongo que sí, lo abre como Excel.
Esto es lo que "no le gusta" al código de acuerdo a mi humilde apreciación.
Saludos.
Otra vez te pregunto.
Qué mensaje de error te aparece.
El error que aparece es:
Se ha producido el error '1004' en tiempo de ejecución:
Error definido por la aplicación o el objeto.
Se detiene en la línea 83 de la macro.
Saludos.
¿La línea 83?
Pero si la macro que te envié solamente tiene 45 líneas, ¿le modificaste algo a la macro?
Perdón. Leí el contador pero no tuve en cuenta que había una macro anterior. el numero de línea es la 36. La secuencia es la siguiente:
u1 = h1.Range("A" & Rows.Count).End(xlUp).Row + 1
Saludos
Envíame 2 archivos de prueba uno OPE y otro SAP para revisarlos.
Mi correo [email protected]
En el asunto del correo escribe tu nombre de usuario “jorgecmr” y el título de esta pregunta.
