How to Check If a File Exists Using Excel VBA

When automating tasks in Excel, it is important to verify whether a file exists before attempting to open or process it. Trying to access a file that does not exist can trigger runtime errors, cause macros to fail, or disrupt automated workflows.

This article provides a reusable VBA function to check whether a file is available locally, on a network share, or at a URL.

Why Check for File Existence

Failing to confirm that a file exists before using it in a macro can lead to:

• Runtime errors that stop code unexpectedly.
• Broken automation workflows.
• Confusion in multi-user or networked environments.
• Potential data loss if files are assumed to exist.

Attempting to open a nonexistent file directly, for example, raises runtime error 1004 when using Workbooks.Open, or error 53 (File not found) when using statements such as Kill or the Open statement, depending on the method used.

Adding a file existence check improves the reliability of Excel automation workflows.

VBA Function to Check If a File Exists

The function below handles local paths, UNC/network paths, and URL-based files. It uses VBA’s native Dir function for local and network files and an HTTP HEAD request to verify URLs.

Option Explicit

' Returns True if the file exists, False otherwise.
Public Function FileExists(ByVal filePath As String) As Boolean
  If IsUrlPath(filePath) Then
    FileExists = UrlFileExists(filePath)
  Else
    FileExists = LocalFileExists(filePath)
  End If
End Function

' Checks for local or network-based files.
Private Function LocalFileExists(ByVal filePath As String) As Boolean
  On Error Resume Next
  LocalFileExists = (Len(Dir$(filePath, vbNormal)) > 0)
  On Error GoTo 0
End Function

' Checks whether a URL exists using an HTTP HEAD request.
Private Function UrlFileExists(ByVal url As String) As Boolean
  Dim http As Object

  On Error GoTo ErrHandler

  Set http = CreateObject("MSXML2.XMLHTTP")
  http.Open "HEAD", url, False
  http.send

  UrlFileExists = (http.Status >= 200 And http.Status < 400)
  Exit Function

ErrHandler:
  UrlFileExists = False
End Function

' Determines whether the supplied path is an HTTP or HTTPS URL.
Private Function IsUrlPath(ByVal path As String) As Boolean
  Dim lowerPath As String

  lowerPath = LCase$(path)

  IsUrlPath = (Left$(lowerPath, 7) = "http://") _
           Or (Left$(lowerPath, 8) = "https://")
End Function

How It Works

1. Local or Network Paths: The function uses Dir$ to check if the file exists locally or on a connected network share.
2. URLs: For web-based files, the function sends an HTTP HEAD request, which checks availability without downloading the file, and treats a successful response as evidence the resource exists.
3. Automatic Path Detection: The function identifies whether a path is a URL or local/network path and uses the appropriate check.

This design allows macros to verify files in most common storage scenarios.

Alternative Approach: FileSystemObject

The FileSystemObject library provides another common method for checking file existence in VBA. Its FileExists method offers functionality similar to the Dir$ approach shown above, though it requires either a reference to the Microsoft Scripting Runtime library or late binding through CreateObject. Some developers find the FileSystemObject syntax more readable, though it carries a small amount of additional overhead compared to Dir$.

Function FileExistsFSO(ByVal filePath As String) As Boolean
  Dim fso As Object
  Set fso = CreateObject("Scripting.FileSystemObject")
  FileExistsFSO = fso.FileExists(filePath)
End Function

How to Install the VBA Function

1. From the Developer tab in Excel, click Visual Basic.
2. In the Visual Basic for Applications window, go to Insert > Module.
3. Paste the FileExists, LocalFileExists, UrlFileExists, and IsUrlPath functions into the new module.
4. Close the editor and return to Excel.

The function is now available to use in any macro within the workbook.

How to Use the VBA Function

Here is a simple example that demonstrates how to test different file locations:

Option Explicit

Sub TestFileExists()
  Dim filePath As String

  ' Test a local file
  filePath = "C:\temp\test.xlsx"
  MsgBox filePath & " exists? " & FileExists(filePath)

  ' Test a network file
  filePath = "\\Server\Share\test.xlsx"
  MsgBox filePath & " exists? " & FileExists(filePath)

  ' Test a URL
  filePath = "https://www.example.com/test.xlsx"
  MsgBox filePath & " exists? " & FileExists(filePath)
End Sub

Sub TestGuardPattern()
  Dim filePath As String

  filePath = "C:\temp\test.xlsx"

  ' Guard pattern on local file
  If Not FileExists(filePath) Then
    MsgBox "File not found."
    Exit Sub
  End If

  Workbooks.Open filePath
End Sub

TestFileExists displays True if the file exists and False if it does not, covering local, network, and URL-based files. TestGuardPattern demonstrates a typical guard pattern that skips Workbooks.Open when the target file is missing.

Known Limitations

While this function is versatile, there are a few things to keep in mind:

• URL checks may fail: Some servers block HTTP HEAD requests or require authentication.
• Permission issues: Local or network files may appear non-existent if the user lacks read access.
• Transient network issues: Network shares or virtual drives may temporarily appear unavailable.
• File type is not verified: The function only checks existence, not whether the file is a valid Excel workbook.
• Wildcard characters: If filePath contains * or ?, Dir$ may match a different file than the one intended.
• Platform differences: On macOS, CreateObject cannot create ActiveX objects such as MSXML2.XMLHTTP, so URL checks will not work.

Summary

This VBA function works for local, network, and URL-based files, preventing some common errors and improving workflow stability. It is most reliable for local and network paths. URL checks carry the additional caveats noted above, including the macOS limitation.