如何修改VBA代码将Excel工作簿备份保存至SharePoint？

修改后的完整代码 #

Sub Backup_Save()
    Dim wb As Workbook
    Dim folderPath As String
    Dim fileName As String
    Set wb = ActiveWorkbook
    
    ' 替换为你的Teams关联SharePoint文档库的实际路径
    folderPath = "https://yourcompany.sharepoint.com/sites/YourTeamName/Shared%20Documents/Art/Art%20A/"
    
    With wb.Sheets("Vessel Schedule")
        fileName = .Range("F2").Value & " - " & _
                   .Range("P1").Value & " - " & _
                   Format(Now(), "mm-dd hhmm ss") & ".xlsm"
    End With
    
    ' 过滤SharePoint不允许的文件名非法字符
    Dim invalidChars As Variant
    invalidChars = Array("/", "\", ":", "*", "?", """", "<", ">", "|")
    Dim char As Variant
    For Each char In invalidChars
        fileName = Replace(fileName, char, "-")
    Next char
    
    On Error Resume Next
    wb.SaveCopyAs fileName:=folderPath & fileName
    If Err.Number <> 0 Then
        MsgBox "备份失败：" & Err.Description, vbCritical
    Else
        MsgBox "Workbook saved successfully!"
    End If
    On Error GoTo 0
End Sub

关键修改说明 #

1. 替换SharePoint路径

   • 获取正确路径方法：在Teams中打开目标文件夹，点击顶部「打开在SharePoint」，复制浏览器地址栏中文件夹层级的路径（如https://yourcompany.sharepoint.com/sites/YourTeamName/Shared Documents/Art/Art A），将空格替换为%20或直接保留空格，最后添加斜杠/。
   • 确保你对该SharePoint文件夹拥有读写权限。

2. 新增非法字符过滤

   • SharePoint文件名不允许包含/ \ : * ? " < > |这些字符，代码中添加了自动替换逻辑，避免因单元格内容包含非法字符导致保存失败。

3. 错误处理优化

   • 添加错误捕获，保存失败时会弹出具体错误信息，方便排查权限不足、路径错误等问题。

4. 保留原有命名规则

   • 完全保留原代码中基于F2、P1单元格值和当前时间的文件名生成逻辑。

备选方案（映射网络驱动器） #

如果直接使用HTTPS路径报错，可将SharePoint文件夹映射为本地网络驱动器（如Z:\），然后修改folderPath为映射路径：

folderPath = "Z:\Art\Art A\"

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