excel vba windows file-io filesystemobject

Check file existence in VBA for files with long path

Windows has a 256 characters limit for file paths, but users can definitely create files with path longer than 256 characters. Let's call file paths shorter than or equal 255 characters as short paths, and those longer than or equal to 256 characters as long path.

While working on another problem, I need to check whether a file exists given its file path, regardless of the length of the file path, regardless of normal paths or UNC paths on Windows. Is it possible with VBA?

What I have tried

In VBA, there are two main ways to check file existence:

Use Dir().

Dim isExists As Boolean
isExists = Dir("some\file\path") = vbNullString

Use FileSystemObject (FSO).

Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")

Dim isExists As Boolean
isExists = objFSO.FileExists("some\file\path")

Dir() is not useful here because:

It does not support file paths with Unicode characters, e.g. Chinese characters. And there are Chinese characters in the file paths that I work with.
For long paths, it throws File Not Found error no matter the file exists or not.

FileSystemObject, on the other hand, supports file paths with Unicode characters, but I cannot get it to report file existence correctly for files with a long path.

Whenever a long path is given, objFSO.FileExists(...) returns False even when the file obviously exists in Windows File Explorer?!

For example,

' Short paths: `True` if file exists and `False` otherwise, as expected.
objFSO.FileExists("C:\some\short\path")  ' Windows native path.
objFSO.FileExists("\\server\drive\some\short\path")  ' UNC path.

' Long paths: `False` no matter the file exists or not, unfortunately.
objFSO.FileExists("C:\some\very\long\path\that\may\have\unicode\characters")  ' Windows native path.
objFSO.FileExists("\\server\drive\some\very\long\path\that\may\have\unicode\characters")  ' UNC path.

I have read the Microsoft VBA documentation many times, e.g. FileExists method, but with no luck.

_{Please forgive me to insert a small rant here that nowhere in the documentation of Dir() mentions the fact that it does not support Unicode characters. Come on!}

What I expect

Can anyone please point out what I may have missed, or answer the question whether this is solvable with VBA? If so, what can I do? It will be kind of you if you include some code examples to illustrate your answer. Thank you!

Solution

After more research, I have come up with a workable VBA function to do what I want.

Please feel free to comment if there is anything that can be improved.

Function `IsFileExists`

Function IsFileExists(filepath As String) As Boolean
' Determine whether a file exists given its (absolute) file path, regardless
' of path length.
'
' filepath:
'     The (absolute) file path to be checked for file existence. Can be 
'     native paths or UNC paths, short or long, extended with "\\?\" or 
'     "\\?\UNC" or not.
' Return: True if the file exists, False otherwise.

    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")

    ' FileSystemObject is sufficient for short paths.
    If Len(filepath) <= 255 Then
        IsFileExists = objFSO.FileExists(filepath)
        Exit Function
    End If

    ' Long paths.
    If Left(filepath, 4) = "\\?\" Then
        ' Example: \\?\UNC\Server\Share\Some\Very\Long\Path
        ' Example: \\?\C:\Some\Very\Long\Path
        ' No action.
    ElseIf Left(filepath, 2) = "\\" Then
        ' Example: \\Server\Share\Some\Very\Long\Path
        filepath = objFSO.BuildPath("\\?\UNC\", Mid(filepath, 2))
    Else
        ' Example: C:\Some\Very\Long\Path
        filepath = objFSO.BuildPath("\\?\", filepath)
    End If

    IsFileExists = objFSO.FileExists(filepath)
End Function

Brief Explanation

As mentioned in Eugene's prompt answer (Thanks a lot to him), Windows API limits the length of file paths to 260 characters, but it is possible to have extended file paths by prefixing the paths with \\?\ or \\?\UNC\.

My function above distinguishes short paths and long paths, and extend the paths when needed, and then uses FileSystemObject to check file existence.

One caveat of the function is that \\?\ cannot be used on relative paths, but I wrote this function with absolute paths in mind only. Long relative paths may need to be converted to absolute paths before inputting to this function.

Other method(s)

`PathFileExistsW` in `shlwapi`

As of writing, I cannot successfully make PathFileExistsW in shlwapi to work using Declare Function in VBA. And I am a bit skeptical about it because Microsoft documentation says the said function's only parameter accepts "[a] pointer to a null-terminated string of maximum length MAX_PATH that contains the full path of the object to verify." (emphasis mine). I shall update when I can make it work to test it.