help with disseminating this VBA code

[TroyBarnes17]

Hi folks,

I have a folder full of subfolders, each subfolder containing one or more files. I want to perform an operation on each of these files. To do this, I am trying to use this code, but am having trouble understanding the code to make it do what I want. I need to pass each individual file from the subfolders to perform the operation I want on it, then move to the next subfolder and the files within it. May I please ask for help with this?

The code is as follows:

Private Function List_Folders_and_Files(folderPath As String, destCell As Range) As Long

    Dim FSO As Object, FSfolder As Object, FSsubfolder As Object, FSfile As Object
    Dim folders As Collection, levels As Collection, subfoldersColl As Collection
    Dim n As Long, c As Long, i As Long
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set folders = New Collection
    Set levels = New Collection
    
    folders.Add FSO.GetFolder(folderPath)
    levels.Add 0
       
    n = 0

    Do While folders.Count > 0
    
        'Remove next folder from top of stack
        
        Set FSfolder = folders(folders.Count): folders.Remove folders.Count
        c = levels(levels.Count): levels.Remove levels.Count
        
        'Output this folder and its files
        
        destCell.Offset(n, c).Value = "'" & FSfolder.Name
        n = n + 1
        c = c + 1
        For Each FSfile In FSfolder.Files
            destCell.Offset(n, c).Value = "'" & FSfile.Name
            n = n + 1
        Next
               
        'Get collection of subfolders in this folder
        
        Set subfoldersColl = New Collection
        For Each FSsubfolder In FSfolder.SubFolders
            subfoldersColl.Add FSsubfolder
        Next
        
        'Loop through collection in reverse order and put each subfolder on top of stack.  As a result, the subfolders are processed and
        'output in the correct ascending ASCII order
        
        For i = subfoldersColl.Count To 1 Step -1
            If folders.Count = 0 Then
                folders.Add subfoldersColl(i)
                levels.Add c
            Else
                folders.Add subfoldersColl(i), , , folders.Count
                levels.Add c, , , levels.Count
            End If
        Next
        Set subfoldersColl = Nothing
                
    Loop
    
    List_Folders_and_Files = n

End Function

 

[Dan_W]

When you say you want help disseminating the VBA code - who do you want to disseminate it to and how? I don't understand.

In terms of dealing with individual files, the following code loops through each of the files, which you can deal with by using the FSFile object:

For Each FSfile In FSfolder.Files
            destCell.Offset(n, c).Value = "'" & FSfile.Name
            n = n + 1
        Next

 

[TroyBarnes17]

When you say you want help disseminating the VBA code - who do you want to disseminate it to and how? I don't understand.

In terms of dealing with individual files, the following code loops through each of the files, which you can deal with by using the FSFile object:

For Each FSfile In FSfolder.Files
            destCell.Offset(n, c).Value = "'" & FSfile.Name
            n = n + 1
        Next

Hi @Dan_W, thanks for your response.

I've modified the code a little since I posted this and am struggling to get the loop to move past the first iteration. I am trying to get it to conduct an operation for each file in each subfolder within my chosen folder. Below is my code:

Option Explicit
Public Sub Main_List_Folders_and_Files()
    List_Folders_and_Files "F:\Pet\Test\"
End Sub
Private Function List_Folders_and_Files(folderPath As String) As Long

    Dim FSO As Object, FSfolder As Object, FSsubfolder As Object, FSfile As Object
    Dim folders As Collection, levels As Collection, subfoldersColl As Collection
    Dim n As Long, c As Long, i As Long, scriptPath As String, imgPath As String
    Dim img As ZiImage, myScript As ZiScript, allScripts As ZiScripts, scriptServe As ZiScriptService
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    Set folders = New Collection
    Set levels = New Collection
    
    scriptPath = "C:\Users\Staff\Documents\Carl Zeiss\Data\Script\generateMasks.ziscript"
    
    folders.Add FSO.GetFolder(folderPath)
    levels.Add 0
    n = 0
    Do While folders.Count > 0
        Set FSfolder = folders(folders.Count): folders.Remove folders.Count
        c = levels(levels.Count): levels.Remove levels.Count
        n = n + 1
        For Each FSfile In FSfolder.Files
            Set scriptServe = New ZiScriptService
            Set allScripts = scriptServe.GetScripts
            Set myScript = allScripts.Add("generateCokeMasks")
            Set img = New ZiImage
            imgPath = folderPath & FSfolder.Name & "\" & FSfile.Name
            img.Load imgPath
            ZiApplication.Documents.AttachAndOpen img
            myScript.Load scriptPath
            scriptServe.RunScript myScript
               
            Set myScript = Nothing
            Set allScripts = Nothing
            Set scriptServe = Nothing
            Set img = Nothing
        Next
        
        Set subfoldersColl = New Collection
        
        For Each FSsubfolder In FSfolder.SubFolders
            subfoldersColl.Add FSsubfolder
        Next
        
        For i = subfoldersColl.Count To 1 Step -1
            If folders.Count = 0 Then
                folders.Add subfoldersColl(i)
                levels.Add c
            Else
                folders.Add subfoldersColl(i), , , folders.Count
                levels.Add c, , , levels.Count
            End If
        Next
        
        Set subfoldersColl = Nothing
                
    Loop

    List_Folders_and_Files = folders.Count
End Function

 

[Dan_W]

Saying it's not working (or something along those lines) doesn't help me help you. In what way is it not working? Is there an error message?

Looking af the code, it seems you're using a 3rd party COM object I'm not familiar with. If the error relates to that, then the you'd be best checking 3rd party provider.

 

[TroyBarnes17]

Saying it's not working (or something along those lines) doesn't help me help you. In what way is it not working? Is there an error message?

Looking af the code, it seems you're using a 3rd party COM object I'm not familiar with. If the error relates to that, then the you'd be best checking 3rd party provider.

Apologies for not being clearer!

I am trying to perform an operation on each file in the folder, then move to the next folder. The problem (I think) is that each operation adds files to the folder, so the loop keeps repeating on the new files in the folder. I need it to ignore the new files and move to the next folder after the original files have been operated on!

Essentially, the loop keeps repeating because the new files keep being saved onto the first folder, the loop doesn't seem to move on. The operation is being conducted on the new files.