Invalid Excel window in focus after Workbook_Open

Question

My recent upgrade to Office 365 / Excel 2016 caused some unwanted behavioral changes. Workbook ("Portfolio Evaluation") contains the Workbook_open procedure, which checks if the Workbook ("Index Returns") is open; if it is not, he will open this book.

In Excel 2007, Index Returns will open in the background and stay there, which is the desired behavior. It will be “in the window” and can be viewed in the same Excel window using the Arrange All option on the Window tab of the View ribbon.

In Excel 2016, if it opens with the Workbook_Open procedure, Index Returns opens in its Excel window and ends in front. (It can no longer be viewed in the same Excel window as Portfolio Appreciation ).

The fact that Index Returns is ahead is a problem.

I tried selecting and deselecting to ignore other applications using DDE; I tried the AppActivate method (shown in the code below) and confirmed with MsgBox that the argument matches the corresponding header line.

Not sure where to go next. Suggestions appreciated.

Also: Index Returns does not contain macros or joins. Portfolio Appreciation does not contain macros other than Workbook_Open , and it has a web request that is updated when it is opened (the request downloads some stock index data).

---

 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

When the Comintern code did not change the behavior, I focused on whether it was a synchronization problem, while IndexReturns did not have an active window until another book was activated after the code. And the setup code for this seems to have solved the problem.

I added a loop to check for an IndexReturns window 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 this window invisible, since I do not need to access it except for debugging purposes:

 wb.Windows(1).Visible = False 

It seems that the problem was solved as a result of opening Excel 2016 files in different ways compared to 2007.

Answer 2

I obviously cannot test in your environment, but I would try to get around everything Excel does and use 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