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

Started by nickedw, November 22, 2013, 10:25:13 AM

Previous topic - Next topic

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
macro = xlsFilenameReport:"!FixKamARData"
oXL.Application.Run( macro )
Deana F.
Technical Support
Wilson WindowWare Inc.

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
; 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

Deana F.
Technical Support
Wilson WindowWare Inc.

George Vagenas

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

George

nickedw