vba VBA从带有登录名和密码的网站下载csv文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22675768/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA to download csv file from a website with login and password
提问by Mark
Ole Henrik Skogstr?m kindly posted a reply in the thread: "How do i download a file using VBA (Without internet explorer)".
Ole Henrik Skogstr?m 好心地在帖子中回复了：“我如何使用 VBA（没有 Internet Explorer）下载文件”。
Have used his code as I wish to download a csv file from www.ft.com and save it to the temp file on my c drive. It is not that often I need to do this, so I decided to use simple excel VBA. I have set up a temporary test subscription account at www.FT.com to illustrate what I would like to download, username is "[email protected]" password is "fttestpassword".
使用了他的代码，因为我希望从 www.ft.com 下载一个 csv 文件并将其保存到我的 c 驱动器上的临时文件中。我并不经常需要这样做，所以我决定使用简单的 excel VBA。我在 www.FT.com 设置了一个临时测试订阅帐户来说明我想下载的内容，用户名是“[email protected]”密码是“fttestpassword”。
After logging in, the "export data" link can be seen in the top right hand side of the page:
登录后，在页面右上角可以看到“导出数据”链接：
The url passed when you click this link is:
单击此链接时传递的 url 为：
The following code returns a file, but this file just has "{"json":{"triggerLogin":true}}" in it.
以下代码返回一个文件，但该文件中只有 "{"json":{"triggerLogin":true}}"。
Sub downloadingpositions()
Dim myURL As String
myURL = "http://portfolio.ft.com/PortfolioAPI/ExportToCSV?containerID=3415c458-40bf-4e63-903a-37302a88bd83&type=Holdings&customName=suggested__0YourPortfolio&duration=15&startDate=undefined&endDate=undefined"
Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "[email protected]", "fttestpasword"
WinHttpReq.Send
myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.responseBody
oStream.SaveToFile "c:\temp\testing.csv ", 2 ' 1 = no overwrite, 2 = overwrite
oStream.Close
End If
End Sub
Can anyone point me in the right direction as to why I'm not getting logged in/ not getting the full csv file??
谁能指出我为什么没有登录/没有获得完整的 csv 文件的正确方向？
采纳答案by Mark
First thing I noticed about your code is that the password was missing an "s". Then when you have a strange long number in the url is because there is some kind of authentication needed to generate the url "3415c458-40bf-4e63-903a-37302a88bd83"
我注意到您的代码的第一件事是密码缺少“s”。那么当你在 url 中有一个奇怪的长数字是因为需要某种身份验证来生成 url "3415c458-40bf-4e63-903a-37302a88bd83"
When this happens you need to locate the signin url and then use a "POST" method to validate the data. Once you have done that then you can send the regular "GET" request and the data will show.
发生这种情况时，您需要找到登录 url，然后使用“POST”方法来验证数据。完成后，您可以发送常规的“GET”请求，数据将显示。
Sub downloadingpositions()
Const strLOGING_URL As String = "https://registration.ft.com/registration/barrier/[email protected]&password=fttestpassword"
Const strPORTFOLIO_URL As String = "http://portfolio.ft.com/PortfolioAPI/ExportToCSV?containerID=3415c458-40bf-4e63-903a-37302a88bd83&type=Holdings&customName=suggested__0YourPortfolio&duration=15&startDate=undefined&endDate=undefined"
Const strINCORRECT_CREDENTIALS As String = "Your email address and password do not match our records"
Const strFILE_NAME As String = "c:\temp\testing.csv"
Dim WinHttpReq As Object
Dim oStream As Object
Set WinHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
' Post to the url to set the credentials
WinHttpReq.Open "POST", strLOGING_URL, 0
WinHttpReq.Send
' Make sure authetication went through
If Not UCase$(WinHttpReq.ResponseText) Like "*" & UCase$(strINCORRECT_CREDENTIALS) & "*" Then
' Get the information.
WinHttpReq.Open "GET", strPORTFOLIO_URL, 0
WinHttpReq.Send
' If we have succedeed then write the response to the file.
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write WinHttpReq.ResponseBody
oStream.SaveToFile strFILE_NAME, 2 ' 1 = no overwrite, 2 = overwrite
oStream.Close
End If
Else
MsgBox strINCORRECT_CREDENTIALS, vbOKOnly + vbCritical, "Error"
End If
End Sub
I hope this helps :)
我希望这有帮助 ：）
