wake-up-neo.com

Abrufen einer Liste von Excel-Dateien in einem Ordner mithilfe von VBA

Ich muss die Namen aller Excel-Dateien in einem Ordner abrufen und dann Änderungen an jeder Datei vornehmen. Ich habe den Teil "Änderungen vornehmen" aussortiert. Gibt es eine Möglichkeit, eine Liste der .xlsx Dateien in einem Ordner, sagen D:\Personal und speichern Sie es in einem String-Array.

Ich muss dann die Liste der Dateien durchlaufen und ein Makro für jede der Dateien ausführen, von denen ich dachte, dass ich sie verwenden kann:

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

Ich habe mir this angesehen, konnte die Dateien jedoch nicht öffnen, da die Namen im Format Variant gespeichert waren.

Kurz gesagt, wie kann ich mit VBA eine Liste der Excel-Dateinamen in einem bestimmten Ordner abrufen?

18
Buzz Lightyear

Ok, das könnte für Sie funktionieren, eine Funktion, die einen Pfad verwendet und ein Array von Dateinamen im Ordner zurückgibt. Sie können eine if-Anweisung verwenden, um beim Durchlaufen des Arrays nur die Excel-Dateien abzurufen.

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

Es wäre schön, wenn wir nur über die Indexnummer auf die Dateien im Dateiobjekt zugreifen könnten, aber das scheint in VBA aus irgendeinem Grund (Fehler?) Nicht zu funktionieren.

35
Coder375

Sie können die integrierte Dir-Funktion oder das FileSystemObject verwenden.

Sie haben jeweils ihre eigenen Stärken und Schwächen.

Dir-Funktion

Die Dir-Funktion ist eine integrierte, einfache Methode zum Abrufen einer Liste von Dateien. Die Vorteile bei der Verwendung sind:

  • Einfach zu verwenden
  • Gute Leistung (es ist schnell)
  • Wildcard-Unterstützung

Der Trick besteht darin, den Unterschied zwischen dem Aufrufen mit oder ohne Parameter zu verstehen. Hier ist ein sehr einfaches Beispiel zur Veranschaulichung:

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

Wenn Sie eine der Dateien in der Schleife ändern, erhalten Sie unvorhersehbare Ergebnisse. Es ist besser, alle Namen in ein Array von Zeichenfolgen einzulesen, bevor Operationen an den Dateien ausgeführt werden. Hier ist ein Beispiel, das auf dem vorherigen aufbaut. Dies ist eine Funktion, die ein String-Array zurückgibt:

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

Dateisystemobjekt

Das File System Object ist eine Bibliothek für IO Operationen, die ein Objektmodell zur Bearbeitung von Dateien unterstützt. Vorteile für diesen Ansatz:

  • Intellisense
  • Robustes Objektmodell

Sie können einen Verweis auf "Windows Script Host Object Model" (oder "Windows Scripting Runtime") hinzufügen und Ihre Objekte folgendermaßen deklarieren:

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

Wenn Sie Intellisense nicht möchten, können Sie dies tun, ohne eine Referenz zu setzen:

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
25
Don Jewett
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
5
MatthewD

Wenn alles was Sie wollen, ist der Dateiname ohne Dateierweiterung

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

Ausgabe in ein Excel-Arbeitsblatt

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

Hauptsächlich basierend auf der hier beschriebenen Technik: https://wordmvp.com/FAQs/MacrosVBA/ReadFilesIntoArray.htm

1
gimmegimme

In Bezug auf die hochgestufte Antwort hat es mir gefallen, außer dass, wenn das resultierende "listfiles" -Array in einer Array-Formel {CSE} verwendet wird, die Listenwerte alle in einer horizontalen Zeile ausgegeben werden. Damit sie in einer vertikalen Spalte angezeigt werden, habe ich das Array einfach wie folgt zweidimensional gemacht:

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