VBA 检查文件（来自网站）是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25428611/
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 Check if file (from website) exists
提问by Mayou
Please bear with me as I am a beginner at VBA.
请耐心等待，因为我是 VBA 的初学者。
I am trying to open an excel file through a website using VBA. The address (path) of the file changes from month to month. For example:
我正在尝试使用 VBA 通过网站打开一个 excel 文件。文件的地址（路径）逐月变化。例如：
In July, the file name is: http://www.clevelandfed.org/research/data/inflation_expectations/2014/July/excel1.xls
In August, the file name is: http://www.clevelandfed.org/research/data/inflation_expectations/2014/August/excel1.xls
七月，文件名：http: //www.clevelandfed.org/research/data/inflation_expectations/2014/July/excel1.xls
八月，文件名：http: //www.clevelandfed.org/research/data/inflation_expectations/2014/August/excel1.xls
The problem is that I never know in advance when the new file for the month is going to be published. Therefore, I need to check in the VBA code if the current month file exist, if not, I would just open the previous month file.
问题是我从来不知道这个月的新文件什么时候发布。因此，如果当前月份文件存在，我需要检查VBA代码，如果不存在，我将只打开上个月文件。
This is what I have tried:
这是我尝试过的：
Dim DirFile As String
Dim wbA As Workbook
DirFile = "http://www.clevelandfed.org/research/data/inflation_expectations/" & Format(Now, "YYYY") & "/" & Format(Now, "MMMM") & "/excel1.xls"
' Check if the file for current month does not exist, open previous month's file
If Len(Dir(DirFile)) = 0 Then
Set wbA = Workbooks.Open("http://www.clevelandfed.org/research/data/inflation_expectations/" & Format(Now, "YYYY") & "/" & Format(DateAdd("m", -1, Date), "MMMM") & "/excel1.xls", IgnoreReadOnlyRecommended:=True)
'If the current month file exists, open it
Else
Set wbA = Workbooks.Open(DirFile, IgnoreReadOnlyRecommended:=True)
End If
However, this results in an error:
但是，这会导致错误：
I am assuming this is due to the fact that this is a file that resides on a website. Could anyone please help resolve this issue?
我假设这是因为这是一个驻留在网站上的文件。有人可以帮忙解决这个问题吗？
Thank you!
谢谢！
回答by evenprime
You are correct in assuming Dir()doesn't work for files residing on Websites
您假设Dir()对驻留在网站上的文件不起作用是正确的
Dir FunctionReturns a String representing the name of a file, directory, or folder that matches a specified pattern or file attribute, or the volume label of a drive.
Dir 函数返回一个字符串，该字符串表示与指定模式或文件属性或驱动器卷标匹配的文件、目录或文件夹的名称。
What you need is the following function to check if the URL is valid, P.S. Place the function in Module
您需要的是以下函数来检查 URL 是否有效， PS 将该函数放在模块中
Function URLExists(url As String) As Boolean
Dim Request As Object
Dim ff As Integer
Dim rc As Variant
On Error GoTo EndNow
Set Request = CreateObject("WinHttp.WinHttpRequest.5.1")
With Request
.Open "GET", url, False
.Send
rc = .StatusText
End With
Set Request = Nothing
If rc = "OK" Then URLExists = True
Exit Function
EndNow:
End Function
Then use the function in your Macro
然后在您的宏中使用该功能
If URLExists(DirFile) = 0 Then
Set wbA = Workbooks.Open("http://www.clevelandfed.org/research/data/inflation_expectations/" & Format(Now, "YYYY") & "/" & Format(DateAdd("m", -1, Date), "MMMM") & "/excel1.xls", IgnoreReadOnlyRecommended:=True)
wbA.Activate
'If the current month file exists, open it
Else
Set wbA = Workbooks.Open(DirFile, IgnoreReadOnlyRecommended:=True)
End If
回答by Dick Kusleika
Here's an alternative. Just try to open it and see if it fails. If it does, open last months. Not better, just different.
这是一个替代方案。只需尝试打开它，看看它是否失败。如果是，请在上个月打开。不是更好，只是不同。
Public Function GetCFWorkbook() As Workbook
Dim wb As Workbook
Dim dt As Date
dt = Now
Const sURL As String = "http://www.clevelandfed.org/research/data/inflation_expectations/"
On Error Resume Next
Application.DisplayAlerts = False
Set wb = Workbooks.Open(sURL & Format(dt, "yyyy/mmmm") & "/excel1.xls")
Application.DisplayAlerts = True
On Error GoTo 0
If wb Is Nothing Then
Set wb = Workbooks.Open(sURL & Format(DateAdd("m", -1, dt), "yyyy/mmmm") & "/excel1.xls")
End If
Set GetCFWorkbook = wb
End Function
