Using Excel VBA to perform repetitive web browsing tasks...

[sleuth]

I would like to write a macro to go to an internal company website to download data and import it into my spreadsheet. The website requires me to login and do a series of mouse clicks to download the data as excel spreadsheets, for which I already have developed a macro to merge these spreadsheets and format it the way I want.

I'm new to VBA but I've done a little research. My browser options on my company PC are Chrome, Firefox, IE, or Edge. I cannot download or install any unapproved 3rd-party applications (such as Selenium for Chrome) to aid me in this, and obtaining approvals for anything are unlikely.

Based on what I've found you would need Selenium to use Chrome, and that currently Edge does not support any VBA functionality.

Looking for some advice, pointers, or online resources to help.

 

[John_w]

There are several approaches using VBA.

1. Automate the website using IE and MS HTML Object Library (HTMLDocument etc. classes) and Windows API functions to handle the IE file download dialogue. See the following threads for example code:

http://www.mrexcel.com/forum/excel-...ile-download-dialog-box-without-sendkeys.html - has links to other examples.
https://stackoverflow.com/questions/11638933/excel-vba-sendkeys-not-causing-ie-9-to-save-download (unfortunately the detailed pages by one of the users, Siddharth Rout, don't seem to be available any more).

2. Using UIAutomationClient. Easier than the above method.

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

3. Using XMLhttpRequest to send GET and/or POST requests to the website to emulate the requests that a web browser sends. This is the fastest and most flexible method, but also the most complicated.

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
http://www.mrexcel.com/forum/excel-...ns-automation-websites-login.html#post4324160

Being an intranet site it will be difficult to help you further.