Excel VB to loop around folder and run macro

Question

I tried this code and seems like it's not working. Can anyone tell me why?

Sub ProcessFiles()
    Dim Filename, Pathname As String
    Dim wb As Workbook

    Pathname = ActiveWorkbook.Path & "C:\Macro\"
    Filename = Dir(Pathname & "*.xls")
    Do While Filename <> ""
        Set wb = Workbooks.Open(Pathname & Filename)
        DoWork wb
        wb.Close SaveChanges:=True
        Filename = Dir()
    Loop
End Sub

Sub DoWork(wb As Workbook)
    With wb
       Range("A1").Select
        ActiveCell.FormulaR1C1 = "Name"
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "Anil"
        Range("A2").Select
    End With
End Sub

possible duplicate of [Run same excel macro on multiple excel files](http://stackoverflow.com/questions/14766238/run-same-excel-macro-on-multiple-excel-files) — brettdj, Feb 09 '13 at 05:34
The answer has been given in your original post of this question. — peterm, Feb 09 '13 at 05:55

score 2 · Answer 1 · answered Feb 09 '13 at 04:37

I've found 2 areas of concern. First,

Pathname = ActiveWorkbook.Path & "C:\Macro\"

This will return something similar to "C:\Users\ [USER NAME] \ [FILELOCATION]C:\Macro\" (This was my output C:\Users\Developer\DesktopC:\Macro\")

An easy way to check this is to place

MsgBox Pathname

after

Pathname = ActiveWorkbook.Path & "C:\Macro\"

like

Pathname = ActiveWorkbook.Path & "C:\Macro\"

MsgBox Pathname

and run the code. This will show you the Pathname which is the main issue. If the excel files you plan to open/modify are in the same directory as the ActiveWorkbook, this will work fine

Pathname = ActiveWorkbook.Path & "\"

(remove Msgbox Pathname when you are done.)

The second area of concern is the Do While Loop. You currently have it set to only do the loop if the Filename is not equal to "". However, when you enter the loop, you set Filename to "" with Filename = Dir(). If you do not plan to iterate through the folder, you can use a single IF statement. If you do plan to iterate through the folder, continue with the Do While statement but the Filename = Dir() will need to be modified eventually.

Hope that helps.

score 1 · Answer 2 · answered Feb 09 '13 at 04:39

1

This is probably your issue:

Pathname = ActiveWorkbook.Path & "C:\Macro\"

ActiveWorkbook.Path already returns C:\YourPathToWorkbook so your Pathname is probably something like:

C:\yourpathtoworkbookC:\Macro\

You should step through the code with F8 and hover over the variables when they are highlighted in yellow.

answered Feb 09 '13 at 04:39

Joseph

5,070
1
25
26

1

+1 for helpful reminder to use debugger. You can "hover", or (my preference) you can add variables and expressions to the Watch window - so you can see how multiple variables change without hovering (also, hovering doesn't always get you what you want). You can also use Debug.print(your expression) and look in the "Immediate" window (which you can find under "View" menu). – Floris Feb 09 '13 at 15:22
@Floris Completely agree with the debugging aspect. I actually prefer the locals window myself, but I have noticed that getting an answer on here is really difficult unless you're speedy-quick so, for the sake of brevity, I left that detail out – Joseph Feb 09 '13 at 23:57

Excel VB to loop around folder and run macro

2 Answers2