VBA保存文件至SharePoint报错：'_Workbook'的SaveAs方法失败

以下是针对该问题的排查方向和修复方案：

1. 修正文件名后缀拼写错误 #

代码中文件名后缀写成了.xsls，正确的Excel开放XML格式后缀是.xlsx，拼写错误会导致保存格式不匹配，触发报错：

fileName = "test" & ".xlsx" ' 修正为正确的.xlsx后缀

2. 确认SharePoint路径格式正确性 #

SaveAs方法不支持直接使用浏览器地址栏的SharePoint URL，必须使用WebDAV路径或者已映射的网络驱动器路径：

• WebDAV路径格式示例：\\your-external-sharepoint.com@SSL\DavWWWRoot\sites\TargetSite\Shared Documents\
• 也可以手动将SharePoint文档库映射为网络驱动器（比如Z:\），然后使用本地路径格式Z:\test.xlsx

3. 添加错误捕获定位具体问题 #

原代码的错误处理无法定位具体故障点，建议在SaveAs前后添加错误捕获，获取错误编号和描述：

On Error Resume Next
newBook.SaveAs sharePointFilePath, FileFormat:=xlOpenXMLWorkbook
If Err.Number <> 0 Then
    MsgBox "错误编号: " & Err.Number & vbCrLf & "错误描述: " & Err.Description
    Err.Clear
    newBook.Close SaveChanges:=False
    Exit Sub
End If
On Error GoTo 0

4. 确保权限凭据有效 #

外部组织的SharePoint可能需要重新验证身份：

• 先手动打开目标SharePoint文档库，确认能正常上传文件、创建文件夹，确保当前用户的登录状态有效
• 如果是MFA（多因素认证）环境，VBA可能无法自动获取凭据，需要使用SharePoint应用密码或者通过Power Automate中转上传

修正后的完整代码示例 #

Sub ExportSheetToSharePoint()
    Dim wsExport As Worksheet
    Dim newBook As Workbook
    Dim fileName As String
    Dim sharePointURL As String
    Dim sharePointFilePath As String
    Dim wasVeryHidden As Boolean
    Dim ws As Worksheet

    ' 替换为你的SharePoint WebDAV路径
    sharePointURL = "\\your-external-sharepoint.com@SSL\DavWWWRoot\sites\TargetSite\Shared Documents\"
    
    On Error Resume Next
    Set wsExport = ThisWorkbook.Sheets("export")
    On Error GoTo 0
    
    If wsExport Is Nothing Then
        MsgBox "找不到名为export的工作表"
        Exit Sub
    End If
    
    wasVeryHidden = (wsExport.Visible = xlSheetVeryHidden)
    If wasVeryHidden Then
        wsExport.Visible = xlSheetVisible
    End If
    
    ' 修正文件名后缀
    fileName = "test" & ".xlsx"
    
    Set newBook = Workbooks.Add
    wsExport.Copy Before:=newBook.Sheets(1)
    
    If wasVeryHidden Then
        wsExport.Visible = xlSheetVeryHidden
    End If

    Application.DisplayAlerts = False
    For Each ws In newBook.Worksheets
        If ws.Name <> "export" Then
            ws.Delete
        End If
    Next ws
    Application.DisplayAlerts = True

    sharePointFilePath = sharePointURL & fileName
    
    ' 添加错误捕获
    On Error Resume Next
    newBook.SaveAs sharePointFilePath, FileFormat:=xlOpenXMLWorkbook
    If Err.Number <> 0 Then
        MsgBox "保存失败: " & vbCrLf & "错误编号: " & Err.Number & vbCrLf & "错误描述: " & Err.Description
        newBook.Close SaveChanges:=False
        Exit Sub
    End If
    On Error GoTo 0
    
    newBook.Close SaveChanges:=False
    MsgBox "Export complete: " & sharePointFilePath
    
    ' 清理对象
    Set newBook = Nothing
    Set wsExport = Nothing
End Sub

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