get a handle of the already open instance of Excel and run a macro inside, from Outlook

Question

This is not a duplicate of: Can I set a Excel Application Object to point to an already open instance of Excel?

The idea is to execute a VBA sub contained in an Excel instance that is already open from Outlook

I am running the VBA sub as part of a rule in Outlook.

This is my code:

On Error Resume Next
        Dim tPath As String
        tPath = "X:\Lucas\LucasSheet.xlsm"
        Dim exApp As New Excel.Application
        Dim wb As Excel.Workbook
        wb = System.Runtime.InteropServices.Marshal.BindToMoniker(tPath)

Unfortunately at this point, when running in debug mode I can see that wb is equal to Nothing

        Set exApp = wb.Parent
        usedSub = "PrintSingle"
        exApp.Run usedSub            
        wb.Close False

Is it possible to make this code work in Outlook 2010?

score 0 · Answer 1 · answered Jun 17 '15 at 19:23

0

Instead of creating a new Excel Application in the code:

 Dim exApp As New Excel.Application

You need to get the running Excel instance :

exApp =  System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");

See Access running instances of Excel in VB for more information and sample code in VB.NET.

Use the Run method of the Application class to run the VBA macro programmatically.

answered Jun 17 '15 at 19:23

Eugene Astafiev

47,483
3
24
45

1

what if I have 5 instances of excel running? – LucasSeveryn Jun 18 '15 at 09:21
I think the issue is that I can't get System.Runtime.InteropServices.Marshal to work. – LucasSeveryn Jun 18 '15 at 12:02
Why do you need to have 5 Excel instance running? – Eugene Astafiev Jun 18 '15 at 12:59
I often have more, at the point of writing this reply I have seven, it's a necessity in my role. – LucasSeveryn Jun 18 '15 at 13:02
[Here](https://stackoverflow.com/a/35343847/9700298) is code which gets _all_ instances. – Traveler Sep 13 '22 at 05:03

score 0 · Answer 2 · answered Aug 30 '22 at 17:32

First you need a function that will attach to a running instance of Excel and then look for a workbook by name.

'@Description "Return the open Excel workbook"
Public Function GetHandleForExistingWorkbook(ByVal fullPath As String) As Object
    On Error GoTo openExcel
    
    Dim excelApp As Object
    Set excelApp = GetObject(, "Excel.Application")
    
    Dim wb As Object
    For Each wb In excelApp.Workbooks
        If wb.FullName Like fullPath & "*" Then
            Set GetHandleForExistingWorkbook = wb
            Exit For
        End If
    Next wb

    Exit Function
    
openExcel:
    If Err.Number = 429 Then
        ' Open it if it wasn't already open
        Set excelApp = CreateObject("Excel.Application")
        GetHandleForExistingWorkbook = excelApp.Workbooks.Open(fullPath)
    Else
        Debug.Print "Unhandled exception: " & Err.Number & " " & Err.Description
    End If

End Function

Once you know you are in the correct place you can run the macro by calling on the Excel Application object:

Public Sub RunMacroInOpenWorkbook(ByVal fullPath As String, ByVal macroName As String, _
Optional ByVal macroParameters As String = "")

    Dim theWorkBook As Object
    Set theWorkBook = GetHandleForExistingWorkbook(fullPath)
    theWorkBook.Application.Run "'" & theWorkBook.Name & "'!" & macroName, macroParameters
    theWorkBook.Close False
End Sub

Then your code to use this would look like this:


    Dim tPath As String
    tPath = "X:\Lucas\LucasSheet.xlsm"
    usedSub = "PrintSingle"
    RunMacroInOpenWorkbook tPath, usedSub

Note that `GetObject(, "Excel.Application")` will return some instance of Excel and this might not be what you want if there are multiple instances running. [Here](https://stackoverflow.com/a/35343847/9700298) is code which gets _all_ instances. — Traveler, Sep 13 '22 at 04:59

ChipsLetten · Answer 3 · 2015-06-18T09:33:12.083

-1

You need to include the workbook name when using the Application.Run command.

Try using this:

    exApp.Run wb.Name & "!" & usedSub ' must include workbook name

edited Jun 18 '15 at 09:33

answered Jun 17 '15 at 20:51

ChipsLetten

2,923
1
11
28

you are opening a sheet instead of referring to one that is already open, that's not the point of my question. – LucasSeveryn Jun 18 '15 at 09:22
this is irrelevant to the question as `wb` is equal to`Nothing`. Your `wb.Name` will fail in my question. – LucasSeveryn Jun 18 '15 at 09:34
Have you tried using an `Auto_Open` macro in the workbook? Also, your question needs the [.Net] tag and more explaining. "How can i loop through the multiple open Excel sessions, looking for a workbook and setting a reference to that workbook?" – ChipsLetten Jun 18 '15 at 10:43
I want to run a macro on a sheet that's constantly open from Outlook, what's the reasoning behind adding anything to auto open? – LucasSeveryn Jun 18 '15 at 10:55

get a handle of the already open instance of Excel and run a macro inside, from Outlook

3 Answers3