Importar lista de archivos a Excel

viernes, agosto 25, 2006

Ya vimos que las funciones XLM (macrofunciones Excel 4) nos permiten realizar tareas con fórmulas que de otra manera solo serían posibles con macros.
Una de estas funciones es ARCHIVOS. De acuerdo al archivo de ayuda:

Devuelve una matriz de texto horizontal con los nombres de todos los archivos que se encuentran en el directorio o en la carpeta especificados. Use ARCHIVOS para crear una lista de nombres de archivo sobre los que desea que actúe su macro.

La sintaxis es: ARCHIVOS(directorio)

donde "directorio" especifica los directorios o carpetas que contienen los archivos cuyos nombres se van a devolver.

Un lector del blog me consulta como se puede importar a una hoja de Excel una lista de los archivos de una carpeta.
Supongamos que quiero importar a una hoja de Excel los archivos





Esto la hacemos usando la macrofunción ARCHIVOS (FILES en la versión inglesa).
Los pasos son:
1 - definimos el nombre "Archivos" (Insertar--Nombres--Definir), que contiene esta formula: =ARCHIVOS($A$1).





Prestar atención a la referencia absoluta en la fórmula.

2 - En al celda A1 escribimos:
D:\My Music\Mercedes Sosa\Cantata Sudamericana \*.*

3 - En la celda B1 escribimos la formula: =INDICE(Archivos,FILA())

4 - Copiamos la formula hacia abajo (celdas B2, B3, etc) hasta que recibimos como resultado #REF.

El resultado es el siguiente:




Ahora, seleccionamos todo el rango de la columna B y hacemos Copiar--Pegado especial--Valores, para anular las formulas.

Si queremos usar la fórmula en otras hojas del cuaderno, en el diálogo de definición de nombres, borramos el prefijo Hoja1 en la ventanilla "se refiere a", cuidándonos de dejar el signo "!". De esta manera el nombre Archivos se referirá a la celda A1 de la hoja donde se encuentre, y a la hoja donde fue definido.




En esta entrada hay un enlace para descargar el archivo de ayuda en español.


Categorías: Funciones&Formulas_,

Technorati Tags:

37 comments:

Alan,  31 agosto, 2006 03:53  

Excelente el Blog!!! MARAVILLOSO!!! (Al fin, un blog que no es el diario íntimo de alguien con poca vida...)
INCREIBLE el manejo de Excel!!!
FELICITACIONES!!!

Anónimo,  17 setiembre, 2006 00:42  

