VBA WinHTTP 从受密码保护的 https 网站下载文件 [英] VBA WinHTTP to download file from password proteced https website
问题描述
我正在尝试使用 WinHTTP 从受 https 密码保护的站点保存文件.代码如下:
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
问题在于身份验证.该文件正在保存，但是当我在 Excel 中打开它时，它只是 html 登录页面而不是实际文件.如果我复制直接文件 url 并将其粘贴到浏览器地址栏中并且我没有登录到网页，效果是一样的.我看到了登录页面.然后，如果我输入我的登录名和密码，就会出现下载窗口，允许我保存文件.
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.
所以我认为代码的 SetCredentials 部分无法正常工作，因为如果我 debug.print WHTTP.ResponseBody 它是 html 代码而不是实际文件数据.
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.
有没有办法将用户名和密码传递给 WinHTTP 以便我能够正确保存文件?
Is there a way to pass userid and password to the WinHTTP so I could be able to properly save the file?
这是页面地址:
https://sst.msde.state.md.us/
================================================
===============================================
所以我今天玩了一点，我想我正在前进.这是我得到的.我像这样修改了代码:
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
当我 Debug.Print WHTTP.GetAllResponseHeaders() 我得到例如:
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
保存文件的内容是html网页本身，而不是文件.
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?
推荐答案
好的，我做到了.代码如下:
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
感谢您的帮助.
顺便说一句，我发现这篇文章非常有用:
BTW I've found this posts very useful:
http://www.mrexcel.com/论坛/excel-questions/353006-download-file-excel.html
如何逐行解析行 WinHTTP 响应:UTF-8 编码的 CSV?
这篇关于VBA WinHTTP 从受密码保护的 https 网站下载文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
