Obtener la lista de nombres de archivos de una carpeta en Excel (con y sin VBA)

En mi primer día de trabajo en una pequeña empresa de consultoría, me contrataron para un proyecto corto durante tres días.

El trabajo era simple.

Había muchas carpetas en la unidad de red y cada carpeta tenía cientos de archivos.

Tuve que seguir estos tres pasos:

1. Seleccione el archivo y copie su nombre.
2. Pega ese nombre en una celda de Excel y pulsa Intro.
3. Pasa al siguiente archivo y repite el paso 1 & 2.

Suena simple, ¿verdad?

Fue… simple y una gran pérdida de tiempo.

Lo que me llevó tres días podría haberse hecho en unos minutos si supiera las técnicas adecuadas.

En este tutorial, te mostraré diferentes maneras de hacer todo este proceso súper rápido y súper fácil (con y sin VBA).

Limitaciones de los métodos mostrados en este tutorial: Con las técnicas que se muestran a continuación, sólo podrás obtener los nombres de los archivos dentro de la carpeta principal. No obtendrás los nombres de los archivos en las subcarpetas dentro de la carpeta principal. A continuación se muestra una forma de obtener los nombres de los archivos de las carpetas y subcarpetas utilizando Power Query

Uso de la función FILES para obtener una lista de nombres de archivos de una carpeta

¿Has oído hablar de la función ARCHIVOS antes?

No te preocupes si no lo has hecho.

Es de los días de la infancia de las hojas de cálculo de Excel (una fórmula de la versión 4).

Aunque esta fórmula no funciona en las celdas de la hoja de cálculo, sigue funcionando en los rangos de nombres.

Ahora, supongamos que tienes una carpeta con el nombre – ‘ Carpeta de prueba ‘ en el escritorio, y quieres obtener una lista de nombres de archivos para todos los archivos de esta carpeta.

Estos son los pasos que te darán los nombres de los archivos de esta carpeta:

