Need help using VBA to login and then download files from a website

Lighthouse6

New Member
Joined
Jun 27, 2013
Messages
11
Hi guys, I've been going around in circles for days trying to solve this problem.

I am trying to login to a site and download a file using an InternetExplorer object with VBA. The problem is that once the code clicks on the hyperlink I get a prompt from Internet Explorer (version 10 in my case): "Do you want to open or save "file.xls"? What do I do now?

Here is what I have tried:

1. URLDownloadToFile. This doesn't work because the file is not accessible without logging in.

2. Disabling the open/save prompt in Internet Explorer. Also no go. This prompt is designed as a security "feature" and is designed not to be disabled. Some people have mentioned that manually putting in registry keys could do the trick, but it has not worked for me. (Maybe I am not doing the right registry keys)?

3. Is there an download manager add in for Internet Explorer that disables the prompt? I have not found one yet.

4. Downgrading Internet Explorer from version 10 to version 7. (I'm using Windows 7, and I don't even know if it's compatible).

5. SendKeys ("%o") '(Alt + o). This should 'click' Open file. Why not use it? Because it's not reliable. The user sometimes switches the active window away. (Is there a way to "SendKeys" to a specific window?)

6. Repeating SendKeys if the file didn't open the first time.

7. Using WinHttpRequest instead of InternetExplorer. This should probably work, but it seems like it is a lot of work to learn how to use the WinHttpRequest and to rewrite all of my code. I am pretty new at vba altogether and it took me a few weeks to get this far. Besides, debugging WinHttpRequest would seem to be much more difficult because I cannot see what's happening (correct me if I'm wrong on that point). When using InternetExplorer I can watch what is happening.

8. iMacros. Theoretically, once the code enters the fields in the web site it can call iMacros to download the file. The problem with iMacros is that it takes time to load and close because it would have to load FireFox with the iMacro, then load the Web site and then close Firefox. For each one of hundreds of files!

Help! Thank you.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
I am trying to login to a site and download a file using an InternetExplorer object with VBA. The problem is that once the code clicks on the hyperlink I get a prompt from Internet Explorer (version 10 in my case): "Do you want to open or save "file.xls"? What do I do now?
Try this code - http://www.mrexcel.com/forum/excel-...-visual-basic-applications-3.html#post2805320

5. SendKeys ("%o") '(Alt + o). This should 'click' Open file. Why not use it? Because it's not reliable. The user sometimes switches the active window away. (Is there a way to "SendKeys" to a specific window?)
Try this independent SendKeys program.

7. Using WinHttpRequest instead of InternetExplorer. This should probably work, but it seems like it is a lot of work to learn how to use the WinHttpRequest and to rewrite all of my code.
Or the similar MSXML2.XMLHTTP are the best methods because they can download the file 'silently' without any visible interaction. But they might not work with your specific web site.
 

Lighthouse6

New Member
Joined
Jun 27, 2013
Messages
11
Thanks John. Looks like some good stuff you wrote. It will take me some time to go through the code to learn it and see what I can adapt for my purposes. I noticed that you had originally wrote it for IE8. Have you ever tried it with IE10 (which has more security blocks)?
Try this independent SendKeys program.
Sounds like a potentially very useful program. Problem is that both Web Of Trust and K9 both don't like the site, warning about malware and such.
Or the similar MSXML2.XMLHTTP are the best methods because they can download the file 'silently' without any visible interaction. But they might not work with your specific web site.
Is there a steep learning curve for MSXML2.XMLHTTP? Is IE easier to debug?
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
Have you ever tried it with IE10 (which has more security blocks)?
No I haven't.

Sounds like a potentially very useful program. Problem is that both Web Of Trust and K9 both don't like the site, warning about malware and such.
There's nothing wrong with that site or the SendKeys.zip download which contains SendKeys.exe - download it and run an online virus scan if you want. I've used SendKeys.exe without any problems.

Is there a steep learning curve for MSXML2.XMLHTTP? Is IE easier to debug?
Not really to both questions - just follow the many examples on the web. XMLhttp is similar to WinHttpRequest but with less flexibility, i.e. there are certain options not available to XMLhttp.

The main difficulty is parsing HTML for which you need to know about HTML tags and elements and their associated class names in the Microsoft HTML Object Library. Developing programs is easier if you use early binding in the VBA project - Microsoft XML v6.0 (for XMLhttp), MS Internet Controls (for InternetExplorer object), and MS HTML Object Library (for HTML elements).
 

Lighthouse6

New Member
Joined
Jun 27, 2013
Messages
11

ADVERTISEMENT

Thanks John. I am trying your suggestions. I hope to keep you posted.
 

Lighthouse6

New Member
Joined
Jun 27, 2013
Messages
11
Is there a way to fill forms (such as name and password) using WinHttpRequest? I have searched online for this but came up empty.
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708

ADVERTISEMENT

You need to send direct requests to the webserver - you don't "Fill in Form", you send POST (usually in the case of a form) to the server and parse the response -just like a web browser would. If you open the developer tab - chrome is probably easiest, go to network and "preserve data on navigation", fill in the form and submit it. You can see the actual request you need to send using xmlhttp or winhttp
 

Lighthouse6

