VBA导出工作表为PDF时遇运行时错误'5'，请求修复代码

问题根源 #

• 路径拼接使用了双反斜杠\\，导致生成的文件路径格式错误
• 未处理工作簿未保存的情况（ThisWorkbook.Path为空），会生成无效路径
• 仅替换了工作表名称中的空格，未处理其他Windows文件名非法字符（如/\:*?"<>|）

修复后的代码 #

Sub LoopSheetsSaveAsPDF()
    Dim ws As Worksheet
    Dim savePath As String
    Dim currentDate As String
    Dim pdfFileName As String
    Dim cleanSheetName As String
    
    ' 检查工作簿是否已保存
    If ThisWorkbook.Path = "" Then
        MsgBox "请先保存当前工作簿，再执行此宏！", vbExclamation
        Exit Sub
    End If
    
    savePath = ThisWorkbook.Path
    currentDate = Format(Date, "yymmdd")
    
    For Each ws In ThisWorkbook.Worksheets
        ' 清理工作表名称中的所有非法文件名字符
        cleanSheetName = Replace(ws.Name, " ", "_")
        cleanSheetName = Replace(cleanSheetName, "/", "_")
        cleanSheetName = Replace(cleanSheetName, "\", "_")
        cleanSheetName = Replace(cleanSheetName, ":", "_")
        cleanSheetName = Replace(cleanSheetName, "*", "_")
        cleanSheetName = Replace(cleanSheetName, "?", "_")
        cleanSheetName = Replace(cleanSheetName, """", "_")
        cleanSheetName = Replace(cleanSheetName, "<", "_")
        cleanSheetName = Replace(cleanSheetName, ">", "_")
        cleanSheetName = Replace(cleanSheetName, "|", "_")
        
        ' 拼接正确的文件路径（单个反斜杠）
        pdfFileName = savePath & "\" & cleanSheetName & "_" & currentDate & ".pdf"
        
        ' 导出为PDF
        ws.ExportAsFixedFormat Type:=xlTypePDF, _
                               Filename:=pdfFileName, _
                               Quality:=xlQualityStandard, _
                               IncludeDocProperties:=True, _
                               IgnorePrintAreas:=False
    Next ws
    
    MsgBox "所有工作表已成功导出为PDF！", vbInformation
End Sub

关键修复说明 #

• 增加工作簿保存状态检查：避免因未保存工作簿导致的空路径问题
• 修正路径拼接：将双反斜杠\\改为单个反斜杠\，确保路径格式合法
• 扩展非法字符处理：替换所有Windows系统不允许的文件名字符，避免生成无效文件名

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