VBA WinHTTP 从受密码保护的 https 网站下载文件

VBA WinHTTP to download file from password proteced https website

提问by user2267971

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

我正在尝试使用 WinHTTP 从受 https 密码保护的站点保存文件。这是代码：

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.

问题在于身份验证。该文件正在保存，但是当我在 Excel 中打开它时，它只是 html 登录页面而不是实际文件。如果我复制直接文件 url 并将其粘贴到浏览器地址栏中并且我没有登录到网页，效果是一样的。我看到了登录页面。然后，如果我输入我的登录名和密码，将出现下载窗口，允许我保存文件。

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.

所以我认为代码的 SetCredentials 部分无法正常工作，因为如果我 debug.print WHTTP.ResponseBody 它是 html 代码而不是实际文件数据。

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

有没有办法将用户名和密码传递给 WinHTTP，以便我能够正确保存文件？

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.:

当我 Debug.Print WHTTP.GetAllResponseHeaders() 我得到例如：

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.

保存文件的内容是html网页本身，而不是文件。

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?

我是否进行了身份验证，问题在于将文件保存到磁盘，还是身份验证仍然存在问题，这就是我无法保存它的原因？有什么线索吗？

回答by user2267971

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

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

Not understanding why WinHTTP does NOT authenticate certain HTTPS resource

不明白为什么 WinHTTP 不验证某些 HTTPS 资源

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

如何逐行解析 WinHTTP 响应：UTF-8 编码的 CSV？