VBA WinHTTP to download file from password proteced https website

Question

I'm trying to save a file from https password protected site using WinHTTP. Here's the code:

Sub SaveFileFromURL()

Dim FileNum As Long
Dim FileData() As Byte
Dim WHTTP As Object

fileUrl = "https://www.website.com/dir1/dir2/file.xls"
filePath = "C:\myfile.xls"

myuser = "username"
mypass = "password"

Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")

WHTTP.Open "GET", fileUrl, False
WHTTP.SetCredentials myuser, mypass, HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
WHTTP.Send

FileData = WHTTP.ResponseBody
Set WHTTP = Nothing

FileNum = FreeFile
Open filePath For Binary Access Write As #FileNum
    Put #FileNum, 1, FileData
Close #FileNum

MsgBox "File has been saved!", vbInformation, "Success"

End Sub

The problem is with authentication. The file is being saved but when I open it in Excel it's just the html logon page instead of the actual file. If I copy direct file url and paste it into browser addressbar and I'm not logged in to the webpage the effect is the same. I'm presented with the logon page. Then if I enter my login and password the download window will show up allowing me to save the file.

So I think that SetCredentials part of the code is not working properly cause if I debug.print WHTTP.ResponseBody it's html code instead of the acutal file data.

Is there a way to pass userid and password to the WinHTTP so I could be able to properly save the file?

Here's the page address:

https://sst.msde.state.md.us/

=======================EDIT:========================

So I've played a little bit with it today and I think I'm moving forward. Here's what I got. I Modyfied the code like this:

Sub SaveFileFromURL()

Dim FileNum As Long
Dim FileData() As Byte
Dim WHTTP As Object

fileUrl = "https://www.website.com/dir1/dir2/file.xls"
filePath = "C:\myfile.xls"

myuser = "username"
mypass = "password"

strAuthenticate = "start-url=%2F&user=" & myuser & "&password=" & mypass & "&switch=Log+In"

Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")

WHTTP.Open "POST", fileUrl, False
WHTTP.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
WHTTP.Send strAuthenticate

WHTTP.Open "GET", fileUrl, False
WHTTP.Send

Debug.Print WHTTP.GetAllResponseHeaders()

FileData = WHTTP.ResponseBody
Set WHTTP = Nothing

FileNum = FreeFile
Open filePath For Binary Access Write As #FileNum
    Put #FileNum, 1, FileData
Close #FileNum

MsgBox "File has been saved!", vbInformation, "Success"

End Sub

When I Debug.Print WHTTP.GetAllResponseHeaders() I get e.g.:

Accept-Ranges: bytes
Content-Disposition: attachement; filename="xxx"
Content-Length: xxxxxx
Content-Type: application/octet-stream

So I think that authentication worked but I still cannot save the file. When I continue with:

FileData = WHTTP.ResponseBody
Set WHTTP = Nothing

FileNum = FreeFile
Open filePath For Binary Access Write As #FileNum
    Put #FileNum, 1, FileData
Close #FileNum

The content of the saved file is the html webpage itself, but not the file.

Did I do the authentication rigth and the problem is with saving the file to the disk or still is there a problem with authentication and that's why I cannot save it? Any clues?

SetCredentials doesn't seem very reliable. You can use a POST/GET method instead. — David Zemens, Feb 26 '14 at 20:11
Use POST/GET method where/how? I'm using it in my code just before set credentials but it doesn't work. Any sugestions? — user2267971, Feb 26 '14 at 20:21
@user2267971 This answer: http://stackoverflow.com/questions/891427/how-to-make-a-post-request-to-a-page-that-may-redirect-to-a-login-page may be helpful. Aside from that, you could automate the log in page through IE, submit your user ID and password, and then navigate to the page to get the information you need. — MattB, Feb 26 '14 at 21:08
I've already automated the logon to get the direct file urls at the first place. But I want to be able to download the file "silently", without any popup download windows. Getting WHTTP.ResponseBody works great for other nonpassprotected sites and for some passprotected http sites as well. The problem is with this particular https site. And BTW I have to use IE for this one. — user2267971, Feb 26 '14 at 21:12
Edit: Looking at html code of the website that I provided is it possible to figure out what method has to be used to pass the username and password and properly download the file? — user2267971, Feb 26 '14 at 21:19
If you use Chrome browser, you should be able to use the developer tools to examine the POST request when you manually submit the credentials. This should help you structure a POST request in VBA, which replicate the way that your browser (IE) will send the request. — David Zemens, Feb 27 '14 at 01:40
Ok, I edited my main post cause I think we have progress today. Any new suggestions? — user2267971, Feb 27 '14 at 18:44

score 14 · Accepted Answer · edited May 23 '17 at 12:32

Ok, I did it. Here the code:

Sub SaveFileFromURL()

Dim FileNum As Long
Dim FileData() As Byte
Dim WHTTP As Object

mainUrl = "https://www.website.com/"
fileUrl = "https://www.website.com/dir1/dir2/file.xls"
filePath = "C:\myfile.xls"

myuser = "username"
mypass = "password"

'@David Zemens, I got this by examining webpage code using Chrome, thanks!
strAuthenticate = "start-url=%2F&user=" & myuser & "&password=" & mypass & "&switch=Log+In"

Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")

'I figured out that you have to POST authentication string to the main website address not to the direct file address
WHTTP.Open "POST", mainUrl, False 'WHTTP.Open "POST", fileUrl, False
WHTTP.SetRequestHeader "Content-Type", "application/x-www-form-urlencoded"
WHTTP.Send strAuthenticate

'Then you have to GET direct file url
WHTTP.Open "GET", fileUrl, False
WHTTP.Send

FileData = WHTTP.ResponseBody
Set WHTTP = Nothing

'Save the file
FileNum = FreeFile
Open filePath For Binary Access Write As #FileNum
    Put #FileNum, 1, FileData
Close #FileNum

MsgBox "File has been saved!", vbInformation, "Success"

End Sub

Thanks for all your help.

BTW I've found this posts very useful:

http://www.mrexcel.com/forum/excel-questions/353006-download-file-excel.html

Not understanding why WinHTTP does NOT authenticate certain HTTPS resource

How to parse line by line WinHTTP response: UTF-8 encoded CSV?

VBA WinHTTP to download file from password proteced https website

1 Answers1

Linked