Wrong Excel Window in Focus after Workbook_Open

Question

My recent upgrade to Office 365 / Excel 2016 has caused some unwanted behavioral changes. Workbook("Portfolio Appreciation") contains a Workbook_open procedure which checks to see if Workbook("Index Returns") is open; if it is not, it will open that workbook.

With Excel 2007, Index Returns would open in the background and stay there, which is the desired behavior. It would be "in a window" and could be viewed, in the same Excel window, using the Arrange All option on the Window tab of the View ribbon.

With Excel 2016, if it gets opened by the Workbook_Open procedure, Index Returns opens in its own Excel Window, and winds up in front. (It can no longer be viewed in the same Excel window as Portfolio Appreciation).

The fact that Index Returns is in front is the problem.

I have tried selecting and deselecting the option to ignore other applications using DDE; I have tried the AppActivate method (shown in the code below) and verified, using a MsgBox, that the argument matches the relevant title bar.

Not sure where to go next. Suggestions appreciated.

Also: Index Returns contains no macros or connections. Portfolio Appreciation contains no macros other than Workbook_Open and does have a web query that gets refreshed when it opens (the query downloads some stock index stuff).

---

Option Explicit
Private Sub Workbook_Open()
    Dim wbs As Workbooks, wb As Workbook
    Dim IndexReturns As String
    Dim re As RegExp
    Const sPat As String = "(^.*\\DATA\\).*"
    Const sRepl As String = "$1EHC\Investment Committee\indexreturns.xlsb"
    Dim sTitle As String

sTitle = Application.Caption

Set wbs = Application.Workbooks
Set re = New RegExp
With re
    .Pattern = sPat
    .Global = True
    .IgnoreCase = True
End With
IndexReturns = re.Replace(ThisWorkbook.FullName, sRepl)

For Each wb In wbs
    If wb.FullName = IndexReturns Then Exit Sub
Next wb

Application.ScreenUpdating = False

wbs.Open (IndexReturns)
Set re = Nothing

AppActivate sTitle  'sTitle contains title of thisworkbook

'The below doesn't work either
'AppActivate ThisWorkbook.Application.Caption

Application.ScreenUpdating = True

End Sub

---

Answer 1

I obviously can't test in your environment, but I'd try bypassing whatever Excel is doing and using a call to BringWindowToTop or SetForegroundWindow instead of AppActivate:

#If VBA7 Then
    Public Declare PtrSafe Function BringWindowToTop Lib "user32" (ByVal _
             hwnd As LongPtr) As Boolean
    Public Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal _
             hwnd As LongPtr) As Boolean
    Public Declare PtrSafe Function FindWindow Lib "user32" Alias _
            "FindWindowA" (ByVal lpClassName As Any, ByVal lpWindowName _
            As Any) As LongPtr
#Else
    Public Declare Function BringWindowToTop Lib "user32" (ByVal _
             hwnd As Long) As Boolean
    Public Declare Function SetForegroundWindow Lib "user32" (ByVal _
             hwnd As Long) As Boolean
    Public Declare Function FindWindow Lib "user32" Alias _
            "FindWindowA" (ByVal lpClassName As Any, ByVal lpWindowName _
            As Any) As Long
#End If

Then...

    Dim hwnd As Long
    hwnd = FindWindow(vbEmpty, sTitle)    'sTitle contains title of thisworkbook
    BringWindowToTop hwnd     
    '...or...
    SetForegroundWindow hwnd

Answer 2

When Comintern's code did not change the behavior, I focused on whether this was a timing issue, with IndexReturns not having an active window until after the code Activated the other workbook. And code to adjust for this seems to have solved the problem.

I added a loop to test for the presence of a Window of IndexReturns before executing the AppActivate method.

Set wb = wbs.Open(IndexReturns)

Do
    DoEvents
Loop Until wb.Windows.Count > 0

AppActivate sTitle

For good measure, I also made that window invisible, as I have no need to access it for other than debugging purposes:

wb.Windows(1).Visible = False

This seems to have solved the problem brought about by Excel 2016 opening files differently compared with 2007.