Help with this loop not working as intended

[TroyBarnes17]

Hi folks,

I have a few files in separate subfolders in a given folder and I am trying to perform an operation on all these files. This operation creates a new excel file, which needs to be in the .xls format as this is the one required by another programme for further analysis.

My issue is that once the loop begins, the operation is performed first on the file I want it to happen on, then on the file that's created as a results, and on and on.

Is there a way to get around this please?

Option Explicit
Sub ListFiles()
    Application.screenupdating = False
    ActiveSheet.Cells.Clear
    'Call GetFiles("H:\Pet\Test\")      'end string with path separator ( \ )
End Sub

Private Sub GetFiles(ByVal path As String)
    Dim FSO As Object, Fldr As Object, subF As Object, File As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set Fldr = FSO.GetFolder(path)
   
    For Each subF In Fldr.SubFolders
        GetFiles (subF.path)
    Next subF

    For Each File In Fldr.Files
        If LCase(Right(File.path, 4)) = ".xls" Then
            'do something here
        End If
    Next File

    Set FSO = Nothing
    Set Fldr = Nothing
    Set subF = Nothing
    Set File = Nothing
End Sub

 

[6StringJazzer]

It would be helpful to see how you are saving the new files, instead of just showing a comment.

Each pass through the loop, your code is going to reevaluate the contents of the folder. So if you add new files to that same folder as you go, they are going to get picked up in the loop. One way around this is to build a list of files in the folder before entering the loop, and loop on the list instead of directly in the folder.

Another, easier, way to do it is to simply store your newly created files in a different path than the files you are looping through. However, I can't help with that solution because I don't know anything about how the files are being saved.

If you give some idea of what you prefer we can work on a concrete solution.

 

[TroyBarnes17]

It would be helpful to see how you are saving the new files, instead of just showing a comment.

Each pass through the loop, your code is going to reevaluate the contents of the folder. So if you add new files to that same folder as you go, they are going to get picked up in the loop. One way around this is to build a list of files in the folder before entering the loop, and loop on the list instead of directly in the folder.

Another, easier, way to do it is to simply store your newly created files in a different path than the files you are looping through. However, I can't help with that solution because I don't know anything about how the files are being saved.

If you give some idea of what you prefer we can work on a concrete solution.

Hi, thanks for this. I'm afraid the former isn't an option as the created files need to be saved in the same folder as the original file for the further analysis to work.

One way around this is to build a list of files in the folder before entering the loop, and loop on the list instead of directly in the folder

May I ask how you'd do this in this context please? I've tried this but with no success.

I also tried saving the file extensions onto a separate worksheet but am not aware of how to read this information into the vba application, given the worksheet is stored in a different location to where the application worksheet is and is unaccessible to me at present.

 

[6StringJazzer]

Here is a method for building a list of files in the folder before beginning the loop. Note that I have replaced File.path with FileDictItem, which you must also do everywhere in "do something here".

Private Sub GetFiles(ByVal path As String)

    Dim FSO As Object, Fldr As Object, subF As Object, File As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set Fldr = FSO.GetFolder(path)
    
    Dim FileDict As Object
    Dim FileDictItem As Variant
    Set FileDict = CreateObject("Scripting.Dictionary")
     
    For Each subF In Fldr.SubFolders
        GetFiles (subF.path)
    Next subF

    For Each File In Fldr.Files
        FileDict.Add File.path, 0
    Next File
    
    
    For Each FileDictItem In FileDict.keys
        If LCase(Right(FileDictItem, 4)) = ".xls" Then
'            'do something here
        End If
    Next FileDictItem

    Set FSO = Nothing
    Set Fldr = Nothing
    Set subF = Nothing
    Set File = Nothing
    Set FileDict = Nothing
    
End Sub

 

[TroyBarnes17]

Here is a method for building a list of files in the folder before beginning the loop. Note that I have replaced File.path with FileDictItem, which you must also do everywhere in "do something here".

Private Sub GetFiles(ByVal path As String)

    Dim FSO As Object, Fldr As Object, subF As Object, File As Object
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set Fldr = FSO.GetFolder(path)
   
    Dim FileDict As Object
    Dim FileDictItem As Variant
    Set FileDict = CreateObject("Scripting.Dictionary")
    
    For Each subF In Fldr.SubFolders
        GetFiles (subF.path)
    Next subF

    For Each File In Fldr.Files
        FileDict.Add File.path, 0
    Next File
   
   
    For Each FileDictItem In FileDict.keys
        If LCase(Right(FileDictItem, 4)) = ".xls" Then
'            'do something here
        End If
    Next FileDictItem

    Set FSO = Nothing
    Set Fldr = Nothing
    Set subF = Nothing
    Set File = Nothing
    Set FileDict = Nothing
   
End Sub

this works perfectly, thank you!

 

[6StringJazzer]

You're welcome! If that worked please mark that post as the solution.