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
Visual Basic for Applications (VBA) Forum (recent threads)
CodeKeep VBA Feed
Visual Studio Tools for Office Forum (recent threads)
Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.
Office Business Applications (OBA) Team Blog
MSDN Code Gallery Published Resources For Tag VSTO
microsoft.public.vsnet.vstools.office Google Group
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..