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