viernes, agosto 25, 2006

Importar lista de archivos a Excel

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:

41 comentarios:

  1. 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!!!

    ResponderBorrar
  2. 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.

    ResponderBorrar
  3. 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.

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

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

    ResponderBorrar
  6. 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.-

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

    ResponderBorrar
  8. 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.

    ResponderBorrar
  9. 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!

    ResponderBorrar
  10. Hola

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

    ResponderBorrar
  11. 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

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

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

    ResponderBorrar
  14. Esto sirve para 2007.

    Buenisimo tu blog

    ResponderBorrar
  15. 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.

    ResponderBorrar
  16. No me sale :( que estoy haceindo mal?

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

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

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

    ResponderBorrar
  20. 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.

    ResponderBorrar
  21. 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.

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

    ResponderBorrar
  23. 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?

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

    ResponderBorrar
  25. 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

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

    ResponderBorrar
  27. 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í.

    ResponderBorrar
  28. 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.

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

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

    ResponderBorrar
  31. 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 :-)

    ResponderBorrar
  32. 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?

    ResponderBorrar
  33. 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\*.*

    ResponderBorrar
  34. 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

    ResponderBorrar
  35. 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.

    ResponderBorrar
  36. Hola, se puede hacer extensivo este métodoun grupo de carpetas? me refiero a listar los archivos que están en un grupo de carpetas, estas a su vez agrupadas en otra.

    ResponderBorrar
  37. Te sugiero que uses el complemento Power Query. Fijate en esta nota.

    ResponderBorrar
  38. Jorge ayudame, no tengo mayor experiencia y necesito en la forma mas sencilla me indique que y como tengo que hacer para COPIAR EN UNA PAGINA DE TEXTO YA SEA WORD O EXCEL UN LISTADO DE ARCHIVOS Y CARPETAS QUE CONTIENE UNA USB. ANTICIPADAMENTE MUCHAS GRACIAS. RENE VARELA -EL SALVADOR

    ResponderBorrar
  39. Bueno, es lo que explico en esta nota.
    También puedes la técnica que muestro en este nota más reciente que usa el complememnto Power Query (posible en Excel 2010 y posterior).

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.