How to access a macro of an other excel file

[Alex St-Pierre]

Hello,
does anyone know why this is not working...
The Macro1 in file C:\test.xls is existing. When I put the
line Macro1(currentworkbook) in commentary, it open the
file. When not, it makes an error 450 (the number of
argument is not correct or property invalid)

Sub test()
Dim CurrentWorkbook As Workbook
Dim file As String
Dim path As String
path = "C:\"
file = "test.xls"
Set CurrentWorkbook = Application.Workbooks.Open(path
& file)
Macro1 (CurrentWorkbook)
End Sub

 

[Tushar Mehta]

To access a macro in another workbook, you need to use the
Application.Run method. [Or, you need to create a reference to the
other workbook.]

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Alex St-Pierre]

Thank you, but what I need to know what is wrong in my
macro. I'm trying to create a reference but it doesn't
works.

Alex

-----Original Message-----
To access a macro in another workbook, you need to use the
Application.Run method. [Or, you need to create a reference to the
other workbook.]

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

Hello,
does anyone know why this is not working...
The Macro1 in file C:\test.xls is existing. When I put the
line Macro1(currentworkbook) in commentary, it open the
file. When not, it makes an error 450 (the number of
argument is not correct or property invalid)

Sub test()
Dim CurrentWorkbook As Workbook
Dim file As String
Dim path As String
path = "C:\"
file = "test.xls"
Set CurrentWorkbook = Application.Workbooks.Open (path
& file)
Macro1 (CurrentWorkbook)
End Sub

.

 

[Perry]

This is a macro from any open workbook

Sub Testing123()
Dim wb As Excel.Workbook
Set wb = GetObject("d:\MyDocs\Excel\ForeignMacro.xls")
wb.Prompt_A_String "This is my line"
wb.Close 0: Set wb = Nothing
End Sub

This is the routine in (the other) ForeignMacro.xls workbook
coded in ThisWorkBook classmodule:

Sub Prompt_A_String(ByVal AnyString As String)
MsgBox AnyString
End Sub

Krgrds,
Perry

 

[Alex St-Pierre]

Thx, it works now!!

-----Original Message-----
This is a macro from any open workbook

Sub Testing123()
Dim wb As Excel.Workbook
Set wb = GetObject("d:\MyDocs\Excel\ForeignMacro.xls")
wb.Prompt_A_String "This is my line"
wb.Close 0: Set wb = Nothing
End Sub

This is the routine in (the other) ForeignMacro.xls workbook
coded in ThisWorkBook classmodule:

Sub Prompt_A_String(ByVal AnyString As String)
MsgBox AnyString
End Sub

Krgrds,
Perry




.