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代码而不是acutal文件数据。
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
当我调试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我发现这篇文章非常有用：
BTW I've found this posts very useful:
http://www.mrexcel.com/forum/excel-questions/353006-download-file-excel.html
这篇关于VBA WinHTTP从密码保护的https网站下载文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
