从Excel生成PDF并在XChange Viewer中打开：VBA代码技术问询

First, let’s break down the issues in your original code and provide a complete, working solution to meet your requirement:

Issues in the Original Code #

• Undeclared variables: wbA and strFile are used but not declared. Always use Option Explicit at the top of your module to catch these kinds of errors early.
• Incorrect worksheet reference: ActiveWorksheet is not a valid VBA property—use ActiveSheet instead.
• Unused variable: wsB is declared but never utilized, so it can be removed to clean up the code.
• Incomplete export statement: The ExportAsFixedFormat call is cut off, missing required parameters and the closing parenthesis.
• Missing launch logic: There’s no code to open the generated PDF in XChange Viewer after export.

Corrected Working Code #

Option Explicit

Sub ExportAndOpenPDF()
    Dim wbA As Workbook
    Dim wsA As Worksheet
    Dim strPath As String
    Dim strName As String
    Dim strPathFile As String
    Dim xChangeViewerPath As String
    
    ' Set references to active workbook and sheet
    Set wbA = ActiveWorkbook
    Set wsA = ActiveSheet
    
    ' Get desktop path, fall back to workbook path if desktop is unavailable
    strPath = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    If strPath = "" Then strPath = wbA.Path
    ' Ensure path ends with a backslash to avoid filename errors
    If Right(strPath, 1) <> "\" Then strPath = strPath & "\"
    
    ' Define PDF filename and full path
    strName = "My Document"
    strPathFile = strPath & strName & ".pdf"
    
    ' Export active sheet to PDF with standard settings
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strPathFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False ' Disable auto-open since we'll use XChange Viewer
    
    ' Define path to XChange Viewer (update this to match your installation)
    ' Common 64-bit path: "C:\Program Files\Tracker Software\PDF-XChange Viewer\PDFXCview.exe"
    ' Common 32-bit path: "C:\Program Files (x86)\Tracker Software\PDF-XChange Viewer\PDFXCview.exe"
    xChangeViewerPath = "C:\Program Files\Tracker Software\PDF-XChange Viewer\PDFXCview.exe"
    
    ' Check if the viewer exists at the specified path before launching
    If Dir(xChangeViewerPath) <> "" Then
        ' Launch XChange Viewer with the generated PDF
        Shell xChangeViewerPath & " """ & strPathFile & """", vbNormalFocus
    Else
        MsgBox "PDF-XChange Viewer not found at the specified path. Please update the path in the code.", vbExclamation
    End If
End Sub

Key Notes #

• Option Explicit: This forces you to declare all variables, preventing typos and unexpected behavior. Always add this at the top of your VBA module.
• XChange Viewer Path: Double-check and update xChangeViewerPath to match your actual installation location. You can find this path by right-clicking the XChange Viewer shortcut, selecting "Properties", and copying the "Target" value.
• Error Prevention: The code verifies the viewer exists before attempting to launch it, avoiding runtime errors if the path is incorrect.
• Export Customization: Adjust parameters like IgnorePrintAreas (set to True to export the entire sheet instead of the print area) or Quality (switch to xlQualityMinimum for smaller file sizes) as needed.

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