windows： vba 遍历路径太长

在 VBA 中遍历目录时遇到路径太长的问题（通常超过 260 个字符），这是 Windows 系统的传统限制。

使用前缀 \?\

Sub EnumAllFiles(base_path, sh, last_row)
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim f As Object
    Set f = fso.GetFolder(base_path)
    Dim file_folder$
    path_1 = "\\?\" & sh.Range("C1").Value
    
    If f.Files.Count Then
        For Each ofile In f.Files
            With ofile
                file_folder = .ParentFolder
'                file_folder_path = Replace(file_folder, path_1, "\")
                file_folder_path = Mid(file_folder, Len(path_1) + 1)
                file_folder_link = Replace(file_folder, "\\?\", "")
                
                e_i = is_existed(file_folder_path, .Name, last_row)
                If e_i > 0 Then
                    sh.Range("D" & e_i) = .DateCreated
                    sh.Range("E" & e_i) = .DateLastAccessed
                    sh.Range("F" & e_i) = .DateLastModified
                Else
                    new_row = sh.[B665535].End(xlUp).Row + 1
                    sh.Range("A" & new_row) = "=row() - 8"
                    sh.Range("B" & new_row) = file_folder_path
                    sh.Range("C" & new_row) = .Name
                    sh.Range("D" & new_row) = .DateCreated
                    sh.Range("E" & new_row) = .DateLastAccessed
                    sh.Range("F" & new_row) = .DateLastModified
                    sh.Range("G" & new_row) = .Type
                    sh.Hyperlinks.Add Anchor:=sh.Range("B" & new_row), Address:=file_folder_link
                End If
'                sh.Range("C" & new_row) = .Path
'                sh.Range("D" & new_row) = .ShortPath
'                sh.Range("E" & new_row) = .ShortName
'                sh.Range("L" & new_row) = .Size
                
            End With
        Next
    End If
    
    Dim subfolders As Object
    Set subfolders = f.subfolders
    If subfolders.Count > 0 Then
        For Each tempFolder In subfolders
            subPath = tempFolder.Path
            Call EnumAllFiles(subPath, sh, last_row)
        Next
    End If
    Set f = Nothing
    Set fso = Nothing
    Set ofile = Nothing
    
End Sub

Function is_existed(file_path, file_name, last_row)
    Set sh_this = ThisWorkbook.Sheets("ODL")
    For j = 9 To last_row
        If sh_this.Range("B" & j) = file_path And sh_this.Range("C" & j) = file_name Then
            is_existed = j
            GoTo end_function
        End If
    Next
    is_existed = 0
end_function:
End Function

Sub main()
    Set sh = ThisWorkbook.Sheets("ODL")
    last_row = sh.[A65535].End(xlUp).Row
    base_path = sh.Range("C1").Value
'    base_path = "\\?\unc\" & base_path
    base_path = "\\?\" & base_path
    Call EnumAllFiles(base_path, sh, last_row)
    MsgBox ("success")
End Sub

\\?\UNC\ 和 \\?\ 的区别

• \\?\UNC\: 用于网络共享路径（UNC 路径），适用于通过网络访问的文件或文件夹（以 \服务器名\共享名 开头的路径）。
• \\?\: 用于本地文件系统的长路径，适用于直接挂载在本地的驱动器（如 C:、D: 等）。