Thread: Macro to run macros in all files in a specific folder

You can call a macro in a different workbook with code like the following - this will run the macro called runSort in the file C:\Pilot\test.xlsm. You could also place this code in a new workbook and put it into the workbook_open event that way you only have to open one workbook to run a series of macros.

Sub runMacros()
Application.Run "'C:\Pilot\test.xlsm'!runSort"
End Sub

You may need to change the macro code in the workbook you refer to in the above code - you may need to make that workbook the active workbook - if you run into problems try adjusting the code in the workbook you are calling to make it the active workbook

Windows(ThisWorkbook.Name).Activate

This could get you into a heap of trouble if you try calling a group of workbooks and running the embedded macros - it depends on what the macros you are calling are doing - perhaps placing a timer (Application.OnTime method) between calling each macro would be a good idea - it really depends on what the macros are doing

Is this correct?
All of the files i need to run macros for are in
c:\documents and settings\rstevens1\Desktop\2012 D R Horton Bidding\Option Pricing\

the blank workbook i just created and dumped the code into is book1.xls

I get a runtime error so i must have something wrong.

Private Sub Workbook_Open()
Application.Run "'C:\Documents and Settings\rstevens1\Desktop\2012 D R Horton Bidding\Option Pricing\book1.xls'!Macro1Datsmart2" 'You need to change this to the directory and filename plus the macro name
End Sub

Thanks

You could do another thing - Excel has a concept called Personal workbook. You could store all your macros in that book and have a small procedure/macro that will call the rest. Like -

 sub macros_run()
call xxx
call yyy
call zzz 
end sub

But you have to ensure that each of the macros that you need to run will call their respective workbooks to do the activities.

Try

Application.Run ("'C:\Documents and Settings\rstevens1\Desktop\2012 D R Horton Bidding\Option Pricing\book1.xls'!Macro1Datsmart2") 'book1.xls must be the name of the file containing the macro Macro1Datsmart2

otherwise you may need to open the workbook

Workbooks.Open Filename:="C:\Documents and Settings\rstevens1\Desktop\2012 D R Horton Bidding\Option Pricing\book1.xls" 'The file containing the macro you want to run via book1.xls you created
Application.Run "book1.xls!Macro1Datsmart2"

the blank workbook i just created and dumped the code into is book1.xls

The Application.Run needs to refer to the workbook with the macro NOT the workbook calling the macro. You said you placed the code into book1.xls. The book1.xls part of book1.xls'!Macro1Datsmart2 needs to be the name of the file containing the macro Macro1Datsmart2

Are you using Excel 2007 or 2003 (your extension xls suggests 2003)?

As an example in book1.xls the code should be to e.g. run the macro ABC in the file getResults.xlsx

Application.Run ("'C:\Documents and Settings\rstevens1\Desktop\2012 D R Horton Bidding\Option Pricing\getResults.xlsx'!ABC")

I hope this makes sense. Make sure the file you create to run the macros in the workbooks in the directory is named differently from any of the workbooks in the directory.