VBA Check if file (from website) exists

Question

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!

Ravi Yenugu · Accepted Answer · 2014-08-21 14:28:06Z

10

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

edited Aug 21, 2014 at 14:28

answered Aug 21, 2014 at 14:20

Ravi Yenugu

3,8885 gold badges43 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mayou Over a year ago

This is perfect!!! Thank you so much!! (P.S: I assume you have to put the function definition on a different module, right?) Thanks a lot again, it works like a charm!

Vityata · Accepted Answer · 2022-11-23 15:36:00Z

1

This solution works, even using late binding, so you do not need to add a library manually:

Public Function CheckUrlExists(url) As Boolean
        
    On Error GoTo CheckUrlExists_Error
    
    Dim xmlhttp As Object
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
 
    xmlhttp.Open "HEAD", url, False
    xmlhttp.send
    
    If xmlhttp.Status = 200 Then
        CheckUrlExists = True
    Else
        CheckUrlExists = False
    End If
    
    Exit Function
    
CheckUrlExists_Error:
    CheckUrlExists = False
    
End Function

answered Nov 23, 2022 at 15:36

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Comments

Dick Kusleika · Accepted Answer · 2014-08-21 16:09:13Z

0

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

answered Aug 21, 2014 at 16:09

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Collectives™ on Stack Overflow

VBA Check if file (from website) exists

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related