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

En mi primer día en una pequeña empresa de consultoría, me asignaron un proyecto a corto plazo de tres días.El trabajo es sencillo.

Hay muchas carpetas en una unidad de red, cada una con cientos de archivos.

Tuve que seguir estos tres pasos:

1. Seleccione el archivo y copie su nombre.
2. Pegue el nombre en una celda de Excel y presione Entrar.
3. Vaya al siguiente archivo y repita los pasos 1 y 2.

Suena simple, ¿verdad?

Es simple y requiere mucho tiempo.

Lo que hice en tres días se puede hacer en minutos si conozco la técnica correcta.

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

Limitaciones de los métodos mostrados en este tutorial:Usando la técnica que se muestra a continuación, solo podrá obtener los nombres de los archivos en su carpeta de inicio.No obtiene los nombres de los archivos en subcarpetas en la carpeta principal.Aquí hay una forma de obtener nombres de archivo de carpetas y subcarpetas usando Power Query

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

escuché sobre esoFunción ARCHIVOS?

Si no lo hace, no se preocupe.

Es de una hoja de cálculo de Excel de la infancia (fórmulas versión 4).

Si bien esta fórmula no funciona con celdas de hojas de cálculo, aún funciona con rangos con nombre.Usaremos este hecho para obtener una lista de nombres de archivo de la carpeta especificada.

Ahora, suponga que tiene un archivo en su escritorio llamado " Carpeta de prueba ” y desea obtener una lista de nombres de archivo para todos los archivos en esa carpeta.

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

1. En la celda A1, ingrese la dirección completa de la carpeta seguida de un asterisco (*)
   • Por ejemplo, si su carpeta estuviera en la unidad C, la dirección sería algo como
     C:\Usuarios\SU NOMBRE\Escritorio\Carpeta de prueba\*
   • Si no está seguro de cómo obtener la dirección de la carpeta, use el siguiente método:
     • • En la carpeta de la que desea obtener el nombre de archivo, cree un nuevo libro de Excel o abra un libro de trabajo existente en la carpeta y use la siguiente fórmula en cualquier celda.Esta fórmula le dará la dirección de la carpeta con un asterisco (*) al final.Ahora puede copiar y pegar (pegar como valor) esta dirección en cualquier celda del libro de trabajo (A1 en este ejemplo) donde desea el nombre de archivo.

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

         [Si creó un nuevo libro de trabajo en una carpeta para usar la fórmula anterior y obtener la dirección de la carpeta, es posible que deba eliminarlo para que no esté en la lista de archivos de esa carpeta]

2. Vaya a la pestaña "Fórmulas" y haga clic en la opción "Definir nombres".
3. En el cuadro de diálogo Nuevo nombre, use los siguientes detalles
   • Nombre: FileNameList (no dude en elegir el nombre que desee)
   • Alcance: Libro de trabajo
   • Se refiere a: =ARCHIVOS(Hoja1!$A$1)
4. Ahora, para obtener una lista de archivos, usaremos un rango con nombre en la función ÍNDICE.Vaya a la celda A3 (o cualquier celda con la que desee que comience la lista de nombres) e ingrese la siguiente fórmula:

   =SI.ERROR(ÍNDICE(ListaNombreArchivo,FILA()-2),"")

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

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

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

Entonces, la dirección de la carpeta que necesita usar esC:Carpeta UsersSumitDesktopTest*xls*

Del mismo modo, para archivos de documentos de Word, use *doc*

¿Como funciona esto?

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

En la fórmula ÍNDICE, damos los nombres de archivo como una matriz y usamos la función ROW para devolver el primer, segundo, tercer, etc. nombre de archivo.

Tenga en cuenta que uséFILA()-2, ya que empezamos en la tercera línea.Entonces, cuando el número de fila es 4, ROW()-2 será 1 para la primera instancia, 2 para la segunda, y así sucesivamente.

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

Ahora, debo decir que el método anterior es un poco complicado (hay muchos pasos).

Sin embargo, es mucho mejor que hacerlo manualmente.

Sin embargo, si se siente cómodo usando VBA (o si es bueno siguiendo los pasos exactos que enumeraré a continuación), puede crear una función personalizada (UDF) que pueda obtener fácilmente los nombres de todos los archivos.

Beneficios de usar funciones definidas por el usuario (UDF)是Tu可以seráfunciónGuarde en libros de macros personales y reutilícelos fácilmente sin tener que repetir estos pasos una y otra vez.También puede crear complementos y compartir esta funcionalidad con otros.

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