Excelente, durante años quise hacerlo sin éxito.
Una sola consulta, ¿tiene algún límite la función? Porque quise utilizarla con una carpeta de 1600 archivos pero solo copió 256, y error (#REF!) a partir de allí.

Gracias.

Jorge L. Dunkelman 17 setiembre, 2006 21:59  

De acuerdo a la ayuda de Excel (en esta entrada hay un enlace para descargar el archivo) la función produce un una matriz horizontal con los nombres de los archivos. Supongo que la limitación de 256 archivos está relacionada con las 256 columnas que tiene cada hoja de Excel.
Me parece que la solución es combinar ARCHIVOS con la función TRANSPONER. Por ejemplo, definir el nombre TrArchivos como =TRANSPONER(ARCHIVOS(Hoja1!$A$1)). No tengo ninguna carpeta con tantos archivos para hacer un test. Intentalo y decime si funciona.

Anónimo,  23 abril, 2007 18:57  

nop, no funciona con transponer. De que otra manera se podra?

Jorge L. Dunkelman 24 abril, 2007 00:22  

Hola, acabo de publicar esta nota con una macro para realizar la tarea. Espero que te sea útil.

Tulio 30 noviembre, 2007 15:23  

excelente!!! muy util ,hace tiempo buscaba algo asi.. Gracias
queria saber como hacer para que tambien me liste los archivos dentro de las distintas carpetas y sub directorios dentro de un cd. Desde ya gracias.-

jairo abraham 16 febrero, 2008 22:59  

disculpa hace mucho estoy tratando de hacerlo pero no me resulta quiero listar una carpeta que tiene archivos con caracteres japoneses ( li intente en una carpeta que no tiene caracteres japoneses pero es lo mismo ) pero no me resulta la operacion llego hasta la pare de escribir =INDICE(Archivos,FILA()) dice que hay que copiar hasta que salga #REF la copio 1 a 1 y nada la copio arrastrando la formula hacia abajo pero lo unico que pasa es que se copia la formula nuevamente osea =INDICE(Archivos,FILA()) hasta dode yo seleccione y ningun nombre de archivo , si me puedes ayudar , gracias

en lo que me queda http://www.subirimagenes.com/imagen-de-Dibujo-1891716.html

Jorge L. Dunkelman 17 febrero, 2008 22:51  

Jairo

si lo que ves en la celda es la fórmula misma (y no el resultado o algo como #N/A o #ERR), da la impresión que el rango donde estas poniendo la fórmula está fomado como Texto.

Anónimo,  10 abril, 2008 18:34  

Jorge, muy bueno tu blog, me ha ayudado mucho!!!

Quisiera saber si es posible hacer un listado de las carpetas, como has hecho con los archivos.

Muchas Gracias!

Jorge L. Dunkelman 10 abril, 2008 23:17  

Hola

en principio, si es posible. Sobre cómo estaré publicando una nota en breve.

Anónimo,  27 enero, 2009 20:35  

jorge, una consulta, estoy utilizano Excel 2003 - genero el nombre , pero cuando termino de armar la formula indice el resultado es #N/A.
Tengo que instalar algo especial para poder utilizar la "ARCHIVOS"

Gracias

Caty

Jorge L. Dunkelman 27 enero, 2009 21:25  

NO, no tenés que instalar nada. Hay algún error en la construcción de la fórmula.

Anónimo,  28 enero, 2009 23:21  

Jorge tengo una duda. Como hago con office 2007 esto mismo?

Anónimo,  28 enero, 2009 23:24  

Esto sirve para 2007.

Buenisimo tu blog

Jorge L. Dunkelman 29 enero, 2009 08:54  

Según el Office Developer Center de Microsoft, las funciones macro XLM siguén funcionando en Excel 2007. Es decir, podés usarlas de la misma manera que enlas versiones anteriores.

Anónimo,  05 marzo, 2009 17:16  

No me sale :( que estoy haceindo mal?

Jorge L. Dunkelman 05 marzo, 2009 18:08  

No se, tendrías que contarme qué estás haciendo, o mejor, mdanrme el archivo.

Anónimo,  06 marzo, 2009 18:12  

defino como archivos, pero la formula me queda igual, estoy usando office 2003

Anónimo,  22 enero, 2010 09:23  

buenas tengo una interrogante como hago el apartado de insertar nombre definir en office 2007, y es compatible esta funcion con excel de office 2007

Jorge L. Dunkelman 22 enero, 2010 13:53  

En Excel 2007 los nombres se definen en la pestaña Fórmulas, en la etiqueta Nombres Definidos-Administrador de Nombres.
En esta nota hay un enlace para descargar guías interactivas que muestran como encontrar los comandos en Excel 2007.

Anónimo,  22 enero, 2010 17:28  

Muchisimas gracias funciona a la perfeccion, me gustaria saber, si existe alguna forma de hacer, que se incluyan en la lista, las carpetas y subcarpetas y los archivos que estas continen.

Jorge L. Dunkelman 22 enero, 2010 18:31  

No, tendrías que usar una macro para esa tarea.

Anónimo,  16 junio, 2012 20:25  

No me funciona en absoluto. Solamente repite el contenido de la celda A1 en la celda B1.
¿Qué estoy haciendo mal?
Por qué no esta funcionando?

Jorge L. Dunkelman 17 junio, 2012 06:58  

Tendrías que describir un poco más qué es lo que estás haciendo, o mandarme el archivo.

Anónimo,  30 junio, 2012 03:49  

muchisimas gracias, corre perfectamente. solo tengo una duda... se puede hacer a la inversa? es decir, yo tengo una lista de nombres de archivos que quiero, lo que necesito como resultado es la RUTA en la que se encuentra... se puede?? gracias

Jorge L. Dunkelman 30 junio, 2012 11:04  

Teóricamente se puede hacer. Tendrías que evaluar cada archivo por separado usando las funciones de búsqueda del Windows.

Orellana,  19 julio, 2012 14:05  

La mayoría de los comentarios son antiguos, pero lo comento para los que lo prueben a partir de ahora. El hecho de que no les funcione a muchos es porque en el punto 3, en lugar de poner una , (coma)entre Archivos y FILA, hay que poner ; (punto y coma), quedando así:
=INDICE(Archivos;FILA())

Puede que esto haya cambiado en versiones de excel posteriores a la usada para hacer este tutorial. Yo he probado con el 2010 y me ha funcionado así.

Jorge L. Dunkelman 19 julio, 2012 15:07  

Orellana, gracias por el comentario. Un pequeño detalle que se suele pasar por alto: los separadores (como o punto y coma) dependen de las definiciones del sistema. En la mayoría de los países europeos y en USA, por ejemplo, el separador de argumentos de funciones es la coma.

Alcides Tapia 19 julio, 2012 19:09  

y para listar los directorios o carpetas como quieran llamarles...¿como lo hago?

Jorge L. Dunkelman 20 julio, 2012 14:03  

Uando funciones del Windows; wl tema es un tanto complejo como para ponerlo en el marco de un comentario.

Susan Calvin 08 noviembre, 2012 13:00  

Algo más sencillo:
1.- Abrir Google Chrome
2.- pegar la ruta de tus ficheros
3.- En la opción Editar, pinchar en Copiar
4.- Ir a Excel y en Pegado especial seleccionar Texto
La ventaja es que también tienes el tamaño y fecha de los archivos en distintas columnas y que es más rápido :-)

Jorge L. Dunkelman 09 noviembre, 2012 12:14  

Susan, gracias por colaborar, pero me parece que hace falta algo.
Después de pegar la ruta de los ficheros en Chrome y apretar Enter, recibimos una página con la lista de los ficheros/archivos que contiene. Antes de pinchar Copiar habría que seleccionar toda la página, no?

Anónimo,  31 diciembre, 2012 08:01  

Wow esta increíble esta fórmula, por fin pude pasar el listado completo a Excel! Gracias!

PD. Para los que obtienen #N/A como resultado de la fórmula, verifiquen que en la ruta de la carpeta deseada no haya espacios entre la última letra y el *.* (eso me sucedía a mi)

D:\My Music\Mercedes Sosa\Cantata Sudamericana\*.*

carol gomezgianine 23 febrero, 2013 03:10  

cunado estoy formulando al colocar la coma en la formulacion me sale error en formulacion y esta igual a la del ejemplo. y si coloco ; solo me genera el b1, no se que estoy haciendo mal.
agradeceria la colaboracion

Jorge L. Dunkelman 23 febrero, 2013 09:01  

Carol,
el uso de la coma o el punto y coma como separador depende de las definiciones regionales. En cuanto a la lista, fijate de haber definido el nombre como muestro en la nota.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP