jueves, 17 de mayo de 2012

VBA: Macro para listar ficheros de una carpeta.

Continuando con una serie de artículos hoy contaré cómo poder listar en una Hoja de cálculo de Excel los diferentes ficheros o archivos contenidos en una carpeta concreta de nuestro equipo.
La siguiente macro, bastante sencilla, emplea el objeto FileSystemObject, que habilita el acceso al sistema de todos los archivos de nuestros PC.

Insertaremos en un módulo del Explorador de proyectos del Editor de VBA el siguiente código, formando nuestra macro de Excel:

Sub ListarFicherosCarpeta()
'www.excelforo.com
Dim Ruta As String
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Creamos el objeto FileSystemObject que
'proporciona acceso al sistema de archivos de un equipo
Set fso = CreateObject("Scripting.FileSystemObject")
'Indicamos la ruta de donde vamos a obtener
'los ficheros, en este caso D:\BancoFotos
Ruta = "D:\BancoFotos\"
'definimos dos variables que necesitaremos,
'para recuperar el nombre de la carpeta, y los ficheros que haya dentro
Set Carpeta = fso.GetFolder(Ruta)
Set ficheros = Carpeta.Files
'damos un título en negrita para la celda A1
With Range("A1")
    .Value = "Ficheros de la carpeta " & Ruta
    .Font.Bold = True
End With
'escribimos los ficheros, a partir de A2
Range("A2").Select
For Each archivo In ficheros
    'escribimos el nombre del fichero
    ActiveCell = archivo.Name
    'bajamos una fila
    ActiveCell.Offset(1, 0).Select
Next archivo
ActiveCell.EntireColumn.AutoFit
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Limpiamos los objetos y variables definidas
Set fso = Nothing
Set Carpeta = Nothing
Set ficheros = Nothing

Application.ScreenUpdating = True
End Sub


En este caso, por simplificar, he decidido añadir la Ruta de la carpeta de manera manual, en mi caso, una carpeta donde guardo imágenes que utilizaré en una entrada posterior.
El funcionamiento de la macro comienza permitiendo el acceso al sistema de directorios y carpetas de nuestro equipo de trabajo, luego, tras definir la Ruta exacta que terminaremos abriendo, empleando el método GetFolder, que devuelve un objeto Folder que corresponde a la carpeta de la ruta especificada.El resto es simple, ya que forzamos un texto en la celda A1, y con un proceso FOR EACH...IN...NEXT pasamos por todos los ficheros contenidos en la carpeta definida en 'Ruta' y nos lo traemos a la Hoja de cálculo a partir de la celda A2.
Finalmente auyoajustamos el ancho de la columna y limpiamos las variables definidas con la instrucción SET.

Podemos ver el resultado en la imagen, para mi caso, un listado de cuatro ficheros:

VBA: Macro para listar ficheros de una carpeta.

