- 1 year ago
Check file existence in VBA for files with long path
In VBA, the built-in functions for file operations, such as Dir
and FileSystemObject
, have limitations with file paths that exceed 260 characters (known as the MAX_PATH limit). However, you can use the Windows API functions to check file existence for files with long paths. Here's an example of how you can achieve this:
vbaOption Explicit
Private Declare Function PathFileExistsW Lib "shlwapi.dll" (ByVal pszPath As Long) As LongPtr
Private Declare PtrSafe Function StrPtr Lib "msvbvm60.dll" Alias "StrPtr" (ByVal s As String) As LongPtr
Function FileExistsLongPath(ByVal filePath As String) As Boolean
Dim result As LongPtr
result = PathFileExistsW(StrPtr(filePath))
FileExistsLongPath = (result <> 0)
End Function
Sub TestFileExistsLongPath()
Dim filePath As String
filePath = "C:\very\long\path\to\file.txt"
If FileExistsLongPath(filePath) Then
MsgBox "File exists!"
Else
MsgBox "File does not exist!"
End If
End Sub
In this example:
The
PathFileExistsW
function from theshlwapi.dll
library is used to check the existence of the file with a long path. It accepts a Unicode (wide character) file path.The
StrPtr
function from themsvbvm60.dll
library is used to get the pointer to the Unicode string.The
FileExistsLongPath
function takes a file path as a parameter, callsPathFileExistsW
, and returnsTrue
if the file exists orFalse
otherwise.The
TestFileExistsLongPath
subroutine demonstrates how to use theFileExistsLongPath
function by passing a file path and displaying a message box based on the existence of the file.
Note that the Windows API functions used in this example have different names for 32-bit and 64-bit versions of Office/VBA. The code shown is suitable for 64-bit Office/VBA. If you're using 32-bit Office/VBA, remove the PtrSafe
keyword from the Declare
statements and use Long
instead of LongPtr
.
Please ensure that you have the necessary permissions to access the file, and adjust the filePath
variable in the TestFileExistsLongPath
subroutine with the actual file path you want to check.