VBA WinHTTP to download file from password proteced https website
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?
user2267971
Updated on June 29, 2020Comments
-
user2267971 almost 4 years
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?