Workbook_Open之后焦点出现错误的Excel窗口 [英] Wrong Excel Window in Focus after Workbook_Open

问题描述

我最近升级到Office 365/Excel 2016导致了一些不需要的行为更改. Workbook("Portfolio Appreciation")包含一个Workbook_open过程，该过程检查以查看Workbook("Index Return")是否处于打开状态；如果不是，它将打开该工作簿.

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.

在Excel 2007中，Index Returns将在后台打开并停留在该位置，这是所需的行为.这将是在一个窗口中"，并且可以使用View功能区的Window选项卡上的Arrange All选项在同一Excel窗口中查看.

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.

在Excel 2016中，如果通过Workbook_Open过程将其打开，则Index Returns将在其自己的Excel窗口中打开，并在前面弹出. (不能再在与Portfolio Appreciation相同的Excel窗口中查看它.)

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).

Index Returns在前面的事实是问题所在.

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

我尝试选择和取消选择该选项以忽略使用DDE的其他应用程序；我尝试了AppActivate方法(如下代码所示)，并使用MsgBox验证了参数是否与相关的标题栏匹配.

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.

也:Index Returns不包含宏或连接. Portfolio Appreciation除Workbook_Open外不包含任何宏，并且确实有一个打开时会刷新的网络查询(该查询下载了一些股指资料).

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

---

推荐答案

当Comintern的代码未更改行为时，我着眼于这是否是计时问题，IndexReturns直到激活代码后才有活动窗口另一个工作簿.对此进行调整的代码似乎已经解决了问题.

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.

在执行AppActivate方法之前，我添加了一个循环来测试IndexReturns窗口的存在.

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

这似乎已经解决了Excel 2016与2007相比打开文件所带来的问题.

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

这篇关于Workbook_Open之后焦点出现错误的Excel窗口的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！