WinBatch® Technical Support Forum

Archived Boards => COM Automation and dotNet => Topic started by: nickedw on November 22, 2013, 10:25:13 AM

Title: run macro in first Excel workbook, after opening a second workbook
Post by: nickedw on November 22, 2013, 10:25:13 AM
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")



Title: Re: run macro in first Excel workbook, after opening a second workbook
Post by: Deana on November 22, 2013, 10:33:56 AM
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 )
Title: Re: run macro in first Excel workbook, after opening a second workbook
Post by: nickedw on November 22, 2013, 02:11:14 PM
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?
Title: Re: run macro in first Excel workbook, after opening a second workbook
Post by: Deana on November 22, 2013, 03:23:19 PM
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

Title: Re: run macro in first Excel workbook, after opening a second workbook
Post by: George Vagenas on November 22, 2013, 08:34:35 PM
You could also try setting a reference to the first workbook in your second workbook.
Tools/References...
Title: Re: run macro in first Excel workbook, after opening a second workbook
Post by: nickedw on November 23, 2013, 11:44:52 PM
 :) That works! Your support rocks, Thank you