calling macro from another instance of excel

[Guest]

Is it possible to create a macro in one instance of excel to call/elecute
another macro in another instance of excel??

Say the first instance macro is first_instance.xls!macro1 and the second
instance macro is second_instance.xls!macro2. Both files are on the desktop
of one machine.

The two macros are outside each other's scope, but is it possible to call
one from the other anyway???

What would the calling code line say?

 

[merjet]

Assuming the other workbook is open, you can use
Application.Run, e.g.:

Application.Run "FileName.xls!MacroName"

If it's not open, you can make a reference to it
(menu Tools | References) and call the macro that
way.

Either way, it can be risky. It depends on what the
macro does.

Hth,
Merjet

 

[Chip Pearson]

Paul,

I don't think this is possible, at least in a reliable way. To do so, you'd
have to use GetObject to get a reference to the other instance of Excel, but
there is no way to tell GetObject which instance of Excel to reference. As
likely as not, you'll get a reference to the instance that contains the
GetObject statement, not the other instance.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Peter T]

Providing you know the path of the file, as well of course as it's name, try
the following -

Sub test()
Dim sFolder As String
Dim sFile As String
Dim sMacro As String
Dim wb As Workbook
Dim xlApp As Excel.Application

sFile = "myBook.xls"
sFolder = "C:\My Documents\Excel\"

On Error Resume Next
Set xlApp = GetObject(sFolder & sFile).Parent
On Error GoTo errH

If xlApp Is Nothing Then
MsgBox sFile & " not found"
Else

sMacro = "'" & sFile & "'!" & "myMacro"
xlApp.Run sMacro

'' if you want to do stuff to the file
' Set wb = xlApp.Workbooks(sFile)
' wb.Worksheets(1).Range("A1") = Now
End If

Exit Sub
errH:
MsgBox Err.Description

End Sub

Normally this should find the file if loaded in any instance, even own
instance.
Bear in mind any dialogs or msgbox's that myMacro shows may be hidden behind
other windows, though some API calls could bring the instance to the front.

If you don't know the the file's path it's considerably more difficult to
reference other unknown instances.

Regards,
Peter T

 

[Chip Pearson]

That's not a bad approach, but I found it more reliable to use

Dim XLB As Excel.Workbook
Set XLB = GetObject("C:\Book1.xls")
XLB.Parent.Run "Book1.xls!AAA"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)