Excel VBA – loop all folders & subfolders 1

[chevyv8]

Please help, I wrote the following code expecting it to step through every workbook in a specific folder and every subfolder.
But it only found the workbook in the Folder1 subfolder, it does not find any workbooks in the route Data folder or any subfolders within the Folder1 subfolder:

Dim DataFolder As String
Dim WkBook As String
Dim DataRow As Long
Dim ThisWB As Workbook
Dim NegWB As Workbook
Dim Fso As Scripting.FileSystemObject
Dim Folder As Scripting.Folder, Subfolder As Scripting.Folder, File As Scripting.File
Set ThisWB = ActiveWorkbook
Sheets("Andy").Select
DataRow = 2
Set Fso = New Scripting.FileSystemObject
DataFolder = "C:\Data"
Set Folder = Fso.GetFolder(DataFolder)
For Each Subfolder In Folder.subfolders
    For Each File In Subfolder.Files
        WkBook = File.Name
        If Left(WkBook, 1) <> "~" And Right(WkBook, 4) = "xlsm" Then
            Application.DisplayAlerts = False
            Application.AskToUpdateLinks = False
            Workbooks.Open (File)
            Application.AskToUpdateLinks = True
            Application.DisplayAlerts = True
            Set NegWB = ActiveWorkbook
            Sheets("NEG BRIEF").Select
            ThisWB.Sheets("Andy").Cells(DataRow, 1) = WkBook
            ActiveWorkbook.Close savechanges:=False
            ThisWB.Activate
        End If
    Next
Next
Set Fso = Nothing
Set Folder = Nothing




For a test I created a Data folder and the following workbooks and subfolders
C:\Data
  Data.xlsm
  Folder1
    Folder1.xlsm
    SubFolder1
        SubFolder1.xlsm
        SubSub1
           SubSub1.xlsm
    SubFolder2
        SubFolder2.xlsm
        SubSub2
           SubSub2.xlsm

 

[TonyJollans]

Let's look at what your code actually does ..

It gets your root folder ("C:\Data").
It loops through all the subfolders in C:\Data.
There is just one of these: "Folder1"
It loops through each file within subfolder "Folder1".
There is just one of these: "Folder1.xlsm".
No more files in the Folder1 folder, and no more subfolders within the Data folder.
That's it. Job done.​

Now let's look at what you want to do ...

For each folder, starting with your root folder. ...

(a) process each file in the folder.
(b) process each subfolder in the folder in the same way.​

Processing files in a single folder is straightforward. You do whatever you want and move onto the next file.

Processing nested folders is something else altogether because you need to remember where you are in each level of the hierarchy. When you work with (sub)folder "SubFolder1", for example, you don't want folder "Folder1" forgetting where it is: it must remember to do SubFolder2" next. At the same time you need folder "Data" to remember that it's working with "Folder1" so that it knows to work with "Folder2" next (your test data doesn't have this but your folder object doesn't know this until it looks).

This process is called recursion and what you do is write your folder routine as a separate Sub and call it from itself as you work through the levels of the hierarchy. VBA then does all the remembering for you.

Something like this ...

[blue]

[indent][green]' ...
' All the stuff at the beginning[/green]

Set Fso = New Scripting.FileSystemObject
DataFolder = "C:\Data"
Set Folder = Fso.GetFolder(DataFolder)

FolderRoutine Folder

[green]' ...[/green][/indent]

Sub FolderRoutine(Folder as Scripting.Folder)

[indent]Dim File      As Scripting.File
Dim SubFolder As Scripting.Folder

For Each File In Folder.Files
    [green]' Do your File stuff here[/green]
Next

For Each SubFolder In Folder.SubFolders
    FolderRoutine SubFolder [green]' recursively process the subfolder[/green]
Next

[/indent]
End Sub[/blue]

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[chevyv8]

Thanks Tony, I just returned from holiday and saw your reply.
Great explanation and your code works a treat.
Brilliant

 

[Andrzejek]

Don't you think Tony deserves a Star?


---- Andy

There is a great need for a sarcasm font.

 

[chevyv8]

Definitely, but it has been years since I last had to post a question and forgot to give a star at the same time I replied saying thanks and brilliant.

 

[Andrzejek]

replied saying thanks and brilliant" is fine and - I am sure - appreciated.
But the Star gives everybody a hint that this particular post was helpful, including the people who search the Internet for an answer to similar / same issues.


---- Andy

There is a great need for a sarcasm font.