Paths, Separators and Checking a File Exists

A path is only text, and that is the problem

Every file has an address: C:\Reports\2026\March.csv on Windows, /Users/anna/Reports/March.csv on a Mac. To VBA that address is a plain String - it is not checked, not corrected and not complained about until the moment you use it. Almost every failure in file automation is a path that is slightly wrong: a doubled backslash, a missing one, a folder that moved, or a workbook that was never saved. This lesson gives you three small functions that end that whole category of bug, and one diagnostic macro that tells you where you actually are.

Run this first

Paste everything below into a standard module (Alt+F11, then Insert - Module) and press F5 inside ShowPathDiagnostics. It needs no file and creates nothing. It prints, both to the Immediate window (Ctrl+G) and to a message box, every path fact your later macros will depend on.

Option Explicit

Sub ShowPathDiagnostics()
    Dim sep As String
    Dim bookFolder As String
    Dim targetFile As String
    Dim msg As String

    sep = Application.PathSeparator
    bookFolder = ThisWorkbook.Path

    msg = "Separator on this machine: " & sep & vbCrLf

    If Len(bookFolder) = 0 Then
        msg = msg & "ThisWorkbook.Path is EMPTY - this file has never been saved." & vbCrLf
    Else
        msg = msg & "Workbook folder: " & bookFolder & vbCrLf
    End If

    msg = msg & "Workbook file:   " & ThisWorkbook.FullName & vbCrLf
    msg = msg & "Temp folder:     " & Environ$("TEMP") & vbCrLf & vbCrLf

    targetFile = JoinPath(bookFolder, "March.csv")

    msg = msg & "Looking for:  " & targetFile & vbCrLf
    msg = msg & "File found:   " & FileExists(targetFile) & vbCrLf
    msg = msg & "Folder found: " & FolderExists(bookFolder) & vbCrLf & vbCrLf
    msg = msg & "Control test, must say True: " & FileExists(ThisWorkbook.FullName)

    Debug.Print msg
    MsgBox msg, vbInformation, "Path diagnostics"
End Sub

Public Function JoinPath(ByVal folderPath As String, ByVal fileName As String) As String
    Dim sep As String

    sep = Application.PathSeparator

    If Len(folderPath) = 0 Then
        JoinPath = fileName
    ElseIf Right$(folderPath, 1) = sep Then
        JoinPath = folderPath & fileName
    Else
        JoinPath = folderPath & sep & fileName
    End If
End Function

Public Function FileExists(ByVal fullPath As String) As Boolean
    Dim found As String

    If Len(fullPath) = 0 Then Exit Function

    On Error Resume Next
    found = Dir(fullPath, vbNormal)
    On Error GoTo 0

    FileExists = (Len(found) > 0)
End Function

Public Function FolderExists(ByVal folderPath As String) As Boolean
    Dim attrFlags As Long

    If Len(folderPath) = 0 Then Exit Function

    On Error Resume Next
    attrFlags = GetAttr(folderPath)
    If Err.Number <> 0 Then
        Err.Clear
        Exit Function
    End If
    On Error GoTo 0

    FolderExists = ((attrFlags And vbDirectory) = vbDirectory)
End Function

What each piece is for

• Application.PathSeparator returns \ on Windows and / on Mac. Use it instead of typing a backslash and your code survives the trip between platforms.
• ThisWorkbook.Path is the folder holding the file that contains your code - and it is an empty string until the workbook has been saved at least once. Do not confuse it with ActiveWorkbook.Path, which follows whichever window is in front.
• JoinPath glues folder and file name with exactly one separator, whether or not the folder already ends in one. That single If is the end of the C:\Reports\\March.csv bug.
• FileExists wraps Dir, which returns the bare file name when the file is there and an empty string when it is not. VBA has no True/False version, so everybody writes this one.
• FolderExists uses GetAttr instead, and tests the vbDirectory bit. GetAttr raises an error on a path that does not exist, which is why it sits inside On Error Resume Next - the error is the answer.
• The control test checks ThisWorkbook.FullName, which must exist by definition. If that line says False, the problem is your environment, not your path.

The trap: Dir cannot see folders unless you ask it to

Called with one argument, Dir looks for files only. Give it a perfectly good folder and it returns an empty string, your FileExists-style check reports False, and you spend the next hour proving that a folder which is plainly there is missing. The second argument fixes it - Dir(path, vbDirectory) - but that flag also matches files, so a file of the same name would pass a folder check. That is why the function above uses GetAttr. Run this to see all three behaviours at once:

Sub TheFolderTrap()
    Dim folderPath As String

    folderPath = ThisWorkbook.Path         ' a folder that definitely exists

    Debug.Print "Plain Dir:         [" & Dir(folderPath) & "]"
    Debug.Print "Dir + vbDirectory: [" & Dir(folderPath, vbDirectory) & "]"
    Debug.Print "GetAttr:            " & FolderExists(folderPath)
End Sub

Two more edges worth knowing

• Dir treats * and ? in a path as wildcards, so a name arriving from a network share can turn your existence check into a silent search.
• CurDir is not your workbook's folder. It is whatever folder Excel last used, and it changes under you. Never build a path from it.

Using the helpers

Sub UseTheHelpers()
    Dim csvPath As String

    csvPath = JoinPath(ThisWorkbook.Path, "March.csv")

    If Not FileExists(csvPath) Then
        MsgBox "Put March.csv next to this workbook first:" & vbCrLf & csvPath, _
               vbExclamation
        Exit Sub
    End If

    ' ... your import code goes here, knowing the file is really there
End Sub

How it goes on

Everything here works on Windows and on Mac. The next lesson puts Dir in a loop to walk a whole folder, which has one rule that catches everyone: a second Dir call inside the loop resets the search. Then come the FileSystemObject (richer, but Windows-only), reading and writing text files, parsing CSV without tearing quoted fields apart, merging many exports into one sheet, and letting the user pick the file.