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.

 

[cwild]

Using the workbook_open event is good but it doesn't allow flexibility to run whatever macro you want in that moment. But if in the 2nd Excel instance you have created counterpart macros that use OnTime to call a specific macro after a 1 second delay then it will be the 2nd Excel instance that calls it, thereby allowing your 1st instance to carry on independently.

 

[Jaafar Tribak]

Sorry cwild .. I forgot to post the vbs code

Try this in a standard module and run the Test routine :

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function GetTickCount Lib "kernel32" () As Long
#Else
    Declare Function GetTickCount Lib "kernel32" () As Long
#End If


Sub RunRemoteMacroAsync(ByVal FilePathName As String, ByVal MacroName As String)
    Dim sVBSCode As String
    Dim iFileHandle As Integer
    Dim sTempVbs As String
    
    If Len(Dir(FilePathName)) <> 0 Then
        sTempVbs = Environ("Temp") & "\" & GetTickCount & "Temp.vbs"
        sVBSCode = "On Error Resume Next" & vbCrLf _
        & "With CreateObject(""Excel.Application"")" & vbCrLf _
        & ".Visible = True" & vbCrLf _
        & ".Workbooks.Open (""" & FilePathName & """)" & vbCrLf _
        & ".Application.Run """ & MacroName & """" & vbCrLf _
        & "End With" & vbCrLf _
        & "If Err<>0 Then" & vbCrLf _
        & " Msgbox ""Error Number:  "" & Err.Number & vbcrlf & ""Error Description : "" & Err.Description" & vbCrLf _
        & "End If" & vbCrLf _
        & "With CreateObject(""Scripting.FileSystemObject"")" & vbCrLf _
        & ".DeleteFile (Wscript.ScriptFullName)" & vbCrLf _
        & "End With"
        iFileHandle = FreeFile
        Open sTempVbs For Append As #iFileHandle
            Print #iFileHandle, sVBSCode
        Close #iFileHandle
        Call Shell("WScript.exe " & sTempVbs)
    End If
End Sub


Sub test()
    RunRemoteMacroAsync "C:\Test\MyFile1.xlsm", "MyMacro1"
    RunRemoteMacroAsync "C:\Test\MyFile2.xlsm", "MyMacro1"
    
[B][COLOR=#008000]    'rest of your caller application code goes here....[/COLOR][/B]
End Sub