A continuación vamos a ver un programa en Excel VBA que recorra todos los libros cerrados y hojas de trabajo en un directorio, y muestra todos los nombres.

Descargar Book1.xlsx, Book2.xlsx, Book3.xlsx, Book4.xlsx y Book5.xlsx y añadirlos a «C: \ test \»

Situación:

Files in a Directory Example

Añadir las siguientes líneas de código en el botón de comando:

  1. En primer lugar, declaramos dos variables de tipo String, un objeto de hoja y dos variables de tipo entero.

Dim directory As String, fileName As String, sheet As Worksheet, i As Integer, j As Integer

  1. Para evitar parpadeo de la pantalla, desactivar la actualización de pantalla.

Application.ScreenUpdating = False

  1. Inicializar el directorio variable. Utilizamos la función Dir para encontrar la primera .xl ?? archivo almacenado en este directorio.

directory = "c:\test\"

fileName = Dir(directory & ".xl??")

Nota: (?) La función Dir apoya el uso de varios caracteres (*) y el carácter único comodines para buscar todos los diferentes tipos de archivos de Excel.

  1. La variable nomArchivo ahora tiene el nombre del primer archivo de Excel que se encuentra en el directorio. Añadir un bucle Do While.

Do While fileName <> ""

Loop

Añadir las siguientes líneas de código (a los 5, 6, 7, 8 y 9) para el bucle.

  1. Inicializar las variables de tipo entero y añadir el nombre del archivo de Excel a la primera columna de la fila i.

i = i + 1

j = 2

Cells(i, 1) = fileName

  1. No existe una forma sencilla de extraer datos (o nombres de hoja) de archivos de Excel cerradas. Por lo tanto, abrimos el archivo de Excel.

Workbooks.Open (directory & fileName)

  1. Agregue todos los nombres de las hojas del archivo de Excel a las otras columnas de la fila i.

For Each sheet In Workbooks(fileName).Worksheets

Workbooks("files-in-a-directory.xlsm").Worksheets(1).Cells(i, j).Value = sheet.Name

j = j + 1

Next sheet

  1. Cierre el archivo de Excel.

Workbooks(fileName).Close

  1. La función Dir es una función especial. Para obtener los otros archivos de Excel, puede utilizar la función Dir de nuevo sin argumentos.

fileName = Dir()

Nota: cuando no hay más nombres de archivo coinciden, la función Dir devuelve una cadena de longitud cero ( «»). Como resultado, Excel VBA dejará el bucle Do While.

  1. Encienda la actualización de pantalla de nuevo (fuera del bucle).

Application.ScreenUpdating = True

Prueba 11. el programa.

Resultado:

Files in a Directory in Excel VBA