如何在VBA中模拟点击保存按钮，无需手动触发PDF保存？

Hey there! I see exactly what you're trying to do—right now your code just pops up the save dialog to capture the filename, but it doesn’t actually execute the save action. Let’s fix that by adding the PDF export logic immediately after you get the user’s chosen path.

Here’s the step-by-step solution: #

1. First, we need to handle the case where the user cancels the save dialog (since GetSaveAsFilename returns False if they hit Cancel, which would cause errors if we try to save to a non-existent path).
2. Use Excel’s built-in ExportAsFixedFormat method to automatically generate and save the PDF to the path the user selected.

Full Working Code Example: #

Sub AutoGenerateAndSavePDF()
    Dim ws As Worksheet
    ' Replace ActiveSheet with your specific worksheet if needed, e.g., ThisWorkbook.Worksheets("SalesReport")
    Set ws = ActiveSheet
    
    Dim Fname As Variant
    Dim FileFormatstr As String
    FileFormatstr = "PDF Files (*.pdf), *.pdf"
    
    ' Prompt user to select save location and filename
    Fname = Application.GetSaveAsFilename( _
        InitialFileName:=ws.Name & "_" & Range("M21") & "_" & VBA.Strings.Format(Now, "ddmmyy"), _
        filefilter:=FileFormatstr, _
        Title:="Create PDF")
    
    ' Check if user didn't cancel the dialog
    If Fname <> False Then
        ' Automatically export the worksheet to PDF
        ws.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=Fname, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False ' Set to True if you want the PDF to open after saving
        
        ' Optional: Show confirmation message to the user
        MsgBox "PDF saved successfully to:" & vbNewLine & Fname, vbInformation
    End If
End Sub

Key Details to Customize: #

• Cancel Handling: The If Fname <> False check ensures we don’t run save logic if the user closes the dialog without choosing a path.
• Export Scope: Replace ws.ExportAsFixedFormat with ThisWorkbook.ExportAsFixedFormat if you want to save the entire workbook as a PDF instead of just one worksheet.
• Post-Save Behavior: Set OpenAfterPublish:=True if you want the PDF to open automatically right after saving.
• Specific Ranges: If you only need to export a portion of the worksheet, set the sheet’s PrintArea property before exporting (e.g., ws.PageSetup.PrintArea = "A1:Z50"), or use the From/To parameters in ExportAsFixedFormat.

That’s all! This code will skip the manual save step and automatically generate the PDF as soon as the user selects a save location.

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