VBA导出PDF时出现Runtime error '5'无效过程调用或参数问题排查

我之前多次用下面的VBA代码成功将工作表导出为PDF，但4小时后代码突然失效。已经通过MsgBox "File: "确认文件路径没问题，附上代码，请问有没有遗漏的设置？

使用版本：Microsoft® Excel® for Microsoft 365 MSO (Version 2211 Build 16.0.15831.20220) 64-bit

Option Explicit

Sub ExportAsPDF()

Dim Folder_Path As String

Dim NameOfWorkbook

NameOfWorkbook = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))

With Application.FileDialog(msoFileDialogFolderPicker)
    .Title = "Select Folder path"
    
    If .Show = -1 Then Folder_Path = .SelectedItems(1)

End With

If Folder_Path = "" Then Exit Sub

Dim sh As Worksheet
Dim fn As String

For Each sh In ActiveWorkbook.Worksheets
    fn = Folder_Path & Application.PathSeparator & NameOfWorkbook & "_" & sh.Name & ".pdf"
    MsgBox "File: " & fn
    sh.PageSetup.PaperSize = xlPaperA4
    sh.PageSetup.LeftMargin = Application.InchesToPoints(0.5)
    sh.PageSetup.RightMargin = Application.InchesToPoints(0.5)
    sh.PageSetup.TopMargin = Application.InchesToPoints(0.5)
    sh.PageSetup.BottomMargin = Application.InchesToPoints(0.5)
    sh.PageSetup.HeaderMargin = Application.InchesToPoints(0.5)
    sh.PageSetup.FooterMargin = Application.InchesToPoints(0.5)
    sh.PageSetup.Orientation = xlPortrait
    sh.PageSetup.CenterHorizontally = True
    sh.PageSetup.CenterVertically = False
    sh.PageSetup.FitToPagesTall = 1
    sh.PageSetup.FitToPagesWide = 1
    sh.PageSetup.Zoom = False
    sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fn, Quality:=xlQualityStandard, OpenAfterPublish:=True

Next

MsgBox "Done"

End Sub

可能的问题排查方向 #

• 文件锁定/权限问题：导出的PDF文件可能被其他程序（比如PDF阅读器）占用，导致Excel无法写入。检查目标文件夹里的PDF是否处于打开状态，或者当前用户对目标文件夹是否有写入权限。
• 工作表状态问题：如果后续有工作表被设置保护，或者处于隐藏状态，ExportAsFixedFormat可能无法正常执行。可以在循环里添加判断：

  If sh.Visible = xlSheetVisible Then
      ' 执行导出代码
  End If
  

• 打印设置冲突：部分工作表可能存在打印区域设置冲突，或者FitToPagesTall/Wide的设置在某些工作表上不兼容。可以尝试先清除打印区域：

  sh.PageSetup.PrintArea = ""
  

• Excel进程异常：重启Excel，避免因长时间运行导致的内存泄漏或进程异常。
• 文件名特殊字符：工作表名称如果包含/ \ : * ? " < > |这类特殊字符，会导致PDF文件名无效。可以添加代码过滤特殊字符：

  Dim cleanSheetName As String
  cleanSheetName = Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(sh.Name, "/", ""), "\", ""), ":", ""), "*", ""), "?", ""), """", ""), "<", ""), ">", "")
  cleanSheetName = Replace(cleanSheetName, "|", "")
  fn = Folder_Path & Application.PathSeparator & NameOfWorkbook & "_" & cleanSheetName & ".pdf"
  

• 添加错误捕获：在代码里添加错误捕获，明确报错信息，方便定位问题：

  On Error Resume Next
  sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fn, Quality:=xlQualityStandard, OpenAfterPublish:=True
  If Err.Number <> 0 Then
      MsgBox "导出工作表 " & sh.Name & " 失败：" & Err.Description
      Err.Clear
  End If
  On Error GoTo 0
  

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