Get data / reports from web-based program through VBA?

[xancalagonx]

Every week I am putting together a rather extensive report, which requires data from several different projects all put into one large report.

The raw data I need is gathered by logging in to a web based software and then running each individual report from that program through a report tab within the program.

E.g. for a single project I need 4 different reports which are dumped out as seperate Excel files. (I wish the program was smart enough to allow me to get them all in one go, but it doesn't and I can't change the program in question).

Currently I have to run the report for 3 different projects x 4 reports is 12 different Excel files that have to be combined in a presentable way (and more might be required down the road).

As soon as I have the files in Excel formats I know how to do the rest in VBA to automate the whole combining the reports thing.

But what I am wondering... is there a way, through VBA, to also automate the login to the website and then getting each of the individual reports dumped out into Excel ? I know automating login and "clicking" around to navigate through a website is possible in some other programming languages (but I haven't learned those, yet).

If, somehow, I can get the currently manual work of exporting reports to become automated as well I could most likely automate the entire process of exporting reports + combining them + dumping them out in PDF and having them emailed to appropriate parties, rather than spending hours clicking around a clumsy website to export the data manually.

Put short, I could spend 3-4 hours each Monday doing something useful rather than the assembly line robotic excel report exporting I currently have to endure

Thanks for reading!

 

[Comfy]

Rather than try to stitch something together in VBA have you considered contacting the Database/System admin?

There maybe some back-end functionality that can assist you.

The Database administrator could write a report specifically for generating all the information you require (that way you would not have to stitch it together)
The Systems admin may be able to schedule the report generation so that they generate automatically on a given date/time.

I have no idea if they are applicable in you situation, but worth checking out.

 

[xancalagonx]

Thanks for reply Comfy, but that won't be possible, otherwise I would definitely have attempted that route. Getting any changes or additional reports beyond the standard ones within the system is a slow and fruitless process, as well as expensive.

I was hoping there would be a way to automate navigation through the webpages to run the reports in a similar way that we do when we are doing it manually by clicking around.

Anyways.. maybe I am stuck with doing this manually....

 

[Comfy]

There probably is a way to do it.

But I don't imagine anyone will be able to code this for you without having access to the web application and it's HTML.

This might help you to get started:

IE (Internet Explorer) Automation using Excel VBA - Excel VBA Templates
Welcome to LearnExcelMacro.com How to interact with HTML pages from Excel
Using VBA to Link to Internet Explorer

You will also need to review the web apps HTML on each page to get the ids of each element you want to interact with.