VBA (Excel)：通过 FileSystemObject 遍历文件夹中的文件

VBA (Excel): Iterating through files in folder via FileSystemObject

提问by SlashnBurn92

Ok, so I consider myself an Excel VBA expert (even though I've not done much with it for a while) but I'm stumped on this one - that obviously means it is something extremely simple which I've overlooked due to my arrogance :D

好的，所以我认为自己是 Excel VBA 专家（尽管我已经有一段时间没有对它做太多事情了），但我对这个感到困惑 - 这显然意味着它非常简单，但由于我的原因而被我忽略了傲慢:D

I'm using a FileSystemObject assigned to a global variable (called "myFSO" - original, I know...) and using two other global variables - xFolder and xFile - to iterate through all the files within a folder, and perform actions on each file within the folder.

我正在使用分配给全局变量（称为“myFSO”-原始，我知道...）的 FileSystemObject 并使用另外两个全局变量-xFolder 和 xFile-遍历文件夹中的所有文件，并对其执行操作文件夹中的每个文件。

This is the code:

这是代码：

Global myFSO As FileSystemObject
Global xFolder As Scripting.Folder
Global xFile As Scripting.File

Public Sub GetData()

Set bgd = ThisWorkbook.Sheets("BGD")
Set myFSO = New FileSystemObject
Set xFolder = myFSO.GetFolder(bgd.Range("C4").Value)

For Each xFile In xFolder.Files
          <do stuff here>
Next xFile

End Sub

So, when I step through the code, I can see that the xFolder is being assigned correctly. If I add a Watch or insert

因此，当我逐步执行代码时，我可以看到 xFolder 被正确分配。如果我添加 Watch 或插入

Debug.Print xFolder.Files.Count

into the code, it returns the correct file count, so everything seems to be setup fine to go into the For loop and do what it needs to do.

进入代码，它返回正确的文件计数，所以一切似乎都设置得很好，可以进入 For 循环并做它需要做的事情。

Once I step past the For Each... statement line however the code just runs to the end of the routine, completely missing out the whole of the code nested within the For Each... Next code. It doesn't even go to the "Next xFile" line. If I modify the loop to

一旦我越过 For Each... 语句行，代码就会运行到例程的末尾，完全遗漏了嵌套在 For Each... Next 代码中的整个代码。它甚至没有转到“下一个 xFile”行。如果我将循环修改为

For i = 1 to xFolder.Files.Count

and do the process that way, it works OK. So, it's not a matter of life-and-death since I can do what I want to do, I just wanted to know if anyone could tell why the "For Each" method hasn't worked.

并以这种方式执行该过程，它可以正常工作。所以，这不是生死攸关的问题，因为我可以做我想做的事，我只是想知道是否有人能说出为什么“For Each”方法不起作用。

回答by P??

This is a working minimal example:

这是一个工作最小的例子：

1. Create a new file
2. Reference the Microsoft scripting Runtime
3. Create a sheet named BGDand write an existing path into C4e.g. C:\Windows
4. Paste the code below into a module and run it

1. 创建一个新文件
2. 参考 Microsoft 脚本运行时
3. 创建一个名为的工作表并将BGD现有路径写入C4例如C:\Windows
4. 将下面的代码粘贴到一个模块中并运行它

This lists the path and all filenames in the debug window.

这将列出调试窗口中的路径和所有文件名。

Public Sub GetData()
    Dim bgd As Worksheet
    Dim myFSO As FileSystemObject
    Dim xFolder As Scripting.Folder
    Dim xFile As Scripting.File


    Set bgd = ThisWorkbook.Sheets("BGD")
    Set myFSO = New FileSystemObject
    Set xFolder = myFSO.GetFolder(bgd.Range("C4").Value)

    Debug.Print xFolder.Path
    For Each xFile In xFolder.Files
        Debug.Print xFile.Name
    Next xFile

End Sub

If you need the variables locally then declare them locally instead of global. Anything else is very bad practice and leads into errors.

如果您需要本地变量，则在本地而不是全局声明它们。其他任何事情都是非常糟糕的做法，会导致错误。

回答by SlashnBurn92

Ok - turns out the issue was actually with the folder and not the code. As pointed out by @Peh, I should have tested the code with another folder, as well as testing variations of the code itself. When using another folder, the For Eachcode worked fine. As stated in the OP, I just used For i = 1 to xFolder.Files.Countrather than For Eachand got the result I needed, but I prefer the object-led approach of For Eachrather than using an integer/long variable to go through the item count, and wanted to know why that method wasn't working.

好的 - 事实证明问题实际上出在文件夹而不是代码上。正如@Peh 所指出的，我应该用另一个文件夹测试代码，以及测试代码本身的变体。使用另一个文件夹时，For Each代码工作正常。正如 OP 中所述，我只是使用For i = 1 to xFolder.Files.Count而不是For Each得到了我需要的结果，但我更喜欢对象主导的方法For Each而不是使用整数/长变量来检查项目计数，并且想知道为什么该方法不是不工作。

To get the For Eachcode working, I copied the desired files to a different folder and it worked perfectly well. As the original folder was a network location, there was possibly some folder permissions or security setting preventing me from using the code I wanted to.

为了使For Each代码正常工作，我将所需的文件复制到不同的文件夹中，并且运行良好。由于原始文件夹是一个网络位置，可能有一些文件夹权限或安全设置阻止我使用我想要的代码。

回答by ASH

I think you want to list all files in all folders and all sub-folders. Check out this link.

我认为您想列出所有文件夹和所有子文件夹中的所有文件。看看这个链接。

http://www.learnexcelmacro.com/wp/2011/11/how-to-get-list-of-all-files-in-a-folder-and-sub-folders/

http://www.learnexcelmacro.com/wp/2011/11/how-to-get-list-of-all-files-in-a-folder-and-sub-folders/

Download the file; that's the way to go. Once all paths and all file names are listed in your Excel worksheet, you can do all kinds of comparisons, manipulations, and the like.

下载文件；这就是要走的路。一旦所有路径和所有文件名都列在 Excel 工作表中，您就可以进行各种比较、操作等。

 Sub GetFilesInFolder(SourceFolderName As String)  

    '--- For Example:Folder Name= "D:\Folder Name\"  

    Dim FSO As Scripting.FileSystemObject  
    Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder  
    Dim FileItem As Scripting.File  

        Set FSO = New Scripting.FileSystemObject  
        Set SourceFolder = FSO.GetFolder(SourceFolderName)  

        '--- This is for displaying, whereever you want can be configured  

        r = 14  
        For Each FileItem In SourceFolder.Files  
            Cells(r, 2).Formula = r - 13  
            Cells(r, 3).Formula = FileItem.Name  
            Cells(r, 4).Formula = FileItem.Path  
            Cells(r, 5).Formula = FileItem.Size  
            Cells(r, 6).Formula = FileItem.Type  
            Cells(r, 7).Formula = FileItem.DateLastModified  
            Cells(r, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"  

            r = r + 1   ' next row number  
        Next FileItem  

        Set FileItem = Nothing  
        Set SourceFolder = Nothing  
        Set FSO = Nothing  
    End Sub  


Sub GetFilesInFolder(SourceFolderName As String, Subfolders As Boolean)  

'--- For Example:Folder Name= "D:\Folder Name\" and Flag as Yes or No  

Dim FSO As Scripting.FileSystemObject  
Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder  
Dim FileItem As Scripting.File  
'Dim r As Long  
    Set FSO = New Scripting.FileSystemObject  
    Set SourceFolder = FSO.GetFolder(SourceFolderName)  

    '--- This is for displaying, whereever you want can be configured  

    r = 14  
    For Each FileItem In SourceFolder.Files  
        Cells(r, 2).Formula = r - 13  
        Cells(r, 3).Formula = FileItem.Name  
        Cells(r, 4).Formula = FileItem.Path  
        Cells(r, 5).Formula = FileItem.Size  
        Cells(r, 6).Formula = FileItem.Type  
        Cells(r, 7).Formula = FileItem.DateLastModified  
        Cells(r, 8).Formula = "=HYPERLINK(""" & FileItem.Path & """,""" & "Click Here to Open" & """)"  

        r = r + 1   ' next row number  
    Next FileItem  

    '--- This is the Function to go each and Every Folder and get the Files. This is a Nested-Function Calling.  

    If Subfolders = True Then  
        For Each SubFolder In SourceFolder.Subfolders  
            ListFilesInFolder SubFolder.Path, True  
        Next SubFolder  
    End If  

    Set FileItem = Nothing  
    Set SourceFolder = Nothing  
    Set FSO = Nothing  
End Sub