How to login to a website and download a file using Excel VBA

Question

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 VBA and an InternetExplorer object. 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. 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?)

5. Using WinHttpRequest. 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. (Maybe lazy people have more work) Besides, debugging WinHttpRequest would seem to be much more difficult as the actions are not visible while in IE I can watch what is happening.

6. iMacros. Theoretically, once I enter the fields in the web site I can use iMacros to download the file. The problem with iMacros is that it takes time to load and close. It would have to load FireFox with the iMacro, then load the Web site and then close Firefox. My VBA program is designed to download hundreds of files.

Help! Thank you.

Answer 1

The solution is to use cURL rather than trying to enlist the "help" of Explorer. You can find details of how to do this at Equivalent cURL in VBA? . But instead of sending the response to a MsgBox you would open a file and write to it. Of course this is really using WinHTTPRequest and you already said in your question "that seems like a crazy amount of work" - so I'm not sure you will be happy with the solution.

Note - there is a tricky use of the Options property - you can find details of how to use it at http://msdn.microsoft.com/en-us/library/windows/desktop/aa384108(v=vs.85).aspx . In the example given, the option SslErrorIgnoreFlags is set to 13056 - that is 0x3300 which means "ignore any and all errors" since it's the sum of

Unknown certification authority (CA) or untrusted root      0x0100
Wrong usage                                                 0x0200
Invalid common name (CN)                                    0x1000
Invalid date or certificate expired                         0x2000

I hope this is enough - given the detail of your question, I have little doubt!

Another suggestion (not sure if this is practical) - use a platform other than VBA. There are many advanced languages with toolboxes just for this purpose. My personal favorite is cURL in PHP, but there are some really good options in Python as well. But whether that makes any sense depends really on what you are trying to connect this into.