VBA Download Excel Repoort From an ASP Site

[hatman]

Once upon a time our Maintenance department used a software package that sat on top of an Oracle Database within our network to manage all planned and unplanned Maintenance activities. A decade ago I built various automated reports from the background tables and created tools to allow the production schedulers to plan around machine downtimes.

Fast forward to Cloud Computing. Data is now housed somewhere outside of our network. The only access I seem to have is to use IE to log into the Maintenance Vendors site and fill in the various fields on the search page to return a report of all open requests. The results get returned in the Explorer Window, with an option to export results to Excel. No Problem. A Simple IE Automation gets me to this point. I can even use the .Click method of the Excel Link Object to start the download process automatically. Where I get Stymied is that a sweep of all Windows using Windows API's does not expose the Open, Save, Cancel buttons in the standard IE Popup that asks what do you want to do with this file. I can't get the HWND of these buttons to press them with code. The .href property of the link is some ASP routine, so the URLDownlad API does not work.

Please help...

 

[Kyle123]

You cannot reliably use IE for this. You need to send the actual request that IE sends directly using something like WINHTTP, this will return the file as a stream that you can write to disk. You can use the developer tools in IE (though Chrome/Firefox would be better) to see the actual request you need to send.

 

[hatman]

WinHTTP Looks like a steep learning curve with a library download. Not sure that IT Security will allow me as a lowly peon to actually implement this. Found a hack using the UIAutomationCore.dl library that seems like it will fit the bill.

 

[Kyle123]

Not sure what you mean by a library download, it's already on your computer and it's probably less code than automating ie. Glad you got it sorted though

 

[hatman]

I'm always interested in having more than one tool in my box to get a job done. But I did not see a WinHTTP library (or any obvious variation) in the References dialog of the VBE. So I guess I'll ask the VBA 101 question of "Which reference do I need to add to include this library in my project?"

 

[John_w]

Here are two threads showing how to download a file using XMLhttp requests to emulate what a browser would send for the specific sites in question:
http://www.mrexcel.com/forum/excel-...n-internet-explorer-web-site.html#post3404965
http://www.mrexcel.com/forum/excel-questions/749860-msxmlhttp-post-paramaters.html

and download using UIAutomationClient:

http://www.mrexcel.com/forum/excel-...ual-basic-applications-website-accessing.html

 

[hatman]

Cool. Enough to get started with. Thank you sir for an alternative path. I'm sure this will come in handy, If not on this project, then on some future project.