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.