运行SaveAsPDF VBA宏触发1004错误：ExportAsFixedFormat方法失败求解决

报错代码 #

Sub SaveAsPDF()
    Dim ws As Worksheet
    Dim filePath As String
    Dim fileName As String
    Dim printArea As String
    Dim pdfPath As String

    ' Set the worksheet you are working with
    Set ws = ThisWorkbook.Sheets("Invoice format") ' Change "Sheet1" to your sheet name

    ' Define the file path and name for the PDF
    filePath = ThisWorkbook.Path
    fileName = Range("V13").Value + ".pdf" ' Change this to your desired file name
    pdfPath = filePath & "\" & fileName

    ' Define the print area
    printArea = "A1:T51" ' Change this to your desired print area
    ws.PageSetup.printArea = printArea

    ' Export the sheet as a PDF
    ws.ExportAsFixedFormat Type:=xlTypePDF, fileName:=pdfPath, Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False

    ' Optional: Display a confirmation message
    MsgBox "PDF saved as: " & fileName & vbCrLf & _
           "Print Area: " & printArea & vbCrLf & _
           "File Path: " & pdfPath
End Sub

报错信息 #

运行时错误'1004'：
_worksheet对象的'ExportAsFixedFormat'方法失败

解决方案 #

• 检查文件路径与文件名合法性

  • 当工作簿未保存时，ThisWorkbook.Path会返回空值，导致生成的pdfPath无效。需先判断工作簿是否已保存：

    If ThisWorkbook.Path = "" Then
        MsgBox "请先保存当前工作簿！"
        Exit Sub
    End If
    

  • Range("V13").Value可能包含Windows文件名非法字符（\ / : * ? " < > |），需过滤后再使用：

    Dim invalidChars As Variant
    invalidChars = Array("\", "/", ":", "*", "?", """", "<", ">", "|")
    fileName = ws.Range("V13").Value ' 明确指定工作表，避免引用错误
    For Each char In invalidChars
        fileName = Replace(fileName, char, "")
    Next
    fileName = fileName & ".pdf"
    

  • 注意Range("V13")需指定所属工作表，否则可能引用当前激活工作表而非目标工作表，修改为ws.Range("V13").Value

• 验证打印区域有效性

  • 确认A1:T51是Invoice format工作表内的合法区域，未超出工作表实际单元格范围
  • 可先清除原有打印区域再重新设置，避免旧设置冲突：

    ws.PageSetup.PrintArea = ""
    ws.PageSetup.PrintArea = printArea
    

• 排查权限与文件占用问题

  • 确保pdfPath指向的文件夹有写入权限，不要尝试写入系统保护目录（如C:\根目录、Program Files）
  • 检查目标PDF文件是否被其他程序（如Adobe Reader、浏览器）占用，关闭后再运行宏

• 修复Office组件

  • 打开控制面板→程序和功能→找到Microsoft Office→右键选择「更改」→执行「快速修复」或「联机修复」
  • 禁用可能冲突的Excel加载项：文件→选项→加载项→点击「转到」（COM加载项）→取消勾选非必要加载项后重启Excel

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