Run macro in another workbook

[Guest]

Hi,

I have two WBs open and are updating/merging data from one to the other. I
want to run a macro in one WB to create data on a specific WS. The results
shall be copied from the WB into the other.
To generate the data there is a pushbutton on one WS (connected to a macro).
The resulting data is gathered on another WS, that is to be copied to the
other WB.

How can I execute a macro in another WB (in VB)?

/konpego

 

[Tom Ogilvy]

If you don't have reference from the calling workbook to the workbook with
the macro, you would use application.Run

application.Run "OtherWorkbookName.xls!Macro1"

 

[Guest]

I have tried this but it seems to be some problem.....

Are there any restrictions on the WB name?
My name contains blanks, minus-sign, parenthesis.
Is this allowed?

Best Regards

konpego

 

[Chip Pearson]

If the name contains blanks, you need to enclose the file name
within single quotes. E.g.,

Application.Run "'Other Workbook Name.xls'!MacroName"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Tom Ogilvy]

Sub RunOrganize()
Application.Run "'My - Test - (b) & ook.xls'!OrganizeData"
End Sub

Enclose the name in single quotes.

The above worked for me.

 

[Tom Ogilvy]

Just to add:

Single quotes is the answer for you as Chip stated, but blanks alone don't
require them. This ran fine for me

Sub RunOrganize()
Application.Run "My - Test - book.xls!OrganizeData"
End Sub

when I changed the name to My - Text - (b) & ook.xls, then I needed single
quotes.

 

[talha]

I am trying to run a macro on one Excel workBook, The macro is located in another excel workbook. When iam trying to run the macro using syntax ("C:\Test_Macro.xls!Hello_World") then i got COM exception. for clarification here is my code of what iam doing. Thanks for your help in advance
Dim oExcel As Excel.ApplicationClass
Dim oBook As Excel.WorkbookClass
Dim oBooks As Excel.Workbooks

'Start Excel and open the workbook.
oExcel = New Excel.Application
oExcel.Visible = False
oBooks = oExcel.Workbooks
' workbook in which i want to run marco
oBook = oBooks.Open("c:\Hello_Macro.xls")
'Run the macros.
oExcel.Run("C:\Test_macro.xls!TEST2")
oBook.Save()
oBook.Close(False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
oBook = Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
oBooks = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel = Nothing
regards
Talha