VBA运行时错误'52'：SharePoint路径适配问题求助

问题根源 #

VBA的Dir函数仅支持本地文件路径或SharePoint的UNC映射路径，无法识别HTTPS格式的SharePoint链接，所以当你传入https://xxx.sharepoint.com/...这类路径时，会触发「运行时错误'52'：无效的文件名或文件号」。

解决方案 #

以下是三种可行的适配方案，可根据你的场景选择：

方案1：转换为SharePoint UNC路径，用FileSystemObject检查

将HTTPS链接转换为SharePoint的UNC格式（\\xxx.sharepoint.com@SSL\sites\...），再通过FileSystemObject验证文件存在性。

修改后的代码：

' 先修正路径拼接逻辑（兼容本地/SharePoint路径）
Dim pathSeparator As String
If InStr(ActiveWorkbook.Path, "https://") > 0 Or InStr(ActiveWorkbook.Path, "http://") > 0 Then
    pathSeparator = "/"
Else
    pathSeparator = "\"
End If
flnameI = ActiveWorkbook.Path & pathSeparator & folder & pathSeparator & txtAgentFile.Value

' 转换SharePoint链接为UNC格式并检查文件
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

If InStr(flnameI, "https://") > 0 Then
    flnameI = Replace(flnameI, "https://", "\\")
    flnameI = Replace(flnameI, "/", "\")
    flnameI = Replace(flnameI, ".sharepoint.com", ".sharepoint.com@SSL")
End If

If Not fso.FileExists(flnameI) Then
    MsgBox "FATAL ERROR - file not found - " & flnameI
    End
End If
Set fso = Nothing

方案2：通过尝试打开工作簿验证（最通用）

直接尝试以只读模式打开目标文件，捕获错误判断文件是否存在，无需路径转换，兼容本地和SharePoint链接。

修改后的代码：

' 先修正路径拼接逻辑（兼容本地/SharePoint路径）
Dim pathSeparator As String
If InStr(ActiveWorkbook.Path, "https://") > 0 Or InStr(ActiveWorkbook.Path, "http://") > 0 Then
    pathSeparator = "/"
Else
    pathSeparator = "\"
End If
flnameI = ActiveWorkbook.Path & pathSeparator & folder & pathSeparator & txtAgentFile.Value

' 尝试打开文件验证存在性
Dim testWb As Workbook
On Error Resume Next
Set testWb = Workbooks.Open(flnameI, ReadOnly:=True, UpdateLinks:=False)
On Error GoTo 0

If testWb Is Nothing Then
    MsgBox "FATAL ERROR - file not found - " & flnameI
    End
Else
    testWb.Close SaveChanges:=False
End If
Set testWb = Nothing

方案3：用HTTP HEAD请求验证（无需打开文件）

通过发送HEAD请求检查文件的HTTP状态码，200表示文件存在，适合仅需验证存在性、不想打开文件的场景。

修改后的代码：

' 先修正路径拼接逻辑（兼容本地/SharePoint路径）
Dim pathSeparator As String
If InStr(ActiveWorkbook.Path, "https://") > 0 Or InStr(ActiveWorkbook.Path, "http://") > 0 Then
    pathSeparator = "/"
Else
    pathSeparator = "\"
End If
flnameI = ActiveWorkbook.Path & pathSeparator & folder & pathSeparator & txtAgentFile.Value

' 发送HEAD请求检查文件
If InStr(flnameI, "https://") > 0 Or InStr(flnameI, "http://") > 0 Then
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    
    xmlHttp.Open "HEAD", flnameI, False
    xmlHttp.Send
    
    If xmlHttp.Status <> 200 Then
        MsgBox "FATAL ERROR - file not found - " & flnameI
        End
    End If
    Set xmlHttp = Nothing
Else
    ' 本地路径仍用Dir函数判断
    If Len(Dir(flnameI)) = 0 Then
        MsgBox "FATAL ERROR - file not found - " & flnameI
        End
    End If
End If

额外说明 #

• 原代码中用+拼接字符串容易出现类型错误，VBA中字符串拼接推荐用&。
• 路径拼接时要注意区分本地路径的\和SharePoint路径的/，上述代码已做兼容处理。

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