Obtenga la lista de nombres de archivo de una carpeta en Excel (con y sin VBA)

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

El trabajo fue sencillo.

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. Pegue ese nombre en una celda en Excel y presione Enter.
3. Vaya al siguiente archivo y repita los pasos 1 y 2.

Suena simple, ¿verdad?

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

Lo que me tomó tres días podría haberse hecho en unos pocos minutos si hubiera sabido las técnicas adecuadas.

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

Limitaciones de los métodos que se muestran en este tutorial: Con las técnicas que se muestran a continuación, solo podrá obtener los nombres de los archivos dentro de la carpeta principal. No obtendrá los nombres de los archivos en las subcarpetas dentro de la carpeta principal. Aquí hay una forma de obtener nombres de archivos de carpetas y subcarpetas usando Power Query

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

Oído hablar de Función ARCHIVOS ¿antes de?

No se preocupe si no lo ha 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).

Si bien esta fórmula no funciona en las celdas de la hoja de trabajo, aún funciona en rangos con nombre. Usaremos este hecho para obtener la lista de nombres de archivos de una carpeta específica.

Ahora, suponga que tiene una carpeta con el nombre "Carpeta de prueba'En el escritorio y desea obtener una lista de nombres de archivo para todos los archivos de esta carpeta.

Estos son los pasos que le darán los nombres de archivo de esta carpeta:

1. En la celda A1, ingrese la dirección completa de la carpeta seguida de un signo de asterisco (*)
   • Por ejemplo, si su carpeta está en la unidad C, entonces la dirección se vería así
     C: \ Users \ Sumit \ Desktop \ Test Folder \ *
   • Si no está seguro de cómo obtener la dirección de la carpeta, utilice el siguiente método:
     • • En la carpeta de la que desea obtener los nombres de los archivos, cree un nuevo libro de Excel o abra un libro existente en la carpeta y use la fórmula siguiente en cualquier celda. Esta fórmula le dará la dirección de la carpeta y agregará un signo de asterisco (*) al final. Ahora puede copiar y pegar (pegar como valor) esta dirección en cualquier celda (A1 en este ejemplo) en el libro de trabajo en el que desea los nombres de archivo.

         = REEMPLAZAR (CELDA ("nombre de archivo"), ENCONTRAR ("[", CELDA ("nombre de archivo")), LEN (CELDA ("nombre de archivo")), "*")

         [Si ha creado un nuevo libro de trabajo en la carpeta para usar la fórmula anterior y obtener la dirección de la carpeta, es posible que desee eliminarlo para que no aparezca en la lista de archivos en esa carpeta]
2. Vaya a la pestaña "Fórmulas" y haga clic en la opción "Definir nombre".
3. En el cuadro de diálogo Nuevo nombre, use los siguientes detalles
   • Nombre: FileNameList (siéntase libre de elegir el nombre que desee)
   • Alcance: Libro de trabajo
   • Se refiere a: = ARCHIVOS (Hoja1! $ A $ 1)
4. Ahora, para obtener la lista de archivos, usaremos el rango con nombre dentro de una función INDICE. Vaya a la celda A3 (o cualquier celda donde desee que comience la lista de nombres) e ingrese la siguiente fórmula:

   = SI.ERROR (INDICE (FileNameList, ROW () - 2), "")

5. Arrastre esto hacia abajo y le dará una lista de todos los nombres de archivo en la carpeta.

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

Si desea obtener todos los archivos con una extensión en particular, simplemente cambie el asterisco con esa extensión de archivo. Por ejemplo, si solo desea archivos de Excel, puede usar * xls * en lugar de *

Entonces, la dirección de la carpeta que necesita usar sería C: \ Users \ Sumit \ Desktop \ Test Folder \ * xls *

De manera similar, para archivos de documentos de Word, use * doc *

¿Como funciona esto?

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

En la fórmula de ÍNDICE, hemos dado los nombres de archivo como la matriz y devolvemos el primer, segundo, tercer nombre de archivo y así sucesivamente usando la función FILA.

Tenga en cuenta que he usado FILA () - 2, ya que comenzamos desde la tercera fila en adelante. Entonces 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.

Vea el video: obtenga una lista de nombres de archivos de una carpeta en Excel

Usando VBA Obtenga una lista de todos los nombres de archivo de una carpeta

Ahora, debo decir que el método anterior es un poco complejo (con varios pasos).

Sin embargo, es mucho mejor que hacerlo manualmente.

Pero si se siente cómodo con el uso de VBA (o si es bueno para seguir los pasos exactos que voy a enumerar a continuación), puede crear una función personalizada (UDF) que puede obtener fácilmente los nombres de todos los archivos.

El beneficio de usar un User Dafinado Ffunción (UDF) es que puede guardar la función en un libro de trabajo de macros personal y reutilizarlo fácilmente sin repetir los pasos una y otra vez. También puede crear un complemento y compartir esta función con otros.

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

Función GetFileNames (ByVal FolderPath como cadena) Como variante Dim Resultado como variante Dim i como entero 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 MyFiles = MyFolder.Files ReDim Resultado (1 a MyFiles.Count) i = 1 Para cada MyFile en MyFiles Resultado (i) = MyFile.Name i = i + 1 Siguiente MyFile GetFileNames = Resultado Función final

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

¿Dónde poner este código?

Siga los pasos a continuación para copiar este código en el Editor de VB.

