run macro in first Excel workbook, after opening a second workbook

[nickedw]

I have two Excel workbooks open. After opening the 2nd workbook, I script the command to run the macro, but Excel thinks the macro is in the last workbook i opened. How do i tell Excel to look for the macro in another workbook?

The following is an excerpt from my wbt:
; Connect to Excel
oXL = ObjectCreate("Excel.Application")

;  open Excel Workbook 1
   oXL.Workbooks.Open(xlsFilename)

;  open Excel Workbook 2
   oXL.Workbooks.Open(xlsFilenameReport)

;  Now I want to execute a macro in the Workbook 1 , but the following only works if i don't open the second workbook.
   oXL.Application.Run( "FixKamARData")

[Deana]

In the Run method try specifying the workbook name and an exclamation mark before the macro name. For example

Code (winbatch) Select Expand

macro = xlsFilenameReport:"!FixKamARData"
oXL.Application.Run( macro )

[nickedw]

Thanks! I tried that but get a COM Exception. Error says "The macro may not be available in this workbook  or all macros are disabled".
Maybe if i activated the first workbook and then tried executing the macro? How do i switch focus back to the first workbook?

[Deana]

Untested....

Code (winbatch) Select Expand

; Connect to Excel
oXL = ObjectCreate("Excel.Application")

;  open Excel Workbook 1
wrkbk1 = oXL.Workbooks.Open(xlsFilename)


;  open Excel Workbook 2
wrkbk2 = oXL.Workbooks.Open(xlsFilenameReport)
wrkbk2.Activate ;Reference http://msdn.microsoft.com/en-us/library/office/bb210376(v=office.12).aspx



[George Vagenas]

You could also try setting a reference to the first workbook in your second workbook.
Tools/References...

[nickedw]

 :) That works! Your support rocks, Thank you