VBA Check if file (from website) exists

by

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") & "https://stackoverflow.com/" & 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

More Related Contents:

  1. delete Autofilter Field resullt using the header Name
  2. Somehow it’s not copying [closed]
  3. VBA macro that search for file in multiple subfolders
  4. So, I have 6 “master” files to then divide into 40 separate files
  5. Getting ScriptControl to work with Excel 2010 x64
  6. Application.Match gives type mismatch
  7. Count and Highlight keywords within phrases
  8. Transpose a range in VBA
  9. Protecting cells in Excel but allow these to be modified by VBA script
  10. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  11. Detect merged cells in VBA Excel with MergeArea
  12. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  13. Parse HTML content in VBA
  14. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  15. How do I name a spreadsheet automatically, by referencing two cells?
  16. Excel VBA Delete Rows
  17. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  18. Should I turn .CutCopyMode back on before exiting my sub procedure?
  19. VBA: How to delete filtered rows in Excel?
  20. How to use VBA SaveAs without closing calling workbook?
  21. Removing the password from a VBA project
  22. Removing special characters VBA Excel
  23. Excel VBA Formula German/French/Italian/Russian/Dutch/Foreign Function
  24. Iterate over slicer via VBA and select a single item each time
  25. Loop through Excel Sheets
  26. Return a range from A1 to the true last used cell
  27. How to add items to a combobox in a form in excel VBA?
  28. How can I open this VBA workbook faster?
  29. How do I avoid run-time error when a worksheet is protected in MS-Excel?
  30. VBA inheritance, analog of super

Leave a Comment