Is there a way for DIR(path) in VBA to handle strings longer than 260?

Question

Given the following snippet:

Dim s As String: s = "S:\vic\bla\[..insert more here..]\data.xml"
Debug.Print Len(s)
Debug.Print Dir(s)

If Len(s) >= 260 I receive an error stating the following:

Run-time error '53':

File not found

If the string is less than 260 it works fine and displays expected behavior for both found and non-found files.

Is there to get DIR working with long (>260) path names?

Notes

File restructure is not an option
I am running this in Excel 2007

as a short-term fix, you can re-map the deepest folder you need to get to the files — Joseph, Feb 06 '13 at 03:35
Why do you need Dir? You do not show any wildcards in your example, so is it possible that FileSystemObject would work for you? — Fionnuala, Feb 06 '13 at 12:17
@Remou Very good question. I only need to check that the file exists - so if I can do that with an alternative method, I'm open to it — Chris, Feb 06 '13 at 12:21
Based on your last comment, I have provided code that will tell you if a file exists regardless of depth in the directory structure. You might consider editing your question so it reflects what you meant to ask... that way, the answer will make more sense for people who don't read the comments. — Floris, Feb 06 '13 at 14:36

Mathieu Guindon · Answer 1 · 2013-02-06T04:23:12.650

Shortly put (to answer the answer as titled): No. VBA's Dir function simply does not work with paths beyond 260 characters.

Long version: http://msdn.microsoft.com/en-us/library/aa365247(VS.85).aspx#maximum_path_length (then Ctrl+F and search for "260")

Maximum Path Length Limitation

In the Windows API (with some exceptions discussed in the following paragraphs), the maximum length for a path is MAX_PATH, which is defined as 260 characters. A local path is structured in the following order: drive letter, colon, backslash, name components separated by backslashes, and a terminating null character. For example, the maximum path on drive D is "D:\some 256-character path string" where "" represents the invisible terminating null character for the current system codepage. (The characters < > are used here for visual clarity and cannot be part of a valid path string.) Note File I/O functions in the Windows API convert "/" to "\" as part of converting the name to an NT-style name, except when using the "\?\" prefix as detailed in the following sections. The Windows API has many functions that also have Unicode versions to permit an extended-length path for a maximum total path length of 32,767 characters. This type of path is composed of components separated by backslashes, each up to the value returned in the lpMaximumComponentLength parameter of the GetVolumeInformation function (this value is commonly 255 characters). To specify an extended-length path, use the "\?\" prefix. For example, "\?\D:\very long path". Note The maximum path of 32,767 characters is approximate, because the "\?\" prefix may be expanded to a longer string by the system at run time, and this expansion applies to the total length.

I think the section about Win32 File NameSpaces is worth giving a try:

For file I/O, the "\?\" prefix to a path string tells the Windows APIs to disable all string parsing and to send the string that follows it straight to the file system. For example, if the file system supports large paths and file names, you can exceed the MAX_PATH limits that are otherwise enforced by the Windows APIs. For more information about the normal maximum path limitation, see the previous section Maximum Path Length Limitation.

There must be a Win32 API function you can DECLARE and use, but that's not using the DIR function. Sorry, don't have a long path name at hand to test anything...

Floris · Accepted Answer · 2013-02-06T14:53:20.923

Here is some code that ought to work regardless of the depth... Basically, it specifies relative paths - so you never call dir with a long string

Function deepFileExists(longFileName As String)
' slowly make your way to the deepest folder...
' assuming "\" is used as separator
' you could add some code to replace "/" with "\"...

Dim pathFragment As String, currentDir As String
Dim slash As Integer, lastSlash As Integer

