我正在尝试使用 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 代码而不是实际文件数据。
有没有办法将用户名和密码传递给 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
当我 Debug.Print 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网页本身,而不是文件本身。
我是否进行了正确的身份验证,问题在于将文件保存到磁盘,或者身份验证仍然存在问题,这就是为什么我无法保存它?有什么线索吗?