使用 VBA 获取文件夹中的 Excel 文件列表

Get list of Excel files in a folder using VBA

提问by Buzz Lightyear

I need to get the names of all the Excel files in a folder and then make changes to each file. I've gotten the "make changes" part sorted out. Is there a way to get a list of the .xlsxfiles in one folder, say D:\Personaland store it in a String Array.

我需要获取文件夹中所有 Excel 文件的名称，然后对每个文件进行更改。我已经整理了“进行更改”部分。有没有办法获取一个.xlsx文件夹中的文件列表，比如说D:\Personal并将其存储在一个字符串数组中。

I then need to iterate through the list of files and run a macro on each of the files which I figured I can do using:

然后我需要遍历文件列表并对我认为可以使用的每个文件运行一个宏：

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

I had a look at this, however, I wasn't able to open the files cause it stored the names in Variantformat.

我看过这个，但是，我无法打开文件，因为它以Variant格式存储了名称。

In short, how can I use VBA to get a list of Excel filenames in a specific folder?

简而言之，如何使用 VBA 获取特定文件夹中的 Excel 文件名列表？

回答by Coder375

Ok well this might work for you, a function that takes a path and returns an array of file names in the folder. You could use an if statement to get just the excel files when looping through the array.

好吧，这可能对你有用，一个接受路径并返回文件夹中文件名数组的函数。您可以使用 if 语句在循环数组时仅获取 excel 文件。

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

It would be nice if we could just access the files in the files object by index number but that seems to be broken in VBA for whatever reason (bug?).

如果我们可以通过索引号访问 files 对象中的文件会很好，但由于某种原因（错误？）在 VBA 中似乎被破坏了。

回答by Don Jewett

You can use the built-in Dir function or the FileSystemObject.

您可以使用内置的 Dir 函数或 FileSystemObject。

• Dir Function: VBA: Dir Function

• FileSystemObject: VBA: FileSystemObject - Files Collection

• 目录函数：VBA：目录函数

• FileSystemObject: VBA: FileSystemObject - 文件集合

They each have their own strengths and weaknesses.

他们每个人都有自己的长处和短处。

Dir Function

目录函数

The Dir Function is a built-in, lightweight method to get a list of files. The benefits for using it are:

Dir 函数是一种内置的轻量级方法来获取文件列表。使用它的好处是：

• Easy to Use
• Good performance (it's fast)
• Wildcard support

• 便于使用
• 良好的性能（速度很快）
• 通配符支持

The trick is to understand the difference between calling it with or without a parameter. Here is a very simple example to demonstrate:

诀窍是了解使用或不使用参数调用它之间的区别。这是一个非常简单的示例来演示：

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

If you alter any of the files inside the loop, you will get unpredictable results. It is better to read all the names into an array of strings before doing any operations on the files. Here is an example which builds on the previous one. This is a Function that returns a String Array:

如果您更改循环内的任何文件，您将获得不可预测的结果。在对文件进行任何操作之前，最好将所有名称读入字符串数组。这是一个建立在前一个基础上的示例。这是一个返回字符串数组的函数：

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

File System Object

文件系统对象

The File System Object is a library for IO operations which supports an object-model for manipulating files. Pros for this approach:

文件系统对象是一个用于 IO 操作的库，它支持用于操作文件的对象模型。这种方法的优点：

• Intellisense
• Robust object-model

• 智能感知
• 健壮的对象模型

You can add a reference to to "Windows Script Host Object Model" (or "Windows Scripting Runtime") and declare your objects like so:

您可以添加对“Windows Script Host Object Model”（或“Windows Scripting Runtime”）的引用，并像这样声明您的对象：

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

If you don't want intellisense you can do like so without setting a reference:

如果您不想要智能感知，您可以这样做而无需设置参考：

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

回答by MatthewD

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

回答by gimmegimme

If all you want is the file name without file extension

如果你想要的只是没有文件扩展名的文件名

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

To output to excel worksheet

输出到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

Primarily based on the technique detailed here: https://wordmvp.com/FAQs/MacrosVBA/ReadFilesIntoArray.htm

主要基于此处详述的技术：https: //wordmvp.com/FAQs/MacrosVBA/ReadFilesIntoArray.htm

回答by aVIPtoYou

Regarding the upvoted answer, I liked it except that if the resulting "listfiles" array is used in an array formula {CSE}, the list values come out all in a horizontal row. To make them come out in a vertical column, I simply made the array two dimensional as follows:

关于赞成的答案，我喜欢它，只是如果在数组公式 {CSE} 中使用生成的“listfiles”数组，则列表值全部出现在水平行中。为了使它们出现在垂直列中，我简单地将数组设为二维，如下所示：

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

回答by arun

Sub test()
    Dim FSO As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set folder1 = FSO.GetFolder(FromPath).Files
    FolderPath_1 = "D:\Arun\Macro Files\UK Marco\External Sales Tool for Au\Example Files\"
    Workbooks.Add
    Set Movenamelist = ActiveWorkbook
    For Each fil In folder1
        Movenamelist.Activate
        Range("A100000").End(xlUp).Offset(1, 0).Value = fil
        ActiveCell.Offset(1, 0).Select
    Next
End Sub