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