Editing multiple excel files which are in different folders all united in one folder

Question

I have 200 folders all with different names in a folder. Now, each folder with a different name has a macro excel file (.xlsm). I'm trying to edit all the files at once with a separate file. The code goes like this:

Sub Button1_Click()

Dim wb      As Workbook
Dim ws      As Excel.Worksheet
Dim strPath As String
Dim strFile As String

'Get the directories
strPath = "C:\Users\generaluser\Desktop\testing main folder\"
strFile = Dir(strPath)

'Loop through the dirs
Do While strFile <> ""

    'Open the workbook.
    strFileName = Dir(strPath & strFile & "*.xlsm")
    'Open the workbook.
    Set wb = Workbooks.Open(Filename:=strPath & strFile & "\" & strFileName , ReadOnly:=False)

    'Loop through the sheets.

    Set ws = Application.Worksheets(1)

    'Do whatever
    ws.Range("A1").Interior.ColorIndex = 0



    'Close the workbook
    wb.Close SaveChanges:=True

    'Move to the next dir.
    strFile = Dir
Loop

End Sub

But this doesn't work. I have tried tweaking it but whatever i do either does nothing or causes an error. Can someone please help me get this code to work. (also: "testing main folder" is the folder on my desktop which holds the 200 other folders which hold the .xlsm files.)

[this answer](http://stackoverflow.com/questions/22645347/loop-through-all-subfolders-using-vba) will help you understand how to loop through the subfolders. — Scott Holtzman, Jul 19 '16 at 02:01
Possible duplicate of [Cycle through sub-folders and files in a user-specified root directory](http://stackoverflow.com/questions/14245712/cycle-through-sub-folders-and-files-in-a-user-specified-root-directory) — Comintern, Jul 19 '16 at 02:04

score 0 · Accepted Answer · answered Jul 18 '16 at 21:46

Put Option Explicit at the top of the module. You'll get some compiler errors, one of them being that strFileName isn't declared. This would have been a great clue as to where to look, because the problem is that you're using two variable names that have roughly the same meaning when you read them, and they're getting mixed up.

After you're done fixing the variables, take a look at the documentation for the Dir function. The second issue is that you're also calling Dir multiple times in the loop, which means that you're skipping results.

It should look something more like this:

Dim wb As Workbook
Dim ws As Excel.Worksheet
Dim file As String

'path never changes, so make it a Const
Const path = "C:\Users\generaluser\Desktop\testing main folder\"
'This returns the first result.
file = Dir$(path & "*.xlsm")

Do While file <> vbNullString
    Set wb = Workbooks.Open(Filename:=path & file, ReadOnly:=False)
    Set ws = Application.Worksheets(1)
    'Do whatever
    wb.Close SaveChanges:=True
    'This returns the next result, or vbNullString if none.
    file = Dir$
Loop

this still does not open the `.xlsm` files within the **subfolders** — Scott Holtzman, Jul 19 '16 at 01:56
@ScottHoltzman - I didn't see anything about subfolders in the question, but understanding how to iterate one directory is a certainly a step in the right direction. — Comintern, Jul 19 '16 at 01:59
@ScottHoltzman - Fair enough. Dup of [Cycle through sub-folders and files in a user-specified root directory](http://stackoverflow.com/questions/14245712/cycle-through-sub-folders-and-files-in-a-user-specified-root-directory) and voted to close. — Comintern, Jul 19 '16 at 02:04

Editing multiple excel files which are in different folders all united in one folder

1 Answers1