Excel VBA批量生成Word证书并导出PDF仅首个成功的问题求助

Let's break down why your code only saves the first PDF, then fix it step by step.

Key Issues in Your Original Code #

1. Broken Word Instance Management: You create a new Word instance every loop, but the close logic uses an undefined objWord variable—so the close code never runs. This leaves multiple hidden Word instances running, and ActiveDocument starts pointing to the wrong document after the first iteration.
2. Unstable ActiveDocument Dependence: Your Imprime_PDF function relies on ActiveDocument, which is risky when multiple Word documents/instances are open.
3. Inefficient Resource Handling: Creating and destroying Word instances every loop is slow and prone to memory leaks.

Fixed Full Code #

Sub GenerateCertificates()
    Dim i As Integer
    Dim participante As String
    Dim wrdApp As Object
    Dim wrdDoc As Object
    Dim savePath As String
    Dim templatePath As String
    
    ' Initialize counter (set to your starting row, e.g., i = 2 for header row 1)
    i = 2
    
    ' Precompute paths once to avoid repeated, error-prone concatenation
    savePath = ThisWorkbook.Path & "\" & _
               ThisWorkbook.Worksheets("Evento").Cells(1, 7).Text & " " & _
               ThisWorkbook.Worksheets("Evento").Cells(1, 2).Text & "\"
    templatePath = savePath & "Modelo.docx"
    
    ' Create Word instance ONCE outside the loop (faster, fewer errors)
    Set wrdApp = CreateObject("Word.Application")
    wrdApp.Visible = False ' Set to True if you need to debug the Word window
    
    On Error GoTo Cleanup ' Ensure Word is closed even if something breaks
    
    While ThisWorkbook.Sheets("Lista de Presença").Cells(i, 2) <> Empty
        If ThisWorkbook.Sheets("Lista de Presença").Cells(i, 4) = "Presente" Then
            participante = UCase(ThisWorkbook.Worksheets("Lista de Presença").Cells(i, 2))
            
            ' Open the template for the current participant
            Set wrdDoc = wrdApp.Documents.Open(templatePath)
            
            ' Replace #NOME with participant name (more reliable than manual selection)
            With wrdDoc.Content.Find
                .Text = "#NOME"
                .Replacement.Text = participante
                .Execute Replace:=wdReplaceAll
            End With
            
            ' Pass the specific document to the PDF function (no more guesswork with ActiveDocument)
            Imprime_PDF wrdDoc, participante, savePath
            
            ' Close the document without altering the original template
            wrdDoc.Close SaveChanges:=False
            Set wrdDoc = Nothing
        End If
        i = i + 1
    Wend
    
Cleanup:
    ' Clean up Word instance properly
    If Not wrdApp Is Nothing Then
        wrdApp.Quit SaveChanges:=False
        Set wrdApp = Nothing
    End If
    
    ' Show error message if something went wrong
    If Err.Number <> 0 Then
        MsgBox "An error occurred: " & Err.Description, vbExclamation
    End If
End Sub

Function Imprime_PDF(doc As Object, participante As String, savePath As String)
    Dim pdfFileName As String
    
    ' Build the final PDF filename
    pdfFileName = savePath & _
                  ThisWorkbook.Worksheets("Evento").Cells(1, 2).Text & _
                  " - Certificado de " & participante & ".pdf"
    
    ' Export the specific document to PDF
    doc.ExportAsFixedFormat _
        OutputFileName:=pdfFileName, _
        ExportFormat:=wdExportFormatPDF, _
        OpenAfterExport:=False, _
        OptimizeFor:=wdExportOptimizeForPrint, _
        Range:=wdExportAllDocument, _
        IncludeDocProps:=True, _
        CreateBookmarks:=wdExportCreateWordBookmarks, _
        BitmapMissingFonts:=True
End Function

What Changed & Why #

1. Single Word Instance: We create one Word instance before the loop instead of making a new one every time. This cuts down on overhead and eliminates conflicting instances.
2. Document-Specific PDF Export: The Imprime_PDF function now takes the target Word.Document as a parameter, so it always exports the correct document—no more relying on the unpredictable ActiveDocument.
3. Precomputed Paths: We build the save and template paths once at the start, reducing redundant code and potential typos.
4. Reliable Find/Replace: Using wrdDoc.Content.Find with Replace:=wdReplaceAll is more stable than manual selection (which can fail if the cursor is in the wrong place).
5. Error Handling: The Cleanup section ensures Word is closed even if an error occurs mid-loop, preventing hidden Word processes from lingering in your system.
6. Background Processing: Setting wrdApp.Visible = False runs Word in the background, making the process faster and less distracting. Flip it to True if you need to debug the Word window.

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