How to Check If a File Exists Using Excel VBA

When automating tasks in Excel, it’s 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 guide provides a simple yet comprehensive VBA function to safely check if a file exists, whether it’s stored locally, on a network share, or accessible via a URL.

Why You Should Check for File Existence

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

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

Adding a file existence check improves the reliability and safety of your Excel automation.

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 on your computer or a connected network share.

2. URLs: For web-based files, it performs aN HTTP HEAD request to determine if the resource is available without downloading the file.

3. Automatic Path Detection: The function identifies whether a path is a URL or local/network path and uses the appropriate check.

This design ensures your macros can safely verify files in most common storage scenarios.

How to Install the VBA Function

1. From the Developer ribbon 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

This macro displays True if the file exists and False if it does not, covering local, network, and URL-based files.

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.
• Platform differences: On macOS, URL checks may not work due to limited support for XMLHTTP.

For most Excel automation scenarios, however, the function provides a practical and reliable solution.

Summary

Checking whether a file exists is a simple but essential step for reliable Excel automation. This VBA function works for local, network, and URL-based files, preventing errors and improving workflow stability. Incorporating this check into your macros helps ensure your automation runs smoothly and safely.