Author Topic: run macro in first Excel workbook, after opening a second workbook  (Read 4877 times)

nickedw

  • Newbie
  • *
  • Posts: 15
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

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: run macro in first Excel workbook, after opening a second workbook
« Reply #1 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
macro = xlsFilenameReport:"!FixKamARData"
oXL.Application.Run( macro )
Deana F.
Technical Support
Wilson WindowWare Inc.

nickedw

  • Newbie
  • *
  • Posts: 15
Re: run macro in first Excel workbook, after opening a second workbook
« Reply #2 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?

Deana

  • Wilson WindowWare Tech Support
  • Pundit
  • *****
  • Posts: 1183
  • WinBatch® can do it.
    • WinBatch Tech Support Database
Re: run macro in first Excel workbook, after opening a second workbook
« Reply #3 on: November 22, 2013, 03:23:19 pm »
Untested....

Code: Winbatch
; 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

  • Full Member
  • ***
  • Posts: 102
Re: run macro in first Excel workbook, after opening a second workbook
« Reply #4 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...
Thanks

George

nickedw

  • Newbie
  • *
  • Posts: 15
Re: run macro in first Excel workbook, after opening a second workbook
« Reply #5 on: November 23, 2013, 11:44:52 pm »
 :) That works! Your support rocks, Thank you