Extraer datos de muchos archivos a uno solo
Tengo esta situación: tengo muchos archivos (más de 300) donde los empleados registran una información y esta se almacena de una manera ordenada en la Fila2 de la hoja2 de todos los archivos.
Ahora requiero extraer esa fila2 donde están los datos de todos los archivos y formar un nuevo archivo (Resultados.xls) con todo esa información.
Los archivos individuales están nombrados como el número de identificación de cada empleado, por decir 12345678.xls y todos se encuentran en la misma carpeta.
Tengo un listado de todos los empleados con su número de identificación en el archivo resultados.xls para usarlos en el argumento de la función de búsqueda, pero esta función incluye la dirección o ubicación del archivo, es decir ='C:/mis documentos/registros/[12345678.xls]Dato'$A$2 y aquí es donde necesito reemplazar el dato que está entre corchetes, qué es el número de identificación de los empleados pero no sé cómo hacerlo para que funcione y poder crear el mismo tipo de fórmula para que me traiga todos los registros o datos que necesito.
Ahora requiero extraer esa fila2 donde están los datos de todos los archivos y formar un nuevo archivo (Resultados.xls) con todo esa información.
Los archivos individuales están nombrados como el número de identificación de cada empleado, por decir 12345678.xls y todos se encuentran en la misma carpeta.
Tengo un listado de todos los empleados con su número de identificación en el archivo resultados.xls para usarlos en el argumento de la función de búsqueda, pero esta función incluye la dirección o ubicación del archivo, es decir ='C:/mis documentos/registros/[12345678.xls]Dato'$A$2 y aquí es donde necesito reemplazar el dato que está entre corchetes, qué es el número de identificación de los empleados pero no sé cómo hacerlo para que funcione y poder crear el mismo tipo de fórmula para que me traiga todos los registros o datos que necesito.
Respuesta de kapapri
El asunto es que en el Archivo Resultados.xls debes crear una macro que:
Recorra los empleados
--- Cuenta numero de empleados suponiendo que están en la columna A de la datos donde 1 es la columna A e i son las filas comenzando de la fila 2 pues la 1 debe ser encabezado---
i = 1
Do While Sheets("datos").Cells(i+1, 1) <> ""
If Sheets("datos").Cells(i, 1) = "" Then Exit Do
i = i + 1
Loop
---- ahora tienes el numero de registros (archivos a buscar= i)----
for archivonombre=1 to i
archivo= Sheets("datos").cells(archivonombre+1,1).value 'aca asignas el nombre del archivo
----luego debes abrir cada archivo y extraer el registro que deseas-----
Workbooks.Open Filename:= "c:\mis documentos\registros\" & archivo & ".xls"
------copias el valor de la hoja 2 celda a2 en el archivo resultados-------
Windows("resultados").Sheets ("hoja donde grabaras").Range("A"& J).value= Windows(archivo).Sheets ("hoja2").Range("A2").value
------ Cierras el archivo-----
Windows(archivo). Close
----Abres el siguiente archivo----
next archivonombre
Eso a grandes rasgos.
Recorra los empleados
--- Cuenta numero de empleados suponiendo que están en la columna A de la datos donde 1 es la columna A e i son las filas comenzando de la fila 2 pues la 1 debe ser encabezado---
i = 1
Do While Sheets("datos").Cells(i+1, 1) <> ""
If Sheets("datos").Cells(i, 1) = "" Then Exit Do
i = i + 1
Loop
---- ahora tienes el numero de registros (archivos a buscar= i)----
for archivonombre=1 to i
archivo= Sheets("datos").cells(archivonombre+1,1).value 'aca asignas el nombre del archivo
----luego debes abrir cada archivo y extraer el registro que deseas-----
Workbooks.Open Filename:= "c:\mis documentos\registros\" & archivo & ".xls"
------copias el valor de la hoja 2 celda a2 en el archivo resultados-------
Windows("resultados").Sheets ("hoja donde grabaras").Range("A"& J).value= Windows(archivo).Sheets ("hoja2").Range("A2").value
------ Cierras el archivo-----
Windows(archivo). Close
----Abres el siguiente archivo----
next archivonombre
Eso a grandes rasgos.
Me aquivoque en donde puse J debe decir nombrearchivo+1
Hola, gracias por tu respuesta, ¿no es posible crear una fórmula sin necsidad de usar macros?
Lo siento por ti pero eso te significaría tener abierto los 300 archivos con la consiguiente carga de memoria.
¿Lo deseas de esa manera? Cuando crear la macro es más fácil. Solo tendrías abierto 2 archivos y te permitiría automáticamente abrir más archivos si incorporas más empleados.
Ojo la macro no contempla la no existencia de archivos (código que se puede incluir). Es decir si tienes un empleado y no tiene archivo tratara de abrirlo y se caerá la macro. Pero es un detalle.
¿Lo deseas de esa manera? Cuando crear la macro es más fácil. Solo tendrías abierto 2 archivos y te permitiría automáticamente abrir más archivos si incorporas más empleados.
Ojo la macro no contempla la no existencia de archivos (código que se puede incluir). Es decir si tienes un empleado y no tiene archivo tratara de abrirlo y se caerá la macro. Pero es un detalle.
Bien gracias, pero estoy convencido de qur debe haber una forma de incluir el nombre de archivo en el argumento ='C:\Mis documentos\[12345678.xls]\Dato'!$A$2 y poderlo cambiar dinámicamente desde un listado de nombres de archivo.
1 respuesta más de otro experto
Respuesta de Abraham Valencia
1
Si quieres "hacer algo" para que el numero del archivo no sea "fijo", pues, puedes usar Indirecto, pero, tendrías que tener los 300 libros abiertos! Y ademas los 300 códigos en algún rango, por ejemplo, si los tuviese desde A1 hacia abajo, pondrías:
=Indirecto("'C:\mis documentos\registros\[" & A1 &".xls]Hoja2'!A2")
Así, al "jalar" la fórmula, el A1 se convertiría en A2 y así sucesivamente. Cuestión de modificarla a tu gusto
Eso si, existe un super dilema, tendrás que tener de forma obligaoria los 300 libros abiertos, lo que te consumirá demaseados recursos
Abraham
=Indirecto("'C:\mis documentos\registros\[" & A1 &".xls]Hoja2'!A2")
Así, al "jalar" la fórmula, el A1 se convertiría en A2 y así sucesivamente. Cuestión de modificarla a tu gusto
Eso si, existe un super dilema, tendrás que tener de forma obligaoria los 300 libros abiertos, lo que te consumirá demaseados recursos
Abraham
Si la fórmula contiene todo el path o la ruta por que sería necesario tener los 300 archivos abiertos, ¿no sucedería que al abrir el archivo de resultados me preguntara si deseo actualizar los vínculos?
Muchas gracias por tu respuesta.
Fernando
Muchas gracias por tu respuesta.
Fernando
La función INDIRECTO (como muchas otras), obliga a tener los archivos abiertos, de elo contrario devolverá un error.
Abraham
Abraham