How to have excel wait for a workbook to open

Question

My application currently navigates to a vendor website, logs in, opens a report, clicks a download button behind an IFrame(which was cancer to get working), and hits the open button on the open/save dialog box. Now all thats left for this phase of the project is to have excel wait for the new workbook to open, pull the information into a new sheet, and close the other workbook.

The problem is that no matter what I seem to try the new workbook will never open while the macro is running. If i add a break in the code everything works just fine. I've tried application.wait, sleep, DoEvents, and even a msgbox. I'm kinda stumped here.

'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

UPDATE:

IT FINALLY WORKS!!!!!!!!!!!!!!!

I'll have to rethink my single button approach to implementation, but that's a pretty small price to pay. This has been an off and on project for the better part of 2 weeks and has taken me far more time than I anticipated, mostly due to IFrames being absolute cancer (I seriously hate them). But I've learned a lot getting this first part working. Now to start on getting the other two relevant data sources integrated into the spreadsheet. The more I learn about computer programming; the more I realize how little I know about it.

Oh and Mathieu-Guindon is an absolute hero!

UPDATE 2 for posterity.

Nothing to see here. I thought I had resolved the problem, but as it turns out I just need more sleep. Relevant quote from comment discussion.

"I really need to stop staying up till 4 AM reading posts about computer code and trying to work on this thing. I really think that I went to sleep, had a dream about fixing it, woke up and posted about the "fix", and thought it was real."

Update 3: Final Update on this question. Relevant explanation is in the comments on the following code.

'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

A peek under the hood of ImportCase for completeness sake.

'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

What if we just let Excel do its thing, and go with the flow of an event-driven / desynchronized paradigm?

The Excel.Application class raises a number of events you can handle in any class module, but the ThisWorkbook module can do - you declare a WithEvents module-level variable:

Option Explicit
Private WithEvents AppEvents As Excel.Application

And then you initialize it with an Application object reference, perhaps on open - if we're in the ThisWorkbook module, we're inheriting an Application property from the base Excel.Workbook class:

Option Explicit
Private WithEvents AppEvents As Excel.Application

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

Declaring a WithEvents variable makes it available in the top-left dropdown, and then we can pick a member to implement with the top-right dropdown:

One of the events we can handle at the Application level, is a rather convenient WorkbookOpen event that gives us a Wb As Workbook reference to the workbook that was just opened:

Private Sub AppEvents_WorkbookOpen(ByVal Wb As Workbook)

End Sub

Here you get to intercept every workbook that opens during a macro-enabled session, as long as this AppEvents reference remains in-scope - that is, until ThisWorkbook is closed.

So the ThisWorkbook might look like this:

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

And then Public Sub DoThingWithCaseDetailWorkbook(ByVal book As Workbook) could be defined in any standard module and have access to any module state/variable that was set before the WorkbookOpen event fired.

Now the fact that the file is being opened in protected mode (from an untrusted location?) might interfere with this, but then there should be a way to get the script to save the file elsewhere if it's a problem.