vba - Excel VBA获取真实的文件夹路径长度(不包括波浪号)

Question

我有 VBA 代码，每 X 次我的客户端保存文件时创建一个备份文件(使用 .SaveCopyAs)。最近，一个客户遇到了最大文件夹和文件路径长度，似乎约为 220 个字符。我试图捕捉长文件名，但 Excel/Windows 正在用 ~ (波浪号)替换长文件夹名称，所以我无法获得真正的路径长度。

如何获取实际的文件夹/文件路径字符串长度并防止 Windows 使用“~”？

Sub Backup()
    Set awb = ActiveWorkbook

    BackupFolder = awb.Path & "\Backups"   
    BackupFileName = BackupFolder & "\" & awb.Name
    BackupFileName = BackupFileName & " " & Format(Now(), "mmddhhmm") & ".xlsm"

    'debug.print BackupFileName 
    'Result: D:\MF\DOCUME~1\LATEST~1\MASTER~1\SUPERL~1\SUPERL~1\Backups\TestLength-07021655.xlsm

    'debug.print Len(BackupFileName)
    'Result: 83 but the TRUE length is well over 300 characters

    PathLen = Len(BackupFileName) 'Result: 83
    If PathLen > 215 Then 'This obviously doesn't fire
        BackupFolder = GetDesktop & "BidListBackups"
        BackupFileName = BackupFolder & "\" & awb.Name
        BackupFileName = BackupFileName & " " & sType & Format(Now(), "mmddhhmm") & ".xlsm"
    End If

    With awb
        .SaveCopyAs BackupFileName
    End With

    If PathLen > 215 
        MsgBox "Backup file was saved to your desktop", vbokonly
    End If   
End Sub

Answer 1

见 this answer对于使用 GetLongPathName 的代码API。请注意，您需要从代码中显示的 165 增加缓冲区大小。