Execute a macro in a different workbook
Runs a macro or calls a function. This can be used to run a macro written in Visual Basic or the Microsoft Excel macro language, or to run a function in a DLL or XLL.
Sub Run_Macro_In_Different_WorkBook()
Application.Run "'C:\CanBeDeleted.xlsx.xlsm'!AnotherWrkBook_Macro"
End Sub
Runs a macro or calls a function. This can be used to run a macro written in Visual Basic or the Microsoft Excel macro language, or to run a function in a DLL or XLL.
Sub Run_Macro_In_Different_WorkBook_With_Arguments()
On Error GoTo Err_Trap
Application.Run "'C:\CanBeDeleted.xlsx.xlsm'!Function_Two_Args", "Argument 1", "Argument 2"
Err_Trap:
If Err <> 0 Then
Debug.Print Err.Number & Err.Description
End If
End Sub
The above code passes arguments to a macro in another workbook
The following errors need to be handled for forward compatibility of your macro
450 - Wrong number of arguments or invalid property assignment
449 - Argument not optional
Sunday, March 02, 2008
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.
Posts
Thanks. Nice entry.
ReplyDeleteMy situation is a bit different. The macro I want to run in in a specific worksheet.
Any idea how would I identify the worksheet in Application.Run "'C:\CanBeDeleted.xlsx.xlsm'!AnotherWrkBook_Macro"
Thanks
IF you want to execute a macro in a specific module you can try
ReplyDeleteApplication.Run "'C:\CanBeDeleted.xlsx.xlsm'!Sheet1.AnotherWrkBook_Macro"
I ended up doing the following: Hopefully, this will help someone else:
ReplyDeletePrivate Sub CommandButton1_Click()
Dim controlws As Worksheet
Set controlws = Application.ActiveSheet
col = 6
Row = 11
For i = 1 To controlws.Cells(Row, col).Value
Row = Row + 1
bookname = controlws.Cells(Row, col).Value
Application.StatusBar = "Opening workbook " & bookname
Set wb = Workbooks.Open(Filename:=bookname)
Application.StatusBar = "Getting data for workbook " & bookname
' For this to work -> and un-privatize this macro in the spreadsheet
Call wb.Sheets.Item("FOUNDRY_SETUP").CommandButton1_Click
Next i
Application.StatusBar = "Done!"
End Sub
I am working in a workbook called Invoice, Location of the Master Workbook is specified in a cell of Invoice Workbook. I need a macro to search a word "YES" in column C:C in the Master Workbook.
ReplyDeleteHere is a hint:
ReplyDeleteSub Find_A_Cell()
Dim oWS As Worksheet
Dim oRng As Range
Dim FirstUL
Set oWS = ActiveSheet
Application.FindFormat.Clear
Application.FindFormat.Font.Color = vbBlue
Set oRng = oWS.Range("C:C").Find(What:="YES", LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext)
If Not oRng Is Nothing Then
FirstUL = oRng.Address
Do
oRng.Font.Color = vbRed
Set oRng = oWS.Range("C:C").FindNext(oRng)
Loop While Not oRng Is Nothing And oRng.Address <> FirstUL
End If
End Sub
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
ReplyDeleteDim 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
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
ReplyDeleteDim 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
Note that if you use variables as filename and it contains special characters like spaces, use
ReplyDeleteApplication.Run "'" & sYourFilename &"'!AnotherWrkBook_Macro"
This took me a while to grasp..
The macro that I am using uses the name of the workbook, as a result whenever i have to rename the excel workbook i have to edit the code accordingly.
ReplyDeleteFor instance:
My excel file name is test i uses the code
Workbooks("test.xls").Worksheets("data").Range("a1:g65536").ClearContents
and if i have to rename the excel file as test2 i have to change the code as
Workbooks("test2.xls").Worksheets("data").Range("a1:g65536").ClearContents
Can you please help .I am new when it comes to using macros.I am using excel 2007
Thanks in advance
Hi,
DeleteUse a variable to get the current file name and use that variable in the syntax above instead giving a file name in the quotes. For e.g
a=Application.ActiveWorkbook.Name
Workbooks(a).Worksheets("data").Range("a1:g65536").ClearContents