VBA to Open Another Instant of Excel and Run Macro

[Ceeyee]

Hi,

Is it possible in VBA to open another Excel file in another Excel instant (process) and more importantly, call a macro built in it?

The workbook.open method opens the file in the same Excel instant, which is not what we want.

Thanks.

 

[GTO]

Sub - Question Reference passed args in .Run

Hi Ceeyee,

Yes, you can do that. Try:

In a Standard Module in the Calling wb:

Option Explicit
 
Sub example()
Dim app                 As Application
Dim wbTarget            As Workbook
Dim strReturnedValue    As String
Dim strArg              As String
 
'// Supply/Change fullname to suit                                              //
Const FULL_PATH As String = "G:\2011\2011-08-03\New Folder(2)\TempMac.xls"
 
    '// Set a reference to the new instance.                                    //
    Set app = New Application
 
    With app
        '// Until I am confident no glitches can occur, I would keep the new    //
        '// instance visible, but reduced for focus.                            //
        .Visible = True
        .WindowState = xlMinimized
 
        '// Set a reference to the opening wb in the other instance             //
        Set wbTarget = app.Workbooks.Open(FULL_PATH)
 
        '// Build any req'd argument(s).  The single quotes' placement always   //
        '// gives me fits.                                                      //
        strArg = "'" & wbTarget.Name & "'!Module1.TestCalled"
 
        '// Run the code and return whatever...                                 //
        strReturnedValue = app.Run(strArg, ThisWorkbook.Name)
 
        MsgBox strReturnedValue
 
        '// Close the otehr wb and kill the instance.                           //
        wbTarget.Close False
        .Quit
    End With
End Sub

In a Standard Module in the Called wb:

Function TestCalled(ByVal CallingWBName As String) As String
    TestCalled = CallingWBName & " called the macro in " & ThisWorkbook.Name & _
    ", supplied" & CallingWBName & "'s name as a string."
End Function

Please note that I used the simple return of the function. I passed ByVal really just for clarity, but I am not aware if there is a way to pass back ByRef? (Anyone care to comment about that aspect?)

Anyways, w/no args, it is easy. With args seems a tad bit of a PITA, but can be done.

Does that help?

Mark

 

[Ceeyee]

Re: Sub - Question Reference passed args in .Run

Great.
We don't need args so it will be just right for us.

Let me try the code.

Thanks.

 

[Ceeyee]

Re: Sub - Question Reference passed args in .Run

If I call two external files/macro in one subroutine using this method,

will the second file/macro wait for the first one to finish before opening/running, or they will be running in parallel (which is what we want)?

Thanks.

 

[shg]

The code will in the called workbook will run to completion and then return control to the code in the calling workbook.

 

[Ceeyee]

Is there any way to let the external workbook macros run in parallel (so that we can take advantage of the multicore cpu, which is our purpose of the exercise)?

 

[Jaafar Tribak]

Is there any way to let the external workbook macros run in parallel (so that we can take advantage of the multicore cpu, which is our purpose of the exercise)?

If you use the Shell function to open the target workbook then the macros should run asynchronously.

The details depend on what exactly the opened workbook is supposed to do and whether it will communicate data with the calling workbook or not.

 

[Ceeyee]

We just need to call subs, and no communication with the calling workbook.

Could you please give me some examples on how to use the Shell function to do the calling of multiple workbooks running at the same time?

Say we have workbook0 calling
macro1 in workbook1
macro2 in workbook2
and let them run at the same time.

Thank you very much.

 

[Jaafar Tribak]

The following example illustrates how you can open an external workbook in a seperate excel instance and run a Macro in it asynchronously.

In this example, both the calling and called Macros will keep updating the value of Range("A1") in each respective excel instance.


1- Code in the calling workbook


- In a standard module in your calling workbook (Run the RunRemoteMacro routine to execute the external Macro and the StopRemoteMacro to stop it) :

1-

Option Explicit

Private Type GUID
    lData1 As Long
    iData2 As Integer
    iData3 As Integer
    aBData4(0 To 7) As Byte
End Type
 
