将Excel工作表另存为PDF到SharePoint时出现运行时错误1004

问题概述 #

原工作簿中正常运行的VBA代码，复制到新工作簿仅修改SharePoint路径和文件名后，触发错误：Run-time error '1004' Document not saved. The document may be open or an error may have been encountered when saving

原代码：

Dim saveName As String

Dim mydir As String

Windows("Fluoride Report template.xlsx").Activate

saveName = Range("C6").Value & " " & Range("F14").Value

mydir = "https://fcx365.sharepoint.com/Sites/STO-Laboratory/General%20Documents/Flouride%20Reports/"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=mydir & saveName & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

排查与修复步骤 #

• 修正路径拼写错误
  注意到原模板文件名是Fluoride Report template.xlsx，但SharePoint路径中写的是Flouride%20Reports（多了一个字母l），这会导致目标路径不存在，直接触发保存失败。把路径中的Flouride改成Fluoride即可。

• 移除对窗口激活的依赖
  代码中Windows("Fluoride Report template.xlsx").Activate依赖特定文件处于打开状态，一旦文件名或状态变化就会出错。改用明确的工作表引用，避免激活操作：

  Dim ws As Worksheet
  Set ws = ThisWorkbook.Worksheets("报表工作表") ' 替换为实际工作表名称
  saveName = ws.Range("C6").Value & " " & ws.Range("F14").Value
  

• 清理文件名中的非法字符
  单元格C6和F14的内容可能包含文件系统禁止的字符（如/ \ : * ? " < > |），添加代码自动清理：

  Dim invalidChars As Variant
  invalidChars = Array("/", "\", ":", "*", "?", """", "<", ">", "|")
  Dim i As Integer
  For i = LBound(invalidChars) To UBound(invalidChars)
      saveName = Replace(saveName, invalidChars(i), "")
  Next i
  

• 改用本地同步路径替代HTTPS地址
  直接通过HTTPS保存到SharePoint可能存在权限或协议兼容性问题，使用OneDrive/SharePoint同步到本地的文件夹路径：

  mydir = "C:\Users\你的用户名\OneDrive - 公司名称\STO-Laboratory\General Documents\Fluoride Reports\"
  

• 添加错误捕获定位问题
  加入错误捕获代码，输出详细错误信息和目标路径，方便快速排查：

  On Error Resume Next
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=mydir & saveName & ".pdf", _
  Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
      :=False, OpenAfterPublish:=True
  If Err.Number <> 0 Then
      MsgBox "错误详情：" & Err.Description & vbCrLf & "目标路径：" & mydir & saveName & ".pdf"
      Err.Clear
  End If
  On Error GoTo 0
  

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