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

Question

我正在尝试使用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

问题出在认证上。文件已经被保存，但是当我在Excel中打开它时，只有html登录页面而不是实际的文件。如果我复制直接的文件url并将其粘贴到浏览器地址栏中，而此时我没有登录到网页，则效果相同。我会看到登录页面。然后，如果我输入我的登录名和密码，下载窗口就会出现并允许我保存文件。

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

是否有一种方法可以将用户ID和密码传递给WinHTTP，以便我能够正确地保存该文件？

以下是该页面的地址：

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

=======================编辑：========================

今天我稍微试用了一下，我觉得我正在向前迈进。这是我所得到的结果。我像这样修改了代码：

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

当我调试打印WHTTP.GetAllResponseHeaders()，我得到如下信息：

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

我认为身份验证已经成功，但我仍然无法保存文件。继续使用以下命令：

FileData = WHTTP.ResponseBody
Set WHTTP = Nothing

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

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

我是否正确进行了身份验证，问题在于将文件保存到磁盘上还是仍然存在身份验证问题，导致无法保存？有任何线索吗？

- user2267971

---

SetCredentials似乎不是很可靠。您可以使用POST/GET方法代替。 - David Zemens

在哪里/如何使用POST/GET方法？我在我的代码中在设置凭据之前使用它，但它不起作用。有什么建议吗？ - user2267971

@user2267971 这个回答：https://dev59.com/8kfRa4cB1Zd3GeqP_KxJ 可能会有所帮助。除此之外，您还可以通过IE自动化登录页面，提交您的用户ID和密码，然后导航到页面以获取所需的信息。 - MattB

编辑：查看我提供的网站的HTML代码，是否可以确定必须使用哪种方法来传递用户名和密码并正确下载文件？ - user2267971

如果您使用Chrome浏览器，您应该能够使用开发人员工具来检查手动提交凭据时的POST请求。这应该可以帮助您在VBA中构建一个POST请求，以复制浏览器（IE）发送请求的方式。 - David Zemens

Answer 1

好的，我做到了。这是代码：

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

感谢您的所有帮助。

顺便说一下，我发现这些帖子非常有用:

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