• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

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?

Cheers
Matt
0
matt_m
Asked:
matt_m
  • 3
  • 3
  • 2
  • +1
1 Solution
 
SiddharthRoutCommented:
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.

Sid
0
 
ErezMorCommented:
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?
0
 
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!)?  
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SiddharthRoutCommented:
>>>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?

Sid
0
 
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.
0
 
SiddharthRoutCommented:
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.

http://getfirebug.com/downloads

We will take it from there.

Sid
0
 
NorieCommented:
Matt

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.

        doc.Forms(0).Submit

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?
0
 
matt_mAuthor Commented:
Thansk Imnorie, exactly what I was after.

Cheers
Matt
0
 
NorieCommented:
What even the typos?

PS Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now