Downlaod an excel file from the a webpage using VBA from excel

Hi Experts,

Every day I have to go to a website and download an excel file, the steps being:

1. Open internet explorer.
2.  Navigate to a portal page.
3. Enter username and password.
4. Follow a couple of links to get to my desired page.
5.  Alter a few dropdown menus to adjust settings for the downloaded file.
6.  Download file.

I have no idea if this is feasible to be automated (ideally I would want to do it from VBA in excel) - dfoes anyone have any ideas?

Who is Participating?
NorieConnect With a Mentor VBA ExpertCommented:

Points 1-3 might be pretty straightforward, 4-5 a little more complicated but should still be possible.

6 is probably the main stumbling block in the whole thing, you could sail through 1-5 but then you've got to do the actuall download.

There are various methods to deal with that, one of the simplest is to use a Windows API.

For that you would need to know the URL and filename for the download.

One way of doing 1-5 is to automate Internet Explorer, where the basic logic for 1-5 would be something like this.

1 Create instance of Internet Explorer (IE).

2 Navigate to required URL, wait for the page to load.

3 Once the page has loaded create a reference to it's document:

      Set doc = IE.document

4 Grab references to the username and password fields on the page.

That can be done using various methods, here's one.
      Set objUName = doc.GetElementByID("userid")
      Set objPWord= doc.GetElementByID("pwordid")

This will only work if you know the relevant IDs for the fields, if you don't there are other messages.

5 Submit username/password, again various methods - this one assumes the username and password are contained in a form.


6 Wait for authentication and wait for next page to load.

7 Grab a reference to the document (see 3 above).

8 Get references to the dropdowns, select the correct options, submit them if needed.

Now you should be at the final stage, the download.

As I said it could be handled in a few different ways - for that more information would definitely needed.

eg How is the download initiated? Do you get a download dialog prompting to save/open etc?
Yes it's easy Matt.

I can write the code for point 1-3

But after that you will have to take over as I will not have Username and Password. If you can create a Dummy username and password and explain the rest of the stuff then probably I can give you the complete code.

that depends on the authentication mechanism of your web-site.  (html form data, or somethign else?) the main issue is the log-in proccess and weather it can be done by code.
look at the address bar when navigating through the site, does it contain the data being selected (called querystring data) - if so then it's very easy to do from code, if not then you need to know how the data is actually being sent
do you have access to the site's pages' source?
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

matt_mAuthor Commented:
Thanks Sid, its a work realted website so I can't really do the username/password thing - do you think you could walk me through it (this wasy I learn more anyway!)?  
>>>could walk me through it (this wasy I learn more anyway!)?  

Sure :)

Ok, I will explain the entire procedure but you will have to be very patient. It will be some sort of tutorial :)

Would it be ok?

matt_mAuthor Commented:
sounds good Sid!

I should say I'm about to leave work though (I'm in Aus) so I won't be able to respond to anything for about 15 hours or so.
Quick question.

Do you have Firefox? If not, can you install it and then install the Add-On Firebug. You can get it from here.

We will take it from there.

matt_mAuthor Commented:
Thansk Imnorie, exactly what I was after.

NorieVBA ExpertCommented:
What even the typos?

PS Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.