New Member
Joined
Jun 27, 2013
Messages
11
Okay. It's time for an update. I am not there yet, but almost. Thank you John and Kyle for your help and encouragement.

Here is what I tried:
Sendkeys.exe is a Nifty Little Program
After verifying that Sendkeys.exe was not infected, by running it through an online virus scanner that runs dozens of different antivirus software scanners on it, and seeing that the link was suggested elsewhere as well, I tried Sendkeys.exe. First the good news: It's a great little program for what it does (thanks John); simple and elegant.

...But sending keystrokes to a program is just not very reliable.
The way Windows is designed it's not possible to reliably send keystrokes reliably to another program.

(In my case I struggled with sendkeys.exe; trying to send "%o" (Alt+o for "Open File") to Internet Explorer to open the download.xls file in excel. For some reason, although sending "%o" it made the dialog box go away in IE9 and in IE10 many times Excel did not open. Instead I finally switched to %s (alt+s for "Save File") and had VBA code to open the file as soon as it finished downloading. This worked for me but is not very elegant and takes a few seconds to execute (you need to pause at each step long enough to make sure that the dialog box is really open and registering the interaction.)

My learning process of theWinHttpRequest object:
Next, I tried using WinHttpRequest with the encouragement of John and Kyle. It was pretty intimidating at first but with the right information it became a lot easier. Thanks to this article; Using VBA to Access the 'Net by Tushar Mehta for pushing me in the right direction. 1. The right tools make the job much easier.
a. The object browser
in Excel was (and is) very useful. (To get to it, press F2 from within the Excel code editor). It tells us the names of the different pieces of the objects.Sometimes if you are lucky it even describes (briefly) what they do. I was stumbling around in Excel for weeks before discovering it.
b.
The Firefox add-on "Live HTTP Headers" was and is a huge help.It records the communication to and from the Website. You will need some of this information to use the WinHttpRequest object. This information is in the form of URLs, GET and/or POST requests from you to the Website, and the Http Headers information that the website sends back. I found this add-on pretty easy to use, and it taught me very quickly what I needed to know. It is easy to use: Turn on the log and navigate a website. Perrsonally, I found it easier than Kyle's suggestiont to use Chrome, but that may be a personal preference.

2. GET vs POST
These are two fundamentally different ways for you to send data (i.e. username, password, clicks etc.) to the web page. No you don't have a choice to pick whichever one you want. You have to speak in the language that the form understands. (Technically GET is a request to receive information. POST is a statement). WinHttpRequest demands that you know which one is needed. Fortunately "live HTTP Headers" tells you what you need to know.

3.
HTTP HeadersAfter the URL, there is information that is sent from you to the web page and information that the web page sends back in the form of HTTP Headers. Some of the more important headers are:

a. Cookies. Cookies are used by websites to "remember" that it's you. In password protected sites it is used as security measure. I needed to resend the cookie with each new navigation (a.k.a. GET or POST). You will need to extract the cookie, probably using .getAllResponseHeaders.

You use "oHttp.getAllResponseHeaders" to find out what the set-cookie fields are. You will need this information in password protected sites so that the site "knows it's you" and allows entry. In my case I need to reset the cookie header for each GET request.

In my case, I had to turn off redirects in order to read the set-cookie header after logging in. I did this using oHttp.Option(WinHttpRequestOption_EnableRedirects) = False. It took many hours until I stumbled upon this solution. What was happening was that the object automatically moved on to the redirected URL before allowing me to get the cookie from the HTTP headers of the first URL GET request. I don't know if it's a common problem in other websites.

b. User-Agent. User-Agent means the browser name and version. Some sites act differently depending on the browser and this can be a important field.

c. Referer. The refering site. Some sites are picky and demand that the referer header be a certain value before allowing entry.

d. ________ For POST requests there is a fourth header that is necessary to know about. I didn't use POST requests yet. Perhaps someone can remind me what it is and I can fill it in.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,624
d. ________ For POST requests there is a fourth header that is necessary to know about. I didn't use POST requests yet. Perhaps someone can remind me what it is and I can fill it in.
Do you mean Content-Type and Content-Length? The latter might not be needed because I think WinHttp/XMLhttp determines the length from the form data sent.
Code:
    With XMLhttp
        .Open "POST", URL, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .setRequestHeader "Content-Length", Len(formData)
        .send (formData)        'brackets are compulsory only for late binding of XMLhttp
Where formData is a string containing all the web page form field names and field values (input elements, hidden fields, select elements, etc.) concatenated with '&'. Each field name and value must be URI encoded - see the Escape function on the Tushar Mehta page VBA web services.

Typically, you send a GET request to get the initial web page. Put the response in a HTMLDocument, retrieve the form fields using getElementsByTagName or getElementById etc., and construct the formData string. Then send the form data in a POST request and save the response bytes (the actual file being downloaded) in a local file.

If you tell us the URL we should be able to give you more specific help. If it's possible, I'm willing to sign up to the web site to help you.
 

Kyle123

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
It's also worth remembering that by default WinHTTPRequest will handle most of the cookies for you, so there's no need to parse them out - xmlHTTP doesn't
 

Watch MrExcel Video

Forum statistics

Threads
1,123,381
Messages
5,601,302
Members
414,440
Latest member
Kim0204

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top