CODEWITHSUNDEEP
vbaexcel
Mayou
Mayou

Reputation: 8818

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:

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!

Upvotes: 3

Views: 21773

Answers (3)

Vityata
Vityata

Reputation: 43565

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

Upvotes: 1

Dick Kusleika
Dick Kusleika

Reputation: 33145

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

Upvotes: 0

Ravi Yenugu
Ravi Yenugu

Reputation: 3898

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

Upvotes: 10

Related Questions