Set Focus on Excel Workbook

[gmayor]

Provided the macro is in a module in the workbook in question then the following will open Excel (if not already open) and access the macro in the named workbook, which of course must be macro enabled. Or you could simply reproduce the Excel code in the Outlook macro.

Sub OpenExcel()
Dim xlApp As Object
Dim xlwb As Object
Dim bStarted As Boolean
Dim sMyBook As String
    sMyBook = "C:\Path\WorkBookName.xlsm"

    On Error Resume Next
    Set xlApp = GetObject(, "Excel.Application")
    If Err Then
        Set xlApp = CreateObject("Excel.Application")
        bStarted = True
    End If
    
    getWorkbook xlApp, sMyBook
    
    xlApp.Visible = True
    xlApp.Activate
    ' do whatever else you want here -e.g. xlApp.Run YourExcelMacro etc.
    xlApp.Run ("testmacro")
    'xlWB.Close wdDoNotSaveChanges
    ' and when you're finished
    If bStarted = True Then
        xlApp.Quit
    End If
    Set xlwb = Nothing
    Set xlApp = Nothing
lbl_Exit:
    Exit Sub
End Sub

Private Function getWorkbook(xlApp As Object, sWorkbook As String) As Object
    Dim xlwb As Object
    For Each xlwb In xlApp.Workbooks
        If xlwb.FullName = sWorkbook Then Exit For
    Next

    If xlwb Is Nothing Then
        If Len(Dir(sWorkbook)) > 0 Then
            Set xlwb = xlApp.Workbooks.Open(sWorkbook)
        End If
    End If
    Set getWorkbook = xlwb
End Function