1. En la celda A1, introduzca la dirección completa de la carpeta seguida de un asterisco (*)
   • Por ejemplo, si su carpeta en la unidad C, entonces la dirección se vería como C:UsuariosSumitorioNCarpeta de PruebaN*

   %20

   • %20Si%20no%20está%20seguro%20de%20cómo%20obtener%20la%20dirección%20de%20la%20carpeta,%20utilice%20el%20siguiente%20método:%20
     %20
     • %20
       • %20En%20la%20carpeta%20de%20la%20que%20desea%20obtener%20los%20nombres%20de%20los%20archivos,%20cree%20un%20nuevo%20libro%20de%20trabajo%20de%20Excel%20o%20abra%20un%20libro%20de%20trabajo%20existente%20en%20la%20carpeta%20y%20utilice%20la%20siguiente%20fórmula%20en%20cualquier%20celda.%20Esta%20fórmula%20te%20dará%20la%20dirección%20de%20la%20carpeta%20y%20añade%20un%20asterisco%20(*)%20al%20final.%20Ahora%20puedes%20copiar-pegar%20(pegar%20como%20valor)%20esta%20dirección%20en%20cualquier%20celda%20(A1%20en%20este%20ejemplo)%20del%20libro%20de%20trabajo%20en%20el%20que%20quieres%20los%20nombres%20de%20los%20archivos.%20=REPLACE(CELL(«filename»),FIND(«[«,CELL(«filename»)),LEN(CELL(«filename»)), «*») [Si has creado un nuevo libro de trabajo en la carpeta para usar la fórmula anterior y obtener la dirección de la carpeta, puede que quieras borrarla para que no aparezca en la lista de archivos de esa carpeta]
2. Ve a la pestaña «Fórmulas» y haz clic en la opción «Definir nombre».
3. En el cuadro de diálogo Nuevo Nombre, utilice los siguientes detalles
   • Nombre:FileNameList (siéntete libre de elegir el nombre que quieras)
   • Alcance: Cuaderno de trabajo
   • Se refiere a:=ARCHIVOS(Hoja1!$A$1)
4. Ahora para obtener la lista de archivos, usaremos el rango nombrado dentro de una función de ÍNDICE. Ve a la celda A3 (o a cualquier celda en la que quieras que empiece la lista de nombres) e introduce la siguiente fórmula: =IFERROR(INDEX(FileNameList,ROW()-2),»»»)
5. Arrastra esto hacia abajo y te dará una lista de todos los nombres de los archivos de la carpeta

¿Quieres extraer archivos con una extensión específica?

Si quieres obtener todos los archivos con una extensión en particular, sólo tienes que cambiar el asterisco con esa extensión de archivo. Por ejemplo, si quieres sólo los archivos de Excel, puedes usar *xls* en lugar de *

Así que la dirección de la carpeta que necesitas usar sería C:N-UsersN-SumitN-DesktopN-Carpeta de Pruebas*xls*

De manera similar, para los archivos de documentos de word, use *doc*

¿Cómo funciona esto?

La fórmula FILES recupera los nombres de todos los archivos de la extensión especificada en la carpeta especificada.

En la fórmula de INDICE, hemos dado los nombres de los archivos como la matriz y devolvemos el 1er, 2do, 3er nombre de archivo y así sucesivamente usando la función de ROW.

Note que he usado ROW()-2 , ya que empezamos desde la tercera fila. Así que ROW()-2 sería 1 para la primera instancia, 2 para la segunda instancia cuando el número de fila es 4, y así sucesivamente.

Ver Video – Obtener lista de nombres de archivos de una carpeta en Excel

Usando VBA Obtener una lista de todos los nombres de archivos de una carpeta

Ahora, debo decir que el método anterior es un poco complejo (con un número de pasos).

Sin embargo, es mucho mejor que hacer esto manualmente.

Pero si te sientes cómodo con el uso de VBA (o si eres bueno siguiendo los pasos exactos que voy a enumerar a continuación), puedes crear una función personalizada (UDF) que puede conseguirte fácilmente los nombres de todos los archivos.

La ventaja de utilizar una unción U ser D efined F UDF es que se puede guardar la función en un cuaderno de trabajo personal de macros y reutilizarla fácilmente sin repetir los pasos una y otra vez. También puedes crear un complemento y compartir esta función con otros.

Ahora déjame primero darte el código VBA que creará una función para obtener la lista de todos los nombres de archivos de una carpeta en Excel.

Función GetFileNames(ByVal FolderPath como cadena) como variante Resultado tenue como variante Dim i As Integer Dim MyFile como objeto Dim MyFSO como objeto Dim MyFolder como objeto Dim MyFiles como objeto Set MyFSO = CreateObject(«Scripting.FileSystemObject») Set MyFolder = MyFSO.GetFolder(FolderPath) Establecer Mis Archivos = Mi Carpeta.Archivos Resultado de ReDim (1 a Mis Archivos.Cuenta) i = 1 Por cada archivo en mis archivos Resultado(i) = MiArchivo.Nombre i = i + 1 Próximo MyFile GetFileNames = Resultado Fin de la función

El código anterior creará una función GetFileNames que puede ser utilizada en las hojas de trabajo (al igual que las funciones regulares).

¿Dónde poner este código?

Siga los pasos siguientes para copiar este código en el editor de VB.

• Ve a la pestaña de Desarrolladores. <img alt="Pestaña del desarrollador en la cinta" height="135" sizes="(max-width: 611px) 100vw, 611px" data-pin-media="https://trumpexcel.com/wp-content/uploads/2017/10/Developer-tab-in-the-ribbon.png" nitro-lazy-src="https://nitrocdn.com/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe/assets/static/optimized/rev-896a1a1/wp-content/uploads/2017/10/Developer-tab-in-the-ribbon.png" nitro-lazy-srcset="https://nitrocdn.com/zciqOWZgDznkuqFpvIJsLUeyHlJtlHxe/assets/static/optimized/rev-896a1a1/wp-content/uploads/2017/10/Developer-tab