32 comentarios:

  1. Excelente!, una pregunta, y si quiero traer la fecha de creacion, la fecha de modificacion y el tamaño de cada archivo?, que debo adicionar?

    ResponderEliminar
    Respuestas
    1. Hola Fabio,
      mañana subiré un post al blog dando el detalle del código necesario...
      Slds

      Eliminar
  2. muchas gracias...

    ResponderEliminar
  3. y si quiesiera que de una lista escrita en excel me busque los archivos de una capeta definida y luego los copie los archivos a una carpeta distinta...

    gracias Anticipadas

    ResponderEliminar
    Respuestas
    1. Hola,
      en unos días subiré un post explicando cómo trabajar con los ficheros de una carpeta definida en particular; así a bote pronto, se me ocurriría abrir esos archivos:
      workbook.open Filename:=Ruta
      para luego inmediatamente guardarlos en otra carpeta destino
      activeworkbook.close savechanges:=false, filename:=nuevo nombre
      Espero haberte orientado.
      Slds

      Eliminar
  4. No hay forma que la lista salga ordenada alfabéticamente?

    ResponderEliminar
    Respuestas
    1. Hola Jonathan, ¿cómo estás?
      Espero vayas bien.
      Lo más sencillo para verlos ordenados alfabéticamente sería, una vez devueltos los nombres de los ficheros a la hoja, ordenarlos mediante el objeto Sort. POr ejempl:
      With ActiveWorkbook.Worksheets("Hoja1").Sort
      .SortFields.Clear
      .SortFields.Add Key:=Range("A1:A5"), _
      SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      .SetRange Range("A1:A5")
      .Apply
      End With

      para ordenar las celda A1:A5 de la Hoja1.

      Espero te sirva,
      saludos cordiales

      Eliminar
  5. Una consulta, ¿que debo modificar si quiero obtener los nombres de las carpetas en vez de nombres de archivos en el directorio seleccionado?

    ResponderEliminar
    Respuestas
    1. Hola Cristian, que tal?
      para conseguir las subcarpetas existe una propiedad Subfolders para el método Getfolder.
      Un ejemplo de la ayuda de Excel
      Sub ShowFolderList(folderspec)
      Dim fs, f, f1, fc, s
      Set fs = CreateObject("Scripting.FileSystemObject")
      Set f = fs.GetFolder(folderspec)
      Set fc = f.SubFolders
      For Each f1 in fc
      s = s & f1.name
      s = s & vbCrLf
      Next
      MsgBox s
      End Sub

      El código siguiente demuestra cómo conseguir una colección Folders y cómo recorrer la colección utilizando la instrucción For Each...Next.

      Un saludo

      Eliminar
  6. Muchas gracias, ha sido rápido e indiloro.

    ResponderEliminar
  7. Hola buenas

    Ya pude generar la lista de ficheros Excel en una hoja con la macros. Ahora quiero que tome el nombre del primer archivo y lo abra, trabajar con el y posteriormente me abra el segundo archivo de la lista y así sucesivamente. Gracias de Antemano :)

    ResponderEliminar
  8. Como complemento del comentario anterior te cuento que tengo varios archivos Excel en una carpeta. Tengo otro archivo de Excel que extrae información de cada uno de esos archivos. Asi es que quiero que abra un archivo extraiga la información y lo cierre y luego que abra el siguiente de la lista y lo cierre y asi en adelante. La lista de archivos que tengo en la carpeta cambia. Yo primero use este macros para obtener una lista de los archivos de mi carpeta:

    Sub auto_open()

    'Actualización de Video

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    Application.StatusBar = "Importando Información..."


    'Hoja
    Sheets("CALCULOS").Select

    'Sección 1: Variables a utilizar en la macro
    Dim carpeta, archivos As String
    Dim contador As Integer

    'Sección 2: Lectura de carpeta y ajustes necesarios
    carpeta = InputBox("Ingresa la ruta de la carpeta a importar:")

    If carpeta = "" Then
    Exit Sub
    ElseIf Right(carpeta, 1) <> "\" Then
    carpeta = carpeta & "\"
    End If

    'Sección 3: Preparación de variables
    contador = 1
    archivos = Dir(carpeta)

    'Sección 4: Recorrido de la carpeta
    Do While Len(archivos) > 0
    ActiveSheet.Cells(contador, 1).Value = archivos
    archivos = Dir()
    contador = contador + 1
    Loop

    'Actualización de Video

    Application.ScreenUpdating = True
    Application.StatusBar = "Importación Completa"

    'Hoja
    Sheets("CALCULAR").Select

    End Sub

    Lo programé para que el cuadro de dialogo me pida la dirección de la carpeta al abrir el libro, la especifico y me genera la lista de archivos, ahora quiero que me tome esa lista para ir abriendo cada uno de los archivos e importando la información. Otra cosa relacionada con éste código: (No me genera la lista esta macros hasta que corra otra macros de este mismo libro, y ya intento de nuevo y me crea la lista) Espero haberme explicado. Saludos!

    ResponderEliminar
    Respuestas
    1. Hola!!
      buena macro tienes montada ;-)
      Yo probaría, una vez tengas la ruta de cada archivo de la carpeta, con un procedimiento Workbooks.Open "ruta cada archivo"
      actuar sobre el Workbook abierto, y acabar cerrándonlo (Workbooks.Close)antes de pasar al siguiente archivo de la carpeta.

      La última parte de tu comentario no la entendí, lo siento.
      Espero te pueda servir.
      Slds

      Eliminar
  9. Muchas gracias por tu respuesta,tienes bien atendido el blog. Lo que pasa que esos archivos que quiero abrir cambian de nombre. Por lo cual no puedo especificar rutas fijas.

    Lo que estaba haciendo con el paso anterior es generar una lista de los archivos que se encuentran en el directorio.
    Ahora lo que quiero hacer es usar esa información para dar la orden de abrir cada uno en base a la lista.
    Quizás sea muy avanzado ese paso, no sé si sepas como se puede hacer. Saludos! Gracias por tu ayuda.

    ResponderEliminar
    Respuestas
    1. Hola Nikx,
      si te había entendido; y daría igual que cambiaran de nombre.
      Lo que quería explicarte, es que aplicando el recorrido por la carpeta, y así sabes la ruta y nombre da cada fichero que se encuentra en el directorio; puedes emplear dicha ruta y nombre para ir abriéndolo, trabajando sobre él, y cerrándolo posteriormente; para que el recorrido o bucle pasa a continuación al siguiente archivo o fichero del directorio.

      Slds

      Eliminar
  10. Voy avanzando en el trabajo va por buen camino, Gracias por tu apoyo. Te comento en cuanto quede listo.

    ResponderEliminar
    Respuestas
    1. Niks, estoy buscando lo mismo que vos, pero no logre escribir la parte del codigo que vaya abriendo los archivos del listado. Pudiste hacerlo?

      Eliminar
  11. como poner una cuadro donde yo le este dando la ruta del archivo en la hoja y no estar cambiandolo en el codigo y solo darle clic al boton que ya le asine ala macro y me enliste la ruta que quiero antes en nose un cuadro de texto y no directamente en el codigo

    ResponderEliminar
    Respuestas
    1. Hola, que tal estás... espero te encuentres bien tu también.
      Un código podría ser:
      Sub ParaAbrir()
      www.excelforo.com
      Dim strnName As String, strRuta As String

      strRuta = Range("A1").Value
      strname = "nombre del fichero que queramos"

      direccion = strRuta & "\" & strname & ".xlsx"

      'abrimos el fichero
      Workbooks.Open (direccion)
      End Sub

      según la ruta que aparezca en la celda A1

      Un cordial saludo igualmente

      Eliminar
  12. Hola, te llevo siguiendo hace poco para introducirme en esto del VBA para Excel. Ya tengo hechas algunas plantillas y ahora pretendo tomar algo de esta macro.
    Quiero listar los archivos de una carpeta que se encuentra en nuestro servidor y compararlo con el listado de archivos que debería contener y así visualizar rápido el trabajo que queda por hacer.
    El único problema que tengo es con la ruta de la carpeta del servidor pues el excel deja de responder, ¿es qué no es posible dar permisos para acceder ahí?
    Saludos

    Javier D.

    ResponderEliminar
    Respuestas
    1. Hola Javier,
      gracias por seguirme ;-)

      Respecto al tema de la ruta, inicialmente no debería haber ninguna restricción, no al menos planteada desde VBA.
      Otra cosa diferente es que si exista (entiendo que no es tu caso) mediante permisos de usuario.

      Qué error te genera? o simplemente se queda colgado??.. quizá el problema no sea del servidor??.. has probado a ejecutar tu macro accediendo a una carpeta de tu PC??
      Lo más sencillo es que nuestros códigos tengan algún pequeño (o gran) fallo...

      Es lo único que te puedo decir... prueba a ejecutarla entrando en una carpeta local de tu equipo, y si funciona bien, entonces pregunta al administrador de red, por si hubiera alguna limitación para lo que tratas de hacer.

      Un cordial saludo

      Eliminar
    2. Gracias por tu pronta respuesta,

      Es que hice una variante a tu código. Creé un botón de manera que me apareciera una ventana donde ir introduciéndome en las carpetas hasta dar con la ruta pero es ahí donde se me queda colgado si intento acceder al servidor en cambio para carpetas de mi PC no da problemas.

      Tras estar trasteando con la hoja la única solución que veo por ahora es introducir la ruta a mano.

      Saludos.

      Javier D.

      Eliminar
    3. Pues siento no poder ayudarte más.. no puedo replicar tu situación, pero si en local te funciona, claramente hay algo en la ruta del servidor que hace fallar la macro.
      Es extraño de todas formas, por lo que he entendido, que metiendo la ruta a mano sí la reconozca...
      ???

      suerte!!!
      Un saludo

      Eliminar
  13. Buenas tardes,

    Si quisiera que me devolviera HIPERVINCULOS a esos archivos, que debería hacer?

    ResponderEliminar
    Respuestas
    1. Hola Gabriel,
      para construir Hipervinculos con macros debes emplear el método Hiperlinks.Add
      Puedes ver un ejemplo en
      http://excelforo.blogspot.com.es/2012/08/vba-un-hipervinculo-con-macros-de-excel.html

      Como tienes la ruta completa, dentro de tus variables, darle el Address o dirección del vínculo será sencillo.

      Saludos

      Eliminar
  14. Hola Ismael, muy buena la macro, estoy haciendo una lista de varios archivos que se incrementan cada día, pero tarda bastante en actualizarse. cómo hago para que solo incluya en la lista los nuevos archivos que se generan???

    ResponderEliminar
    Respuestas
    1. Hola!
      vaya cuestión complicada.. diría que localizando las fechas de modificación de los ficheros en la carpeta, ya que hay una propiedad que indica tal cosa. Puedes ver algo al respecto:
      http://excelforo.blogspot.com.es/2012/05/vba-una-macro-en-excel-para-listar-las.html

      Saludos cordiales

      Eliminar
  15. Hola Ismael, no tengo tanta experiencia en esto de excel y de las macros, pero me gustaria saber como uso tu macro, te comento, estoy descargando una cantidad considerable de archivos de excel desde una aplicativo web, y los tengo que organizar por carpetas, a estos tengo que hacerle algunas modifcicaciones como eliminar unas filas, quitar una imagen de logo, quitar un combinar centrar (para empezar) y una vez que tenga todo esto voy a necesitar agregar una o varias columnas basandome en un archivo que trae estas cols nuevas para que todos queden iguales, como uso tu macro para listar los archivos y hacer las primeras modifcaciones que necesito sin necesidad de abrir archivo por archivo?

    te agradezco la ayuda,

    CESAR W GRIMALDOS M
    enithma@hotmail.com

    ResponderEliminar
    Respuestas
    1. Hola Cesar,
      para realizar modificaciones en los términos que expones, tienes que abrir los ficheros inexcusablemente...
      otra cosa es que los abras mediante macros, y si esas modificaciones responden a una regla, esto es, son siempre las mismas, sobre los mismo elementos de los libros, se pueda automatizar...

      Saludos cordiales!!

      Eliminar