使用VBA删除SharePoint中PDF文件时Kill命令无法定位文件的问题咨询

报错原因 #

Kill 语句不支持SharePoint默认返回的HTTP/HTTPS格式路径，你通过ActiveWorkbook.FullName拿到的路径是https://开头的网络路径，Kill只能识别本地磁盘路径、或者映射为网络驱动器/UNC格式的SharePoint路径。

解决方法 #

1. 新增HTTP路径转UNC路径的自定义函数 #

将SharePoint的网络路径转换为VBA可识别的UNC路径，函数代码如下：

Function ConvertHTTPToUNC(ByVal HttpPath As String) As String
    ' 替换https前缀
    If LCase(Left(HttpPath, 8)) = "https://" Then
        HttpPath = Replace(Mid(HttpPath, 9), "/", "\")
        HttpPath = "\\" & HttpPath & "@SSL\DavWWWRoot\"
    ' 替换http前缀
    ElseIf LCase(Left(HttpPath, 7)) = "http://" Then
        HttpPath = Replace(Mid(HttpPath, 8), "/", "\")
        HttpPath = "\\" & HttpPath & "\DavWWWRoot\"
    End If
    ' 处理路径末尾多余的反斜杠
    If Right(HttpPath, 1) = "\" Then HttpPath = Left(HttpPath, Len(HttpPath) - 1)
    ConvertHTTPToUNC = HttpPath
End Function

2. 替换原代码的删除逻辑 #

原代码中的Kill PdfFile段替换为以下逻辑，增加文件存在校验、等待文件释放的逻辑，避免Outlook占用附件时删除失败：

' 转换PDF路径为UNC格式
Dim UncPdfPath As String
UncPdfPath = ConvertHTTPToUNC(PdfFile)

' 用FileSystemObject删除，兼容性更强
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

' 最多等待3秒，等待文件释放
Dim waitCount As Integer
waitCount = 0
Do Until waitCount > 3 Or Not fso.FileExists(UncPdfPath)
    DoEvents
    On Error Resume Next
    fso.DeleteFile UncPdfPath, Force:=True
    On Error GoTo 0
    waitCount = waitCount + 1
    Application.Wait (Now + TimeValue("0:00:01"))
Loop

' 可选：删除失败时给出提示
If fso.FileExists(UncPdfPath) Then
    MsgBox "PDF文件删除失败，请手动删除：" & vbCrLf & UncPdfPath, vbExclamation
End If

Set fso = Nothing

额外注意事项 #

• 确保你的设备已正常登录SharePoint，拥有对应路径的读写权限
• 如果你已经将SharePoint站点映射为本地网络驱动器，也可以直接将PdfFile的站点前缀替换为对应的驱动器号，比如把https://xxx.sharepoint.com/sites/xxx替换为Z:\即可直接用Kill删除

内容的提问来源于stack exchange，提问作者MBrann