excel - 如何让excel等待工作簿打开

Question

我的应用程序当前导航到供应商网站，登录，打开报告，单击 IFrame 后面的下载按钮(这是癌症开始工作)，然后点击打开/保存对话框上的打开按钮。现在项目的这个阶段剩下的就是让 excel 等待新工作簿打开，将信息拉到新工作表中，然后关闭另一个工作簿。
问题是，无论我尝试什么，新工作簿都不会在宏运行时打开。如果我在代码中添加一个中断，一切都很好。我尝试过 application.wait、sleep、DoEvents 甚至是 msgbox。我在这里有点难过。

'This clicks the button that initiates the open/save dialog box in IE
iWin.contentDocument.querySelector("a[title=Excel][onclick*=EXCELOPENXML]").Click

'set the name of the windows the program should look for
iStr = "Reports LeaveXpert Application - Internet Explorer"
eStr = "Case Detail.xlsx  [Protected View] - Excel"
'these are declared as longptr
leHwin = 0
cdHwin = 0

'Grabs the windows handle for the IE window
leHwin = FindWindow(vbNullString, iStr)
'Makes sure the IE window is active
i = SetForegroundWindow(leHwin)
'a little bit of delay
Application.Wait DateAdd("s", 5, Now)
'Send the key combination for the open button to the open browser
Application.SendKeys "%{O}"


cdHwin = 0
Debug.Print cdHwin

'this should wait for the new spreadsheet to open but instead the application hangs here and the new spreadsheet never opens.
Do While cdHwin = 0

    'various methods of having the program wait that i've tried
    
    'DoEvents
    'Application.Wait (Now() + TimeValue("00:00:01"))
    'Sleep 1000
    
    'this should set the variable to the windows handle of the new sheet once it opens causing the loop to stop
    cdHwin = FindWindow(vbNullString, eStr)
    
    'This just keeps giving me 0 as an output
    Debug.Print cdHwin
Loop

'we never get to this line of code unless I add a break somewhere in the loop, then the sheet opens and I get the windows handle as output.
Debug.Print cdHwin

更新:
终于成功了!!!!!!!!!!!!!!!
我将不得不重新考虑我的单按钮实现方法，但这是一个很小的代价。在 2 周的大部分时间里，这是一个断断续续的项目，花费的时间比我预期的要多得多，主要是因为 IFrame 是绝对的癌症(我非常讨厌它们)。但我已经学到了很多让这第一部分工作。现在开始将其他两个相关数据源集成到电子表格中。我对计算机编程了解得越多；我越是意识到我对此知之甚少。
哦，Mathieu-Guindon 是绝对的英雄!
为后代更新 2。
这没东西看。我以为我已经解决了这个问题，但事实证明我只需要更多的 sleep 。评论讨论中的相关引用。
“我真的需要停止熬夜到凌晨 4 点阅读有关计算机代码的帖子并尝试处理这件事。我真的认为我睡着了，做了一个关于修复它的梦想，醒来并发布了关于“修复”的帖子，并认为这是真的。”
更新 3:关于这个问题的最终更新。相关解释在以下代码的注释中。

'I made 2 changes that seem to have resolved the issue.
Private Sub AppEvents_WorkbookOpen(ByVal Wb As Workbook)
If Left(Wb.Name, 11) = CaseDetailFileName Then
    'This sub that handles the transfer of data from the newly opened workbook to this workbook.
    'It had an internal call to close the newly opened workbook that I forgot about, so I removed it
    ImportCase Wb
    'a duplicate call also existed out here, there were also a few duplicate ".activate"
    'commands inside the import sub and this if statement that I removed
    Wb.Close
    'This sub handles data sorting and analytics
    CaseFAS
End If
'I had tried copying this line from Workbook_Open a few times before and it 
'didn't resolve the issue by itself,
'but I'm reasonably sure it is part of the solution
Set AppEvents = Me.Application
End Sub

为了完整起见，在 ImportCase 的引擎盖下一瞥。

'This sub handles importing the optis workbook when it opens.  It's called by 
'the AppEvents_WorkbookOpen sub declared in ThisWorkbook
Public Sub ImportCase(ByVal book As Workbook)
Dim srcSht As Worksheet
Set srcSht = ThisWorkbook.Sheets("OptisSource")
'This line clears out any old data
srcSht.Cells.Clear
'Sets the newly opened workbook to be the active sheet
book.Sheets("Case Detail").Activate
'Copys the information and sends it to a predefined sheet in this workbook
ActiveSheet.UsedRange.Copy Destination:=srcSht.Range("A1")
'sets the newly imported information to the active sheet
srcSht.Activate
'This sub clears out ghost data
ClearNulls
End Sub

Answer 1

如果我们只是让 Excel 做它的事情，并遵循事件驱动/去同步范式的流程呢？Excel.Application类引发了许多您可以在任何类模块中处理的事件，但 ThisWorkbook模块可以做 - 你声明一个 WithEvents模块级变量:

Option Explicit
Private WithEvents AppEvents As Excel.Application

然后你用 Application 初始化它对象引用，可能在打开状态 - 如果我们在 ThisWorkbook模块，我们继承了 Application来自基地的属性(property) Excel.Workbook类(class):

Option Explicit
Private WithEvents AppEvents As Excel.Application

Private Sub Workbook_Open()
    Set AppEvents = Me.Application
End Sub

声明 WithEvents变量使它在左上角的下拉列表中可用，然后我们可以选择一个成员来使用右上角的下拉列表来实现:

我们可以在 Application 处理的事件之一级别，是比较方便的 WorkbookOpen给我们一个 Wb As Workbook 的事件对刚刚打开的工作簿的引用:

Private Sub AppEvents_WorkbookOpen(ByVal Wb As Workbook)

End Sub

在这里，您可以拦截在启用宏的 session 期间打开的每个工作簿，只要这个 AppEvents引用仍然在范围内——也就是说，直到 ThisWorkbook已经关了。
所以 ThisWorkbook可能看起来像这样:

Option Explicit
Private Const CaseDetailFileName As String = "Case Detail.xlsx"
Private WithEvents AppEvents As Excel.Application

Private Sub Workbook_Open()
    Set AppEvents = Me.Application
End Sub

Private Sub AppEvents_WorkbookOpen(ByVal Wb As Workbook)
    If Wb.Name = CaseDetailFileName Then
        DoThingWithCaseDetailWorkbook Wb
    End If
End Sub

然后 Public Sub DoThingWithCaseDetailWorkbook(ByVal book As Workbook)可以在任何标准模块中定义，并且可以访问在 WorkbookOpen 之前设置的任何模块状态/变量。事件触发。
现在文件以保护模式打开(从不受信任的位置？)这一事实可能会干扰这一点，但是如果出现问题，应该有一种方法可以让脚本将文件保存在其他地方。