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

I'm currently doing an automation in which I'm extracting data from an internet based membership data provider usi ng Excel VBA.

At this point I have finished a setup that can automatically rquest and open data workbooks from the site. However the problem is as soon as the file has been set to open and download, the VBA processing goes on and for this reason the file download and opening seems to be either cancelled or postponed. If there is no processing done after the request to open the downloaded file, there is no problem, but since I have to automate in way that requires multiple subsequent files to be opened and read this is not an option.

I have tried the following that didn't work:
Application.Wait (Halts Excel so that the internet workbook cannot be opened)
Application.Ontime (To Clumsy, I would rather avoid this)
DoEvents (Does not work for this purpose)

Furthermore I ´like the idea of opening the workbooks instead if saving them and deleting them after theyre read. Saving workbooks through IE when the urls are unknown is also pretty cumbersome, so I would rather keep the "open" method if possible.

In essence I think i need some kind of procedure that can make Excel wait, but still not halt it so that it can open the excel file opened through Internet Explorer automation.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
andreas_rafnAuthor Commented:
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:

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?
andreas_rafnAuthor Commented:
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)
    HndB = 0
    Do Until HndB <> 0 Or VBA.Now > dTimeOut
        HndB = FindWindowEx(hwnd, 0, "Button", "&" & strDialogButtonText)
    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
        hwnd = FindWindow("#32770", strDialogCaption)
End Sub

Open in new window

That was not what I was expecting but ok lets work with this.

I imagine to "click" on the open button your are able to get the Internet Explorer as an object or your have the Webbrowser control buiöt into your app.

Which one is it?  I am going to assume you have IE as an object.

What you are going to have to do is change the way you use your IE object.  You need to use "Public with events" instead of DIM to declare your IE object and you need to do this at the top of your form.

Once you do this in VB editor you will now get in the drop down list of events all the events of the IE.  You should see in there download begin and download end events.

Now you can work with these.  You will have to set your download in motion and check that download begins, if it does then wait for the download to end event and then open that workbook.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
andreas_rafnAuthor Commented:
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.