VBA Check if file (from website) exists

Please bear with me as I am a beginner at 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:

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

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.

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:

enter image description here

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!

You are correct in assuming Dir() doesn't work for files residing on Websites

Dir Function Returns 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.

What you need is the following function to check if the URL is valid, P.S. Place the function in Module

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

VBA Check if file (from website) exists

Related

Recent Posts