• Vaya a la pestaña Desarrollador.
• Haga clic en el botón de Visual Basic. Esto abrirá el VB Editor.
• En VB Editor, haga clic con el botón derecho en cualquiera de los objetos del libro de trabajo en el que está trabajando, vaya a Insertar y haga clic en Módulo. Si no ve el Explorador de proyectos, use el atajo de teclado Control + R (mantenga presionada la tecla de control y presione la tecla "R").
• Haga doble clic en el objeto Módulo y copie y pegue el código anterior en la ventana de código del módulo.

¿Cómo utilizar esta función?

A continuación se muestran los pasos para usar esta función en una hoja de trabajo:

• En cualquier celda, ingrese la dirección de carpeta de la carpeta de la que desea enumerar los nombres de archivo.
• En la celda donde desea la lista, ingrese la siguiente fórmula (la estoy ingresando en la celda A3):

  = SI.ERROR (INDICE (GetFileNames ($ A $ 1), FILA () - 2), "")

• Copie y pegue la fórmula en las celdas a continuación para obtener una lista de todos los archivos.

Tenga en cuenta que ingresé la ubicación de la carpeta en una celda y luego usé esa celda en el GetFileNames fórmula. También puede codificar la dirección de la carpeta en la fórmula como se muestra a continuación:

= SI.ERROR (INDICE (GetFileNames ("C: \ Users \ Sumit \ Desktop \ Test Folder"), ROW () - 2), "")

En la fórmula anterior, usamos FILA () - 2 y comenzamos desde la tercera fila en adelante. Esto aseguró que al copiar la fórmula en las celdas a continuación, se incrementará en 1. En caso de que esté ingresando la fórmula en la primera fila de una columna, simplemente puede usar FILA ().

¿Cómo funciona esta fórmula?

La fórmula GetFileNames devuelve una matriz que contiene los nombres de todos los archivos de la carpeta.

La función INDICE se usa para listar un nombre de archivo por celda, comenzando desde el primero.

¡La función IFERROR se usa para devolver un espacio en blanco en lugar de #REF! error que se muestra cuando se copia una fórmula en una celda pero no hay más nombres de archivo para listar.

Usando VBA Obtenga una lista de todos los nombres de archivo con una extensión específica

La fórmula anterior funciona muy bien cuando desea obtener una lista de todos los nombres de archivo de una carpeta en Excel.

Pero, ¿qué sucede si solo desea obtener los nombres de los archivos de video, o solo los archivos de Excel, o solo los nombres de los archivos que contienen una palabra clave específica?

En ese caso, puede utilizar una función ligeramente diferente.

A continuación se muestra el código que le permitirá obtener todos los nombres de archivo con una palabra clave específica (o de una extensión específica).

Función GetFileNamesbyExt (ByVal FolderPath como cadena, FileExt como cadena) Como variante Dim Resultado como variante Dim i como entero Dim MyFile como objeto Dim MyFSO como objeto Dim MyFolder como objeto Dim MyFiles como conjunto de objetos MyFSO = CreateObject ("Scripting.FileSystemObject") Establecer MyFolder = MyFSO.GetFolder (FolderPath) Establecer MyFiles = MyFolder.Files ReDim Resultado (1 a MyFiles.Count) i = 1 para cada MyFile en MyFiles Si InStr (1, MyFile.Name, FileExt) 0 Entonces Resultado (i) = MyFile .Name i = i + 1 End If Next MyFile ReDim Preserve Result (1 To i - 1) GetFileNamesbyExt = Función de fin de resultado

El código anterior creará una función "GetFileNamesbyExt'Que se puede utilizar en las hojas de trabajo (al igual que las funciones normales).

Esta función toma dos argumentos: la ubicación de la carpeta y la palabra clave de extensión. Devuelve una matriz de nombres de archivos que coinciden con la extensión dada. Si no se especifica ninguna extensión o palabra clave, devolverá todos los nombres de archivo en la carpeta especificada.

Sintaxis: = GetFileNamesbyExt ("Ubicación de carpeta", "Extensión")

¿Dónde poner este código?

Siga los pasos a continuación para copiar este código en el Editor de VB.

• Vaya a la pestaña Desarrollador.
• Haga clic en el botón de Visual Basic. Esto abrirá el VB Editor.
• En VB Editor, haga clic con el botón derecho en cualquiera de los objetos del libro de trabajo en el que está trabajando, vaya a Insertar y haga clic en Módulo. Si no ve el Explorador de proyectos, use el atajo de teclado Control + R (mantenga presionada la tecla de control y presione la tecla "R").
• Haga doble clic en el objeto Módulo y copie y pegue el código anterior en la ventana de código del módulo.

¿Cómo utilizar esta función?

A continuación se muestran los pasos para usar esta función en una hoja de trabajo:

• En cualquier celda, ingrese la dirección de carpeta de la carpeta de la que desea enumerar los nombres de archivo. He ingresado esto en la celda A1.
• En una celda, ingrese la extensión (o la palabra clave), para la cual desea todos los nombres de archivo. He ingresado esto en la celda B1.
• En la celda donde desea la lista, ingrese la siguiente fórmula (la estoy ingresando en la celda A3):

  = SI.ERROR (INDICE (GetFileNamesbyExt ($ A $ 1, $ B $ 1), FILA () - 2), "")

• Copie y pegue la fórmula en las celdas a continuación para obtener una lista de todos los archivos.

¿Y usted? Cualquier truco de Excel que utilice para facilitarle la vida. Me encantaría aprender de ti. ¡Compártelo en la sección de comentarios!