使用VBA导出PDF时出现1004运行时错误，求排查解决

问题核心诱因 #

报错集中在ExportAsFixedFormat语句，结合代码和场景差异，主要问题点包括：路径拼写不一致、数值转字符串处理不彻底、隐式变量引发的逻辑混乱、打印区域设置冗余，或目标文件夹权限/存在性问题。

分步修复方案 #

1. 统一路径拼写，消除空格与下划线差异 #

代码中出现两种路径写法：

• P:\Public\Generated Letters\LTXN Export Spreadsheets\
• P:\Public\Generated_Letters\LTXN_Export_Spreadsheets\

空格与下划线的差异会导致系统无法定位文件夹，直接触发1004错误。统一使用下划线版本路径（避免空格带来的转义风险），建议用常量定义便于维护：

Const PDF_FOLDER As String = "P:\Public\Generated_Letters\LTXN_Export_Spreadsheets\"

2. 彻底处理数值型AccountNumber转字符串 #

若A1是数值型单元格，Right(A1,3)可能带隐形空格（数值默认右对齐，转字符串时保留空格），改用CStr()强制转换为纯字符串，同时显式引用单元格：

AccountNumber = Right(CStr(Range("A1").Value), 3)

3. 强制变量声明，避免隐式类型错误 #

代码中FullName未定义，会被当作变体类型引发不可预期问题。在代码开头添加Option Explicit强制变量声明，同时补全所有变量定义：

Option Explicit

Sub Create_PDF()
    Dim pdfName As String
    Dim myrange As String
    Dim AccountNumber As String
    Dim FullName As String ' 新增变量定义
    Const PDF_FOLDER As String = "P:\Public\Generated_Letters\LTXN_Export_Spreadsheets\"
    ' 后续代码...
End Sub

4. 简化文件名生成，降低特殊字符风险 #

Format(Now, "mm.dd.yyyy hh mm")中的空格无问题，但建议将小时改为连续格式hhmm减少文件名空格，同时提取文件名逻辑为单独块便于调试：

Dim baseFileName As String
baseFileName = "AccountEnding" & AccountNumber

If Dir(PDF_FOLDER & baseFileName & ".pdf") <> vbNullString Then
    FullName = PDF_FOLDER & baseFileName & " - " & Format(Now, "mm.dd.yyyy hhmm") & ".pdf"
Else
    FullName = PDF_FOLDER & baseFileName & ".pdf"
End If

5. 优化打印区域设置，避免冗余代码 #

重复执行myrange = Cells(Rows.Count, 6).End(xlUp).Address属于冗余操作，合并为一次并显式引用工作表（避免ActiveSheet切换导致的错误）：

Dim ws As Worksheet
Set ws = ActiveSheet ' 或指定具体工作表，如ThisWorkbook.Sheets("Sheet1")
myrange = ws.Cells(ws.Rows.Count, 6).End(xlUp).Address

With ws.PageSetup
    .PrintArea = "A1:" & myrange
    .Orientation = xlLandscape
    .Zoom = False
    .FitToPagesTall = False
    .FitToPagesWide = 1
End With

6. 提前检查文件夹存在性与权限 #

在代码开头添加文件夹检查，确保目标路径存在且当前用户有读写权限：

If Dir(PDF_FOLDER, vbDirectory) = "" Then
    MsgBox "目标文件夹不存在，请检查路径！"
    Exit Sub
End If

修复后的完整代码 #

Option Explicit

Sub Create_PDF()
    ' Create and save .pdf
    Dim pdfName As String
    Dim myrange As String
    Dim AccountNumber As String
    Dim FullName As String
    Dim ws As Worksheet
    Const PDF_FOLDER As String = "P:\Public\Generated_Letters\LTXN_Export_Spreadsheets\"
    
    ' 检查目标文件夹是否存在
    If Dir(PDF_FOLDER, vbDirectory) = "" Then
        MsgBox "目标文件夹不存在，请检查路径！"
        Exit Sub
    End If
    
    Set ws = ActiveSheet ' 可替换为具体工作表，如ThisWorkbook.Sheets("Sheet1")
    
    ' 正确转换AccountNumber为字符串
    AccountNumber = Right(CStr(ws.Range("A1").Value), 3)
    
    ' 生成基础文件名
    Dim baseFileName As String
    baseFileName = "AccountEnding" & AccountNumber
    
    ' 检查文件是否已存在
    If Dir(PDF_FOLDER & baseFileName & ".pdf") <> vbNullString Then
        FullName = PDF_FOLDER & baseFileName & " - " & Format(Now, "mm.dd.yyyy hhmm") & ".pdf"
    Else
        FullName = PDF_FOLDER & baseFileName & ".pdf"
    End If
    
    ' 设置打印区域
    myrange = ws.Cells(ws.Rows.Count, 6).End(xlUp).Address
    With ws.PageSetup
        .PrintArea = "A1:" & myrange
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesTall = False
        .FitToPagesWide = 1
    End With
    
    ' 导出PDF并检查结果
    On Error Resume Next
    ws.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=FullName, _
        Quality:=xlQualityMedium, _
        IncludeDocProperties:=False, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
    On Error GoTo 0
    
    If Dir(FullName) = vbNullString Then
        MsgBox "PDF导出失败，请检查路径、权限或打印区域设置！"
    End If
End Sub

Sub openFolder()
    'Open the folder that we save the PDF to
    Const PDF_FOLDER As String = "P:\Public\Generated_Letters\LTXN_Export_Spreadsheets\"
    If Dir(PDF_FOLDER, vbDirectory) <> "" Then
        Call Shell("explorer.exe " & PDF_FOLDER, vbNormalFocus)
    Else
        MsgBox "目标文件夹不存在！"
    End If
End Sub

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