How To Make VBA Code Wait For A Worbook Opening From The Internet?

You need to show your code of how you are downloading the data.

Most of these "downloads" depending on how you do it can be done synchronously or asynchronously

You options are

1. synchronously which should do its job and then go on (but might not - maybe doevents helps here)

2. asynchronously which would mean you set the download to go and that is all.  This will then fire some event when it is done and then you set up your workbook.

I would definately consider it to be done asynchrously as the Internet Explorer "File Download" dialog is just getting its "Open" button clicked.

I more specifically used the method explained in this thread: https://www.experts-exchange.com/questions/24679312/Download-file-dialog-automation.html.

After clicking the "Open" button, the Excel VBA code immediately continues processing, and doing so the file being downloaded cannot open once it is finished downloading (some of the files are large and it may thus take a second or two to download completely). Even using a loop with DoEvents will stop the file from opening in Excel. The same goes for Application. Wait. The only solution  I have been able to make work is to use Application.OnTime in a way where the VBA Code actually stops processing totally giving room for the file to open. then after detecting that the file is amongst the workbooks of the application the recursive function containing the OnTime Call launches the next step of the process.

The latter method works, but as I said I would rather use another way than OnTime as it has given problems for me before when users are doing a lot of other stuff on the PC where the VBA code is running. The optimal solution would be some way to do it synchronously, so that the VBA code courteously waits for the file to download and open, but as I don't know the url, this is difficult to do. But I'm thinking that maybe the InternetExplorer Object (or some other Internet Controls object) that I'm using may hold the means of doing so?

Here's the code of the function used to download the file. I have not included const's and declares as it is irrelevant and would take up a lot of space. As shown it simply does the "click" and then continues processing after this.

Sub OpenWebFileDownload(Optional ByVal strDialogCaption As String = "File Download", Optional ByVal strDialogButtonText As String = "Open", _
    Optional ByVal TimeOutNoSeconds As Long = 60)
    
    Dim hwnd As Long
    Dim HndB As Long
    Dim tRECT As RECT
    Dim y As Long, x As Long
    Dim dTimeOut As Date
    Dim lSecondsElapsed As Long
    
    dTimeOut = VBA.Now + TimeSerial(0,0,TimeOutNoSeconds)
    hwnd = 0
    Do Until hwnd <> 0 Or VBA.Now > dTimeOut
        hwnd = FindWindow("#32770", strDialogCaption)
        DoEvents
    Loop
    HndB = 0
    Do Until HndB <> 0 Or VBA.Now > dTimeOut
        HndB = FindWindowEx(hwnd, 0, "Button", "&" & strDialogButtonText)
        DoEvents
    Loop
    GetWindowRect HndB, tRECT
    With tRECT
        x = .Left + (.Right - .Left) / 2
        y = .Top + (.Bottom - .Top) / 2
    End With
    Do While hwnd <> 0 Or VBA.Now > dTimeOut
        SetForegroundWindow hwnd
        SetCursorPos x, y
        SendMessage HndB, BM_CLICK, 0, 0
        DoEvents
        hwnd = FindWindow("#32770", strDialogCaption)
    Loop
End Sub

Select allOpen in new window

Perfect, I actually thought VBA was limited so that events of non built in objects such as workbooks, worksheets etc. could not be used, but I'm glad to realize that this is not the case.