×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel VBA – loop all folders & subfolders

Excel VBA – loop all folders & subfolders

Excel VBA – loop all folders & subfolders

(OP)
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:

CODE -->

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 

RE: Excel VBA – loop all folders & subfolders

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 ...

CODE



' ... ' All the stuff at the beginning Set Fso = New Scripting.FileSystemObject DataFolder = "C:\Data" Set Folder = Fso.GetFolder(DataFolder) FolderRoutine Folder ' ...
Sub FolderRoutine(Folder as Scripting.Folder)
Dim File As Scripting.File Dim SubFolder As Scripting.Folder For Each File In Folder.Files ' Do your File stuff here Next For Each SubFolder In Folder.SubFolders FolderRoutine SubFolder ' recursively process the subfolder Next
End Sub

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

RE: Excel VBA – loop all folders & subfolders

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

RE: Excel VBA – loop all folders & subfolders

Don't you think Tony deserves a Star?


---- Andy

There is a great need for a sarcasm font.

RE: Excel VBA – loop all folders & subfolders

(OP)
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.

RE: Excel VBA – loop all folders & subfolders

"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. smile


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close