Private Declare Function FindWindowEx Lib "user32" _
Alias "FindWindowExA" _
(ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
 
Private Declare Sub AccessibleObjectFromWindow Lib "OLEACC.DLL" _
(ByVal hwnd As Long, _
ByVal dwId As Long, _
riid As GUID, _
ppvObject As Any)
 
Private Const OBJID_NATIVEOM = &HFFFFFFF0

 

Sub RunRemoteMacro()

    Dim lPid As Long, i As Long
    Dim sRemoteWbName As String, sExeName As String, sParams As String
    
    sExeName = "excel.exe"
    sRemoteWbName = "c:\abcd.xlsm"
    sParams = " /e" & " -!-" & " "
    
    lPid = Shell(sExeName & sParams & sRemoteWbName, vbMaximizedFocus)
    Do
        DoEvents
    Loop Until lPid <> 0
    Do
        Range("a1") = i
        i = i + 1
        DoEvents
    Loop

End Sub

Sub StopRemoteMacro()

    Dim sArg As String
    Dim oXLApp As Application
    
    Set oXLApp = ApplicationFromHwnd()
    If Not oXLApp Is Nothing Then
        sArg = "'" & oXLApp.Workbooks(1).Name & "'!Module1.CancelMacro"
        Call oXLApp.Run(sArg)
        'oXLApp.Workbooks(1).Close False
        'oXLApp.Quit
        Set oXLApp = Nothing
    End If

End Sub

Private Sub SetIDispatch(ByRef ID As GUID)
 
    ' IDispatch Interface.
    ' {00020400-0000-0000-C000-000000000046}.
    With ID
        .lData1 = &H20400
        .iData2 = &H0
        .iData3 = &H0
        .aBData4(0) = &HC0
        .aBData4(1) = &H0
        .aBData4(2) = &H0
        .aBData4(3) = &H0
        .aBData4(4) = &H0
        .aBData4(5) = &H0
        .aBData4(6) = &H0
        .aBData4(7) = &H46
    End With
 
End Sub
 
Private Function ApplicationFromHwnd() As Application
 
    Dim IDispatch As GUID
    Dim oWB As Object
    Dim lXLhwnd As Long
    Dim lXLDESKhwnd As Long
    Dim lWBhwnd As Long
 
    Do
        lXLhwnd = FindWindowEx(0, lXLhwnd, "XLMAIN", vbNullString)
        If lXLhwnd = 0 Then
            Exit Do
        ElseIf lXLhwnd <> Application.hwnd Then
            lXLDESKhwnd = FindWindowEx(lXLhwnd, 0&, "XLDESK", vbNullString)
            lWBhwnd = FindWindowEx(lXLDESKhwnd, 0&, "EXCEL7", vbNullString)
            If lWBhwnd Then
                SetIDispatch IDispatch
                Call AccessibleObjectFromWindow _
                (lWBhwnd, OBJID_NATIVEOM, IDispatch, oWB)
                Set ApplicationFromHwnd = oWB.Application
                Exit Do
            End If
        End If
    Loop
 
    Set oWB = Nothing
 
End Function

To stop the remote macro run the StopRemoteMacro routine.



2- Code in the external Target workbook :

A- In the ThisWorkbook module :

Option Explicit

Private Declare Function GetCommandLine Lib "kernel32" _
Alias "GetCommandLineA" () As Long

Private Declare Function lstrlen Lib "kernel32" _
Alias "lstrlenA" _
(ByVal lpString As Long) As Long

Private Declare Sub CopyMemory Lib "kernel32" _
Alias "RtlMoveMemory" _
(pDst As Any, pSrc As Any, _
ByVal ByteLen As Long)


Private Sub Workbook_Open()

    If InStr(1, GetCommLine, "-!-") Then
        Call RemoteMacro
    End If

End Sub


Private Function GetCommLine() As String

    Dim RetStr As Long, SLen As Long
    Dim Buffer As String
    
    RetStr = GetCommandLine
    SLen = lstrlen(RetStr)
    If SLen > 0 Then
        GetCommLine = Space$(SLen)
        CopyMemory ByVal GetCommLine, ByVal RetStr, SLen
    End If
    
End Function

B- In a Standard module : (Module1)

Option Explicit

Private bCancel As Boolean

Sub CancelMacro()

    bCancel = True

End Sub

Sub RemoteMacro()

    Dim i As Long
    
    bCancel = False
    Do
        Range("a1") = i
        i = i + 1
        DoEvents
    Loop Until bCancel
    MsgBox "Remote Macro Canceled."
    
End Sub

Note that when opening the external workbook via the Shell command, any addins/XLStartUp files will open. This may cause problems. Passing the /s switch in excel 2007 disabled all code when tested.

 

[Jaafar Tribak]

EDIT:

The FullName I gave to the external workbook in the above code example was "c:\abcd.xlsm" - Change that as required.