slash = InStr(1, longFileName, "\")
lastSlash = 0

pathFragment = Mid(longFileName, 1, slash - 1)

currentDir = CurDir        ' save the current directory
ChDrive pathFragment       ' making sure we have the right drive
ChDir pathFragment & "\"   ' be at the root of this drive's directory

lastSlash = slash
slash = InStr(slash + 1, longFileName, "\")

While (slash > 0)
  pathFragment = ".\" & Mid(longFileName, lastSlash + 1, slash - lastSlash)
  ChDir pathFragment
  'MsgBox "changing directory to " & pathFragment
  lastSlash = slash
  slash = InStr(slash + 1, longFileName, "\")
Wend

' now we can look for the file:
Dim a
a = Dir(Mid(longFileName, lastSlash + 1))
If Len(a) > 0 Then
  deepFileExists = True
Else
  deepFileExists = False
End If

End Function

Upvoted this answer before trying it... and it does not work in my case :( Error 76 "Path not found" on the `ChDir pathFragment` line when the (absolute) path crosses the 260-character threshold. — raph82, Jun 13 '13 at 09:10
@raph82 - that's strange... what platform are you using (version of OS, Office)? — Floris, Jun 13 '13 at 18:25

score 2 · Answer 3 · answered Feb 06 '13 at 12:58

I have no means of testing this, so all you have is a few rough notes on a possible approach.

''Reference: Windows Script Host Object Model
Dim fs As New FileSystemObject
Dim fl As Folder
Dim fl2 As Folder

Set fl = fs.GetFolder("Z:\Docs\test\ThisIsInOrderToCreate\ALongFilePath\")
Set fl2 = fl.SubFolders("WithASubFolder")
Debug.Print fl2.ShortPath
For Each File In fl2.Files
    If File.Name = "file.txt" Then
        Debug.Print "Found"
    End If
Next

''May be possible
a = Dir(fl.ShortPath & "\file.*")

Also, regarding comment above:

Set WshNetwork = CreateObject("WScript.Network")
WshNetwork.MapNetworkDrive "L:", "\\mydrive\share"
''Important to destroy when you are finished
Set WshNetwork = Nothing

score 0 · Answer 4 · answered Oct 09 '17 at 12:57

I found this MS page: Naming Files, Paths, and Namespaces

Maximum Path Length Limitation In the Windows API (with some exceptions discussed in the following paragraphs), the maximum length for a path is MAX_PATH, which is defined as 260 characters. A local path is structured in the following order: drive letter, colon, backslash, name components separated by backslashes, and a terminating null character. For example, the maximum path on drive D is "D:\some 256-character path string" where "" represents the invisible terminating null character for the current system codepage. (The characters < > are used here for visual clarity and cannot be part of a valid path string.) Note File I/O functions in the Windows API convert "/" to "\" as part of converting the name to an NT-style name, except when using the "\?\" prefix as detailed in the following sections.

The Windows API has many functions that also have Unicode versions to permit an extended-length path for a maximum total path length of 32,767 characters. This type of path is composed of components separated by backslashes, each up to the value returned in the lpMaximumComponentLength parameter of the GetVolumeInformation function (this value is commonly 255 characters). To specify an extended-length path, use the "\?\" prefix. For example, "\?\D:\very long path". Note The maximum path of 32,767 characters is approximate, because the "\?\" prefix may be expanded to a longer string by the system at run time, and this expansion applies to the total length.

The "\?\" prefix can also be used with paths constructed according to the universal naming convention (UNC). To specify such a path using UNC, use the "\?\UNC\" prefix. For example, "\?\UNC\server\share", where "server" is the name of the computer and "share" is the name of the shared folder. These prefixes are not used as part of the path itself. They indicate that the path should be passed to the system with minimal modification, which means that you cannot use forward slashes to represent path separators, or a period to represent the current directory, or double dots to represent the parent directory. Because you cannot use the "\?\" prefix with a relative path, relative paths are always limited to a total of MAX_PATH characters.

So for a very long UNC path I change the start of the path as shown below and it works.

   Const MAX_PATH_LENGTH As Integer = 260

    If Len(fname) > MAX_PATH_LENGTH Then
        fname = "\\?\UNC\" & Mid$(fname, 3)
    End If
    Set fsoObject = New Scripting.FileSystemObject
    FileExists = fsoObject.FileExists(fname)

score 0 · Answer 5 · answered Oct 18 '18 at 11:56

Since I couldn't post a reply to the comment that had the deepfileexists code, here is the code modified so that you can find network paths (since he replied he had a network location)

You need a function that calls system32 to do a direct path to a network drive. I got the code from here

heres the code, insert the private function at the top of the module or it wont work. it keeps the function tied specifically to that module, if you want to open it up to the whole workbook drop the private off.

Private Declare Function SetCurrentDirectoryA Lib "kernel32" _
    (ByVal lpPathName As String) As Long

then heres the function with the modified code to accept that for when there's a network drive starting with \

Function deepFileExists(longFileName As String)
' slowly make your way to the deepest folder...
' assuming "\" is used as separator
' you could add some code to replace "/" with "\"...


Dim pathFragment As String, currentDir As String
Dim slash As Integer, lastSlash As Integer

If Left(longFileName, 2) = "\\" Then
    slash = InStr(3, longFileName, "\")
    Else
    slash = InStr(1, longFileName, "\")
End If

lastSlash = 0

pathFragment = Mid(longFileName, 1, slash - 1)

currentDir = CurDir        ' save the current directory
If Not Left(pathFragment, 2) = "\\" Then
    ChDrive pathFragment       ' making sure we have the right drive
    ChDir pathFragment & "\"   ' be at the root of this drive's directory
    Else
        SetCurrentDirectoryA (pathFragment)
End If

lastSlash = slash
slash = InStr(slash + 1, longFileName, "\")

While (slash > 0)
    pathFragment = ".\" & Mid(longFileName, lastSlash + 1, slash - lastSlash)
    If Not Left(longFileName, 2) = "\\" Then
        ChDir pathFragment
    Else
        SetCurrentDirectoryA (pathFragment)
    End If
    'MsgBox "changing directory to " & pathFragment
    lastSlash = slash
    slash = InStr(slash + 1, longFileName, "\")
Wend

' now we can look for the file:
Dim a As String
Dim something As String
something = Mid(longFileName, lastSlash + 1)

a = Dir(something)
If Len(a) > 0 Then
    deepFileExists = True
Else
    deepFileExists = False
End If

End Function

Is there a way for DIR(path) in VBA to handle strings longer than 260?

5 Answers5

Linked

Related