vba URLDownloadToFile 函数在循环中不起作用，因为即使没有要下载的文件，该函数也会返回 0
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16289238/
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
URLDownloadToFile Function in a loop does not work as the function returns 0 even if there is no file to download
提问by Deep Kumar Raman
I was building myself a excel sheet to keep track of my investments. I have this link of the stock exchange website that has a single zip file containing a CSV file of the trading data of the last trading day. The name of the zipfile is dynamic in the form "eq_csv.zip where the ddmmyy is the date of the trading day for which the data pertains to. Therefore, the available file may be of the current day or 2-4 days old if the markets were closed.
我正在为自己建立一个 Excel 表来跟踪我的投资。我有这个证券交易所网站的链接，它有一个包含最后一个交易日交易数据的 CSV 文件的 zip 文件。zipfile 的名称是“eq_csv.zip”形式的动态名称，其中 ddmmyy 是数据所属的交易日的日期。因此，如果市场关闭。
I built a module to check online everytime I open my excel file to get the most current online data. The code given below was supposed to loop starting with the current date and move back by 1 day till I got the valid zip file downloaded. For eg, if the current date is Apr 28 (Sun) and if the file on the online resource is of Apr 26 (Fri) (eq260413_CSV.zip), then my loop should go through 3 iterations (2 no file msgs and one file downloaded msg) and download the file eq260413_CSV.zip. As the file eq280413_CSV.zip or eq290413_CSV.zip do not exist at the online link mentioned, I expect to return an error and continue to loop. Whereas on running the code I find that the function just creates a dummy file eq280413_CSV.zip with no data during the very first pass and returns a value of 0 to iRet whereby exiting the loop. Can ANyone please help / throw some light
我构建了一个模块，每次打开我的 excel 文件时都可以在线检查，以获取最新的在线数据。下面给出的代码应该从当前日期开始循环并返回 1 天，直到我下载了有效的 zip 文件。例如，如果当前日期是 4 月 28 日（星期日），并且在线资源上的文件是 4 月 26 日（星期五）（eq260413_CSV.zip），那么我的循环应该经过 3 次迭代（2 次无文件 msgs 和一个文件下载 msg) 并下载文件 eq260413_CSV.zip。由于提到的在线链接中不存在文件 eq280413_CSV.zip 或 eq290413_CSV.zip，因此我希望返回错误并继续循环。而在运行代码时，我发现该函数只是在第一次传递期间创建了一个没有数据的虚拟文件 eq280413_CSV.zip，并向 iRet 返回值 0，从而退出循环。
Sub DownloadFile()
Worksheets("Online Equity Data").Activate
Dim StrURL As String
Dim strPath As String
Dim dDate As Date
Dim iRet As Long
dDate = Now() + 1
iRet = 1
vFolderName = "C:\Users\Deep\Documents\Finances\Test\"
Do While iRet <> 0
dDate = dDate - 1
StrURL = "http://www.bseindia.com/download/BhavCopy/Equity/eq" & Format(dDate, "ddmmyy") & "_csv.zip"
strPath = vFolderName & "eq" & Format(dDate, "ddmmyy") & "_csv.zip"
iRet = URLDownloadToFile(0, StrURL, strPath, 0, 0)
If iRet= 0
MsgBox "File eq" & Format(dDate, "ddmmyy") & "_csv.zip Downloaded"
Else
MsgBox "No File Named eq" & Format(dDate, "ddmmyy") & "_csv.zip"
End If
Loop
'More code Here to unzip and import the downloaded data
End Sub()
采纳答案by Siddharth Rout
The URLDownloadToFileFile API is not supposed to be used when the file/URL doesn't exists.
该URLDownloadToFile文件API是不应该在文件/ URL不存在使用。
You have to first check if the URL is valid and then use URLDownloadToFileif applicable.
您必须首先检查 URL 是否有效，然后URLDownloadToFile在适用时使用。
Use the below function written by Leith Ross (Picked up from HERE)
使用 Leith Ross 编写的以下函数（取自这里）
'Written: March 15, 2011
'Author: Leith Ross
Public PageSource As String
Public httpRequest As Object
Function GetURLStatus(ByVal URL As String, Optional AllowRedirects As Boolean)
Const WinHttpRequestOption_UserAgentString = 0
Const WinHttpRequestOption_EnableRedirects = 6
On Error Resume Next
Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
If httpRequest Is Nothing Then
Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5")
End If
Err.Clear
On Error GoTo 0
httpRequest.Option(WinHttpRequestOption_UserAgentString) = "Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0)"
httpRequest.Option(WinHttpRequestOption_EnableRedirects) = AllowRedirects
'Clear any pervious web page source information
PageSource = ""
'Add protocol if missing
If InStr(1, URL, "://") = 0 Then
URL = "http://" & URL
End If
'Launch the HTTP httpRequest synchronously
On Error Resume Next
httpRequest.Open "GET", URL, False
If Err.Number <> 0 Then
'Handle connection errors
GetURLStatus = Err.Description
Err.Clear
Exit Function
End If
On Error GoTo 0
'Send the http httpRequest for server status
On Error Resume Next
httpRequest.Send
httpRequest.WaitForResponse
If Err.Number <> 0 Then
' Handle server errors
PageSource = "Error"
GetURLStatus = Err.Description
Err.Clear
Else
'Show HTTP response info
GetURLStatus = httpRequest.Status & " - " & httpRequest.StatusText
'Save the web page text
PageSource = httpRequest.responsetext
End If
On Error GoTo 0
End Function
When the URL is OK you will get something like this
当 URL 正常时，你会得到这样的东西
And when it is not, you will get something like this
当它不是时，你会得到这样的东西
So all you need to do is look for 200 - OKand if you get that, then use the URLDownloadToFileto download the file.
所以你需要做的就是寻找200 - OK，如果你找到了，然后使用URLDownloadToFile下载文件。
