vba - Iterating through files in folder via FileSystemObject

Question

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

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.

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

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.

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

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.

Answer 1

This is a working minimal example:

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.