Obtenga una lista de archivos de Excel en una carpeta usando VBA

excel-vba excel-2010

Question

Necesito obtener los nombres de todos los archivos de Excel en una carpeta y luego realizar cambios en cada archivo. He resuelto la parte de "hacer cambios". ¿Hay alguna manera de obtener una lista de los archivos .xlsx en una carpeta, diga D:/Personal y guárdelo en una matriz de cadenas.

Luego necesito iterar a través de la lista de archivos y ejecutar una macro en cada uno de los archivos que pensé que puedo hacer usando:

Filepath = "D:/Personal/"
For Each i in FileArray
Workbooks.Open(Filepath+i)
Next

Eché un vistazo a this , sin embargo, no pude abrir los archivos porque almacenaba los nombres en formato Variant .

En resumen, ¿cómo puedo usar VBA para obtener una lista de nombres de archivos de Excel en una carpeta específica?

Answer 1

Bien, esto podría funcionar para usted, una función que toma una ruta y devuelve una matriz de nombres de archivo en la carpeta. Puede usar una instrucción if para obtener solo los archivos de Excel al recorrer la matriz.

Function listfiles(ByVal sPath As String)
Dim vaArray As Variant
Dim i As Integer
Dim oFile As Object
Dim oFSO As Object
Dim oFolder As Object
Dim oFiles As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sPath)
Set oFiles = oFolder.Files
If oFiles.Count = 0 Then Exit Function
ReDim vaArray(1 To oFiles.Count)
i = 1
For Each oFile In oFiles
vaArray(i) = oFile.Name
i = i + 1
Next
listfiles = vaArray
End Function

Sería bueno si pudiéramos acceder a los archivos en el objeto de archivos por número de índice, pero eso parece estar roto en VBA por cualquier razón (¿error?).

Answer 2

Con respecto a la respuesta votada, me gustó, excepto que si la matriz resultante "listfiles" se usa en una fórmula de matriz {CSE}, los valores de la lista salen todos en una fila horizontal. Para que salgan en una columna vertical, simplemente hice la matriz bidimensional de la siguiente manera:

ReDim vaArray(1 To oFiles.Count, 0)
i = 1
For Each oFile In oFiles
vaArray(i, 0) = oFile.Name
i = i + 1
Next

Answer 3

Puede usar la función Dir incorporada o el FileSystemObject.

• Función Dir: VBA: Función Dir

• FileSystemObject: VBA: FileSystemObject - Colección de archivos

Cada uno tiene sus propias fortalezas y debilidades.

Función Dir

La función Dir es un método incorporado y liviano para obtener una lista de archivos. Los beneficios de usarlo son:

• Fácil de usar
• Buen rendimiento (es rápido)
• Apoyo Comodín

El truco es comprender la diferencia entre llamarlo con o sin un parámetro. Aquí hay un ejemplo muy simple para demostrar:

Public Sub ListFilesDir(ByVal sPath As String, Optional ByVal sFilter As String)
Dim sFile As String
If Right(sPath, 1) <> "/" Then
sPath = sPath & "/"
End If
If sFilter = "" Then
sFilter = "*.*"
End If
''call with path "initializes" the dir function and returns the first file name
sFile = Dir(sPath & sFilter)
''call it again until there are no more files
Do Until sFile = ""
Debug.Print sFile
''subsequent calls without param return next file name
sFile = Dir
Loop
End Sub

Si modifica alguno de los archivos dentro del bucle, obtendrá resultados impredecibles. Es mejor leer todos los nombres en una matriz de cadenas antes de realizar cualquier operación en los archivos. Aquí hay un ejemplo que se basa en el anterior. Esta es una función que devuelve una matriz de cadenas:

Public Function GetFilesDir(ByVal sPath As String, _
Optional ByVal sFilter As String) As String()
''dynamic array for names
Dim aFileNames() As String
ReDim aFileNames(0)
Dim sFile As String
Dim nCounter As Long
If Right(sPath, 1) <> "/" Then
sPath = sPath & "/"
End If
If sFilter = "" Then
sFilter = "*.*"
End If
''call with path "initializes" the dir function and returns the first file
sFile = Dir(sPath & sFilter)
''call it until there is no filename returned
Do While sFile <> ""
''store the file name in the array
aFileNames(nCounter) = sFile
''subsequent calls without param return next file
sFile = Dir
''make sure your array is large enough for another
nCounter = nCounter + 1
If nCounter > UBound(aFileNames) Then
''preserve the values and grow by reasonable amount for performance
ReDim Preserve aFileNames(UBound(aFileNames) + 255)
End If
Loop
''truncate the array to correct size
If nCounter < UBound(aFileNames) Then
ReDim Preserve aFileNames(0 To nCounter - 1)
End If
''return the array of file names
GetFilesDir = aFileNames()
End Function

Objeto del sistema de archivos

El objeto del sistema de archivos es una biblioteca para operaciones de E / S que admite un modelo de objetos para manipular archivos. Pros para este enfoque:

• Intellisense
• Modelo de objeto robusto

Puede agregar una referencia al "Modelo de objetos de host de Windows Script" (o "Tiempo de ejecución de Windows Scripting") y declarar sus objetos de la siguiente manera:

Public Sub ListFilesFSO(ByVal sPath As String)
Dim oFSO As FileSystemObject
Dim oFolder As Folder
Dim oFile As File
Set oFSO = New FileSystemObject
Set oFolder = oFSO.GetFolder(sPath)
For Each oFile In oFolder.Files
Debug.Print oFile.Name
Next ''oFile
Set oFile = Nothing
Set oFolder = Nothing
Set oFSO = Nothing
End Sub

Si no desea intellisense, puede hacerlo sin establecer una referencia:

Public Sub ListFilesFSO(ByVal sPath As String)
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sPath)
For Each oFile In oFolder.Files
Debug.Print oFile.Name
Next ''oFile
Set oFile = Nothing
Set oFolder = Nothing
Set oFSO = Nothing
End Sub

Answer 4

Si todo lo que quiere es el nombre del archivo sin extensión de archivo

Dim fileNamesCol As New Collection
Dim MyFile As Variant ''Strings and primitive data types aren''t allowed with collection
filePath = "c:/file directory" + "/"
MyFile = Dir$(filePath & "*.xlsx")
Do While MyFile <> ""
fileNamesCol.Add (Replace(MyFile, ".xlsx", ""))
MyFile = Dir$
Loop

Para salir a la hoja de trabajo de Excel

Dim myWs As Worksheet: Set myWs = Sheets("SheetNameToDisplayTo")
Dim ic As Integer: ic = 1
For Each MyFile In fileNamesCol
myWs.Range("A" & ic).Value = fileNamesCol(ic)
ic = ic + 1
Next MyFile

Principalmente basado en la técnica detallada aquí: https://wordmvp.com/FAQs/MacrosVBA/ReadFilesIntoArray.htm

Answer 5

Dim iIndex as Integer
Dim ws As Excel.Worksheet
Dim wb As Workbook
Dim strPath As String
Dim strFile As String
strPath = "D:/Personal/"
strFile = Dir(strPath & "*.xlsx")
Do While strFile <> ""
Set wb = Workbooks.Open(Filename:=strPath & strFile)
For iIndex = 1 To wb.Worksheets.count
Set ws = wb.Worksheets(iIndex)
''Do something here.
Next iIndex
strFile = Dir ''This moves the value of strFile to the next file.
Loop