Call to subroutine from seperate workbook

Coby · January 25th, 2008, 04:47 PM

If I had one workbook open, but the vba modules were in another workbook which is closed, does anyone know if there is a way for the codeless workbook to run the subroutine which is in the other workbook?

Any help is sincerely appreciated.

Coby.

Shasur · January 26th, 2008, 03:20 AM

You can use the application.Run and do the needful. However, you need to open the workbook containing the macros to execute it

Here is the code:

Code:

Sub Run_Antoher_Macro()

Dim AnotherWrkBook As String
Dim MacroInAnotherWbk As String
Dim oWB As Workbook


AnotherWrkBook = "MyMacro.XLS"
MacroInAnotherWbk = "Copy_Data"
Set oWB = Workbooks.Open("c:\" & AnotherWrkBook)


Application.Run "'" & AnotherWrkBook & "'!" & MacroInAnotherWbk

oWB.Close False
End Sub

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)

Coby · February 1st, 2008, 10:15 PM

Good! Thanks a lot . . . I think I was close but did not know all of the Syntax such as the ' and the ! and where they needed to be.

That did it.

Have a nice weekend and thanks again.
Coby.