VBA 中的 DIR(path) 有没有办法处理长度超过 260 的字符串？

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

提问by Chris

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) >= 260I receive an error stating the following:

如果Len(s) >= 260我收到说明以下内容的错误：

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.

如果字符串小于 260，它可以正常工作并显示已找到和未找到文件的预期行为。

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

是否可以让 DIR 使用长（> 260）路径名？

Notes

笔记

• File restructure is not an option

• I am running this in Excel 2007

• 文件重组不是一种选择

• 我在 Excel 2007 中运行这个

采纳答案by Floris

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

这是一些无论深度如何都应该工作的代码......基本上，它指定了相对路径 - 所以你永远不会dir用长字符串调用

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

回答by Mathieu Guindon

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

Dir简而言之（回答标题中的答案）：不。VBA 的功能根本不适用于超过 260 个字符的路径。

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

长版：http: //msdn.microsoft.com/en-us/library/aa365247( VS.85).aspx#maximum_path_length（然后 Ctrl+F 并搜索“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.

最大路径长度限制

在 Windows API 中（以下段落中讨论的一些例外情况除外），路径的最大长度为 MAX_PATH，定义为 260 个字符。本地路径按以下顺序构造：驱动器号、冒号、反斜杠、由反斜杠分隔的名称组件和终止空字符。例如，驱动器 D 上的最大路径是“D:\some 256-character path string”，其中“”表示当前系统代码页的不可见终止空字符。（此处使用字符 < > 是为了清晰起见，不能是有效路径字符串的一部分。） 注意 Windows API 中的文件 I/O 函数将“/”转换为“\”作为将名称转换为 NT-样式名称，除非使用“\?\”前缀，如以下部分所述。Windows API 有许多函数，这些函数也有 Unicode 版本，以允许最大总路径长度为 32,767 个字符的扩展长度路径。此类路径由以反斜杠分隔的组件组成，每个组件最多为 GetVolumeInformation 函数的 lpMaximumComponentLength 参数中返回的值（该值通常为 255 个字符）。要指定扩展长度的路径，请使用“\?\”前缀。例如，“\?\D:\非常长的路径”。注意 32,767 个字符的最大路径是近似值，因为“\?\”前缀可能会在运行时被系统扩展为更长的字符串，并且这种扩展适用于总长度。767 个字符。此类路径由以反斜杠分隔的组件组成，每个组件最多为 GetVolumeInformation 函数的 lpMaximumComponentLength 参数中返回的值（该值通常为 255 个字符）。要指定扩展长度的路径，请使用“\?\”前缀。例如，“\?\D:\非常长的路径”。注意 32,767 个字符的最大路径是近似值，因为“\?\”前缀可能会在运行时被系统扩展为更长的字符串，并且这种扩展适用于总长度。767 个字符。此类路径由以反斜杠分隔的组件组成，每个组件最多为 GetVolumeInformation 函数的 lpMaximumComponentLength 参数中返回的值（该值通常为 255 个字符）。要指定扩展长度的路径，请使用“\?\”前缀。例如，“\?\D:\非常长的路径”。注意 32,767 个字符的最大路径是近似值，因为“\?\”前缀可能会在运行时被系统扩展为更长的字符串，并且这种扩展适用于总长度。

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

我认为关于Win32 File NameSpaces 的部分值得一试：

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.

对于文件 I/O，路径字符串的“\?\”前缀告诉 Windows API 禁用所有字符串解析并将其后的字符串直接发送到文件系统。例如，如果文件系统支持大路径和文件名，您可以超出 Windows API 强制执行的 MAX_PATH 限制。有关正常最大路径限制的更多信息，请参阅上一节最大路径长度限制。

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

必须有一个您可以DECLARE使用的 Win32 API 函数，但这并没有使用该DIR函数。抱歉，手头没有很长的路径名来测试任何东西......

回答by Fionnuala

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

回答by ChipsLetten

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

我找到了这个 MS 页面： 命名文件、路径和命名空间

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.

最大路径长度限制 在 Windows API 中（除了以下段落中讨论的一些例外），路径的最大长度是 MAX_PATH，它被定义为 260 个字符。本地路径按以下顺序构造：驱动器号、冒号、反斜杠、由反斜杠分隔的名称组件和终止空字符。例如，驱动器 D 上的最大路径是“D:\some 256-character path string”，其中“”表示当前系统代码页的不可见终止空字符。（此处使用字符 < > 是为了清晰起见，不能是有效路径字符串的一部分。） 注意 Windows API 中的文件 I/O 函数将“/”转换为“\”作为将名称转换为 NT-样式名称，除非使用“\?\”

Windows API 有许多函数，这些函数也有 Unicode 版本，以允许最大总路径长度为 32,767 个字符的扩展长度路径。此类路径由以反斜杠分隔的组件组成，每个组件最多为 GetVolumeInformation 函数的 lpMaximumComponentLength 参数中返回的值（该值通常为 255 个字符）。要指定扩展长度的路径，请使用“\?\”前缀。例如，“\?\D:\非常长的路径”。注意 32,767 个字符的最大路径是近似值，因为“\?\”前缀可能会在运行时被系统扩展为更长的字符串，并且这种扩展适用于总长度。

“\?\”前缀也可用于根据通用命名约定 (UNC) 构造的路径。要使用 UNC 指定这样的路径，请使用“\?\UNC\”前缀。例如，“\?\UNC\server\share”，其中“server”是计算机的名称，“share”是共享文件夹的名称。这些前缀不用作路径本身的一部分。它们表示路径应该以最少的修改传递给系统，这意味着您不能使用正斜杠表示路径分隔符，或使用句点表示当前目录，或使用双点表示父目录。因为不能在相对路径中使用“\?\”前缀，所以相对路径总是限制为总共 MAX_PATH 个字符。

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

因此，对于很长的 UNC 路径，我更改了路径的开头，如下所示，并且可以正常工作。

   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)

回答by enthuse

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)

由于我无法回复包含 deepfileexists 代码的评论，这里是修改后的代码，以便您可以找到网络路径（因为他回答说他有网络位置）

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

您需要一个调用 system32 的函数来执行到网络驱动器的直接路径。我从这里得到了代码

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