Función GetFileNames(ByVal FolderPath As String) Como variante Dim Result Como variante Dim i Como entero Dim MyFile As Object Dim MyFSO Como objeto Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO. GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 para cada MyFile In MyFiles Result(i) = MyFile.Name i = i + 1 Next MyFile GetFileNames = Función final de resultado

El código anterior creará una función GetFileNames que se puede usar en la hoja de trabajo (como una función normal).

¿Dónde poner este código?

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

¿Cómo usar esta función?

Estos son los pasos para usar esta función en una hoja de trabajo:

• En cualquier celda, ingrese la dirección de la carpeta desde la cual desea enumerar los nombres de los archivos.
• En la celda donde quieres la lista, ingresa la siguiente fórmula (yo la ingresé en la celda A3):

  =SI.ERROR(ÍNDICE(ObtenerNombresArchivo($A$1),FILA()-2),"")

• Copie y pegue la fórmula en la celda de abajo para obtener una lista de todos los archivos.

Tenga en cuenta que ingresé la ubicación de la carpeta en una celda y luego enObtenerNombresDeArchivoEsta celda se utiliza en una fórmula.También puede codificar la dirección de la carpeta en la fórmula de esta manera:

=SI.ERROR(ÍNDICE(ObtenerNombresDeArchivo("C:\Usuarios\SU NOMBRE\Escritorio\Carpeta de prueba"),FILA()-2),"")

En la fórmula anterior, usamos ROW()-2 y comenzamos con la tercera fila.Esto asegura que cuando copie la fórmula en la celda de abajo, se incrementará en 1.Si ingresa la fórmula en la primera fila de la columna, simplemente puede usar ROW().

¿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 ÍNDICE se usa para enumerar un nombre de archivo por celda, comenzando con el primero.

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

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

La fórmula anterior es útil cuando desea obtener una lista de todos los nombres de archivo de una carpeta en Excel.

Sin embargo, ¿qué sucede si solo desea obtener el nombre de un archivo de video, o solo un archivo de Excel, o solo un archivo que contiene una determinada palabra clave?

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

El siguiente código le permitirá obtener todos los nombres de archivo que tienen una palabra clave específica (o una extensión específica) en ellos.

Función GetFileNamesbyExt(ByVal FolderPath As String, FileExt As String) Como variante Dim Result Como variante Dim i Como entero Dim MyFile As Object Dim MyFSO As Object Dim MyFolder As Object Dim MyFiles As Object Set MyFSO = CreateObject("Scripting.FileSystemObject") Set MyFolder = MyFSO.GetFolder(FolderPath) Set MyFiles = MyFolder.Files ReDim Result(1 To MyFiles.Count) i = 1 For Each MyFile In MyFiles If InStr(1, MyFile.Name, FileExt) <> 0 Then Result(i) = MyFile.Name i = i + 1 End If Next MyFile ReDim Preserve Result(1 To i - 1) GetFileNamesbyExt = Result End Function

El código anterior creará una función que se puede usar en la hoja de trabajo" Obtener nombres de archivo por extensión " (al igual que las funciones regulares).

Esta función toma dos parámetros: la ubicación de la carpeta y la palabra clave de la extensión.Devuelve una matriz de nombres de archivo 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 la 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.

• Ve a la pestaña de desarrollador.
• Haga clic en el botón Visual Basic.Esto abrirá el editor de VB.
• En el editor de VB, haga clic con el botón derecho en cualquier objeto 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 método abreviado de teclado Control + R (mantenga presionada la tecla Control y presione "R").
• Haga doble clic en el objeto del módulo y copie y pegue el código anterior en la ventana de código del módulo.

¿Cómo usar esta función?

Estos son los pasos para usar esta función en una hoja de trabajo:

• En cualquier celda, ingrese la dirección de la carpeta desde la cual desea enumerar los nombres de los archivos.Ingresé esto en la celda A1.
• En la celda, ingrese la extensión (o palabra clave) que desea que sean todos los nombres de archivo.Ingresé esto en la celda B1.
• En la celda donde quieres la lista, ingresa la siguiente fórmula (yo la ingresé en la celda A3):

  =SI.ERROR(ÍNDICE(ObtenerNombresDeArchivoporExt($A$1,$B$1),FILA()-2),"")

• Copie y pegue la fórmula en la celda de abajo para obtener una lista de todos los archivos.

Que tal tuCualquier truco de Excel que uses para simplificar tu vida.Me encantaría aprender de ti.¡Compártelo en la sección de comentarios!