VBA(Excel):通过FileSystemObject遍历文件夹中的文件 [英] VBA (Excel): Iterating through files in folder via FileSystemObject

问题描述

好吧，所以我认为自己是Excel VBA专家(即使我有一段时间没有做太多事情了)，但我对此感到迷惑-这显然意味着我非常忽略了这件事.由于我的傲慢:D

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

我正在使用分配给全局变量(称为"myFSO"-我知道的...)的FileSystemObject，并使用其他两个全局变量xFolder和xFile遍历文件夹中的所有文件，并且对文件夹中的每个文件执行操作.

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.

这是代码:

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

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

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

Debug.Print xFolder.Files.Count

在代码中，它返回正确的文件计数，因此一切似乎都可以很好地设置为进入For循环并执行所需的操作.

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 Each ...语句行，但是代码只是运行到例程的末尾，完全遗漏了嵌套在For Each ... Next代码中的全部代码.它甚至都没有转到"Next xFile"行.如果我将循环修改为

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 i = 1 to xFolder.Files.Count

并以这种方式进行处理，就可以了.因此，这不是生死攸关的问题，因为我可以做自己想做的事，我只是想知道是否有人可以说出为什么"For Each"方法不起作用.

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.

推荐答案

这是一个可行的最小示例:

This is a working minimal example:

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

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

这会在调试窗口中列出路径和所有文件名.

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.

这篇关于VBA(Excel):通过FileSystemObject遍历文件夹中的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！