Excel中使用VBA循环保存并合并PDF的宏调试求助

Hey Francis, let's work through this PDF generation and merging issue together. Since your existing macro isn't running as expected, I'll walk you through common pitfalls to check, provide a tested base script, and share debugging tips to get things working.

1. First, let's rule out common macro failure causes #

Here are the most frequent issues that break this kind of batch PDF workflow:

• Data sync issues: Your macro isn't properly updating the row in Info-Cas that Enveloppe depends on, leading to duplicate PDFs.
• Incorrect range/worksheet references: You might be missing the Enveloppe! prefix when targeting the A1:I23 range, or the range itself is misdefined.
• Missing library references: If you're trying to merge PDFs, you need to enable the Adobe Acrobat type library in the VBA editor (more on that later).
• Permission errors: The folder you're saving PDFs to doesn't have write access—always test with a local folder like C:\Temp\ first.
• Unoptimized Excel settings: Leaving screen updating or alerts on can slow down the macro or cause unexpected pop-ups that halt execution.

2. Tested VBA Macro for Batch PDF Generation & Merging #

This script handles the full workflow: updating dependent data, exporting individual PDFs, and merging them into one file. Before running, do these prep steps:

• Make sure your 144 rows of data in Info-Cas are continuous (assumed to start at row 2, with row 1 as headers).
• Create a dedicated folder for individual PDFs (e.g., C:\Temp\EnveloppePDFs\—it won't auto-create, so make it manually).
• If you want to merge PDFs, install Adobe Acrobat (not just Reader) and enable the Adobe Acrobat xx.x Type Library in the VBA editor (Tools > References).

Here's the code:

Sub BatchGenerateAndMergePDFs()
    Dim wsInfo As Worksheet, wsEnveloppe As Worksheet
    Dim lastRow As Long, i As Long
    Dim savePath As String, singlePDFPath As String
    Dim acroApp As Acrobat.CAcroApp, acroPDDoc As Acrobat.CAcroPDDoc
    Dim mergedPDDoc As Acrobat.CAcroPDDoc
    
    ' Link to your worksheets
    Set wsInfo = ThisWorkbook.Worksheets("Info-Cas")
    Set wsEnveloppe = ThisWorkbook.Worksheets("Enveloppe")
    
    ' Update this to your actual save folder (must exist!)
    savePath = "C:\Temp\EnveloppePDFs\"
    
    ' Speed up macro by disabling Excel's visual feedback
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' Get the last row of data in Info-Cas (adjust if your data starts elsewhere)
    lastRow = wsInfo.Cells(wsInfo.Rows.Count, "A").End(xlUp).Row
    
    ' Loop through each data row (144 rows = rows 2 to 145 if starting at row 2)
    For i = 2 To lastRow
        ' If your Enveloppe sheet doesn't auto-refresh when Info-Cas changes, uncomment this:
        ' wsEnveloppe.Calculate
        
        ' Name each PDF with a unique identifier (uses column A from Info-Cas)
        singlePDFPath = savePath & "Enveloppe_" & wsInfo.Cells(i, "A").Value & ".pdf"
        
        ' Export the specified range to PDF
        wsEnveloppe.Range("A1:I23").ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:=singlePDFPath, _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=False
    Next i
    
    ' ------------------- Merge PDFs Section -------------------
    ' Check if Adobe Acrobat is installed
    On Error Resume Next
    Set acroApp = CreateObject("AcroExch.App")
    On Error GoTo 0
    If acroApp Is Nothing Then
        MsgBox "Adobe Acrobat is required to merge PDFs. Skip merging or install Acrobat first.", vbExclamation
        GoTo Cleanup
    End If
    
    ' Create a new blank PDF for merging
    Set mergedPDDoc = CreateObject("AcroExch.PDDoc")
    mergedPDDoc.Create
    
    ' Merge each individual PDF into the master file
    For i = 2 To lastRow
        singlePDFPath = savePath & "Enveloppe_" & wsInfo.Cells(i, "A").Value & ".pdf"
        Set acroPDDoc = CreateObject("AcroExch.PDDoc")
        
        If acroPDDoc.Open(singlePDFPath) Then
            If mergedPDDoc.InsertPages(mergedPDDoc.GetNumPages - 1, acroPDDoc, 0, acroPDDoc.GetNumPages, False) Then
                acroPDDoc.Close ' Close the individual PDF after merging
            Else
                MsgBox "Failed to merge: " & singlePDFPath, vbCritical
            End If
        Else
            MsgBox "Couldn't open: " & singlePDFPath, vbCritical
        End If
    Next i
    
    ' Save the merged PDF
    mergedPDDoc.Save PDSaveFull, savePath & "Merged_Enveloppes.pdf"
    mergedPDDoc.Close
    acroApp.Exit
    
    MsgBox "Done! Check your PDFs in: " & savePath, vbInformation
    
Cleanup:
    ' Restore Excel's default settings
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    
    ' Clean up memory by releasing objects
    Set wsInfo = Nothing
    Set wsEnveloppe = Nothing
    Set acroApp = Nothing
    Set acroPDDoc = Nothing
    Set mergedPDDoc = Nothing
End Sub

Key Notes for This Script: #

• Replace savePath with your preferred folder path.
• If your data doesn't start at row 2 in Info-Cas, adjust the For i = 2 To lastRow line to match your starting row.
• If you don't need to merge PDFs, you can delete the entire "Merge PDFs Section"—the macro will still generate all individual PDFs.

3. Debugging Your Existing Macro #

If you want to fix your original code instead of using this one, try these steps:

1. Open the VBA editor (Alt + F11) and press F8 to run the macro line-by-line. This will show you exactly where it throws an error.
2. Check the ExportAsFixedFormat line: ensure you're specifying wsEnveloppe.Range("A1:I23") (not just Range("A1:I23"), which targets the active sheet).
3. Verify that the Filename path is valid—use a full path like C:\Temp\test.pdf instead of a relative path.
4. Make sure Enveloppe isn't hidden (hidden sheets can cause export issues in some Excel versions).

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