Opening Workbooks and Running a macro from them

[xluser2007]

Hi All,

I have a macro that at the moment just opens a workbook SpreadsheetA.xls,

Sub OpenA

Workbooks.Open Filename:=C:\Documents\SpreadsheetA.xls, UpdateLinks:=0

End Sub
SpreadsheetA.xlshas a macro called "ExpectedRun" which is currently operatedby clicking a commandbutton.

What is the code to run the "ExpectedRun" Macro in Sub OpenA?

Eventually I'll be opening abunch of these workbooks, each with its own unique "ExpectedRun" macro, so is there any special command to call on the "ExpectedRun" macro for specifically the opened workbook?

Any help would be much apprecaited.

[Bob Phillips]

If you call you macro from the workbook_open event procedure within the target workbbook, it will run automatically upon opening.

[xluser2007]

xld, I don't quite understand.

Just to clarify, I want to Open Macro first, then make some slight amendments and then run a macro from the opened (i.e. target workbook). I'm sorry i didn't make this clearer in my original post.

So something like:

Sub OpenA

Workbooks.Open Filename:=C:\Documents\SpreadsheetA.xls, UpdateLinks:=0

' Insert more changes to SpreadsheetA.xls here before running '"ExpectedRun" from SpreadsheetA.xls

[Need code here to run "ExpectedRun" from SpreadsheetA.xls]

End Sub
Could you please explain how your method would fit into the above?

Is there a way to "Call" the macro, I tried but this didn't work.

[Bob Phillips]

The you need Application.Run. Check it out in VBA help.

[xluser2007]

xld, thanks for the suggestion.

Tried to follow it, keep running into 1004 error "Method 'Run' of object '_Application' failed when i put in the following code (the bold line gives the error):

Sub OpenA()

Dim targetbook as workbook

Set targetbook = Workbooks.Open(Filename:=C:\Documents\SpreadsheetA.xls, UpdateLinks:=0)

' Insert more changes to SpreadsheetA.xls here before running '"ExpectedRun" from SpreadsheetA.xls

Application.Run ("'targetbook'!ExpectedRun")

End Sub
The help file in VBA wasn't as useful for my example I found (the example they give is a one liner: mySum = Application.Run("MYCUSTOM.XLM!My_Func_Sum", 1, 5)).

I also followed this and wrote Application.Run("targetbook!ExpectedRun").


Could you kindly let me know what I'm doing wriong and how to correct it.

(this will eventually open up a whole bunch of spreadsheets and loop through and run "ExpectedRun" for all of these when the above example is corrected for)