Set Focus on Excel Workbook

[Jakson]

I'm building an Outlook macro that sends email contents to an Excel spreadsheet. It's working perfectly up to the point where Outlook passes control to Excel.

By virtue of focus I cannot get my Excel macro to seamlessly begin executing after the Outlook macro execution finishes with the summon command, "Run [Macro name]"

I know I can set focus on other applications, I'm just unsure how to go about that. I've poured over documentation from Microsoft and I'm having a terrible time digesting the information and making use of it.

TL;DR: I need to set focus on an Excel workbook from an Outlook macro.

Guidance is deeply appreciated.

[Dave]

Maybe it's just a syntax thing. I assume that you have opened the XL wb and that the macro that U want to run exists... I think that the macro also has to be located in a module and be publicly declared as well. Providing these conditions are met the syntax should be...

Application.Run "YourWbName.xlsm!YourSubName"

HTH. Dave

[Jakson]

Maybe it's just a syntax thing. I assume that you have opened the XL wb and that the macro that U want to run exists... I think that the macro also has to be located in a module and be publicly declared as well. Providing these conditions are met the syntax should be...

Application.Run "YourWbName.xlsm!YourSubName"

HTH. Dave

Yes, the target workbook is opened at the beginning of the Sub in Outlook.

'declare mail itemDim Item As MailItem
'declare excel
Dim xlApp As Excel.Application
'declare workbook
Dim xlWB As Excel.Workbook
'declare worksheet
Dim xlSheet As Excel.Worksheet


'Determine path of the destination
Const strPath As String = "C:\TEST.xlsm" ' workbook path and name
'If nothing selected
If Application.ActiveExplorer.Selection.Count = 0 Then
    'MsgBox "No Items selected! Dingus, pick an email!!!", vbCritical, "Error"
    Exit Sub
End If


On Error Resume Next
'Set the declaration
Set xlApp = GetObject(, "Excel.Application")


If Err <> 0 Then


    Application.StatusBar = "Please wait while Excel source is opened ... "
    'Create the Excel object itself.
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    'Initiate in the computing environment
    bXStarted = True
End If


On Error GoTo 0
'Open the workbook to input the data
Set xlWB = xlApp.Workbooks.Open(strPath)
    On Error GoTo ErrClose
'Elect a sheet
Set xlSheet = xlWB.Sheets("Renewals List")

I'm passing vars and macro execution perfectly fine up to the point of activating a Macro with a message box in it. Simply using "Activate ___.xlsm" is not good enough for my needs. I need to set the Windows Environment focus on the XL wb. I'll take a look and see if the XL wb receiving macro is declared as public or not... The receiving macro is declared as Public.

I'm receiving the passed vars just fine. I just need it to activate the workbook like a User clicked on it.

Apologies if I'm confusing LOL. I'm still new to this vernacular.

If you need some code to review lemme know.

[Dave]

Recall that I mentioned that the code to be ran must be in a module and that the syntax (as provided) has to be correct. This code should activate the application and bring it to the foreground. Dave

AppActivate xlApp.ActiveWindow.Caption & " - " & xlApp.Caption
xlApp.WindowState = 1 'wdWindowStateMaximize

[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

[Jakson]

Recall that I mentioned that the code to be ran must be in a module and that the syntax (as provided) has to be correct. This code should activate the application and bring it to the foreground. Dave

AppActivate xlApp.ActiveWindow.Caption & " - " & xlApp.Caption
xlApp.WindowState = 1 'wdWindowStateMaximize

This did the trick.

The exact code I implemented is such:

'Pulling apart the subject line/body information and into vars...
'With selected outlook email item
For Each Item In Application.ActiveExplorer.Selection
    
        'Particularly with the subject...
        Subj = Item.Subject
        'MsgBox "Subj " & Subj
        On Error GoTo SubjErr1
...

'Activate the workbook like a user clicked it
        AppActivate xlApp.ActiveWindow.Caption

'Excel window is pulled to front as though a user had clicked it for focus

...

'Runs a macro on that same book
xlWB.Application.Run "UpdateNOBRAINER", NameSubj, EDTSubj, BodT(0), RNum

Thank you very much for your help!

Now it switches back and forth like I desired. When a new email comes in, it's scooped up by the OnNewMail event listener I set up and processed. Then it activates my Excel book and opens it up like a user clicked it. It does some fancy macro magic and then arrives with the cursor over a specific data line. Then it goes back to "sleep" and waits for more.

Thank you again!

[Dave]

You are welcome. Thanks for posting your outcome. Dave