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")
In the Run method try specifying the workbook name and an exclamation mark before the macro name. For example
macro = xlsFilenameReport:"!FixKamARData"
oXL.Application.Run( macro )
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?
Untested....
; 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
You could also try setting a reference to the first workbook in your second workbook.
Tools/References...
:) That works! Your support rocks, Thank you