Generating completed PDF forms using word docs and ms access

Question

I'm trying to use Microsoft Access to fill out word documents with bookmarked text form fields, and then export them as PDFs. I'm struggling to produce Visual Basic code in Access that works consistently. I continue to get errors about the word documents being locked from editing. Not sure how to proceed

Code I have so far

Public Sub ExportToMGR()
    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim rs As DAO.Recordset

    Set wApp = New Word.Application
    Set wDoc = wApp.Documents.Open("C:\filepath\doc.docx")
    Set rs = CurrentDb.OpenRecordset("Detail Report - Individuals")

    If Not rs.EOF Then rs.MoveFirst

    Do Until rs.EOF
        wDoc.Bookmarks("FullName1").Range.Text = Nz(rs!ClientName, "")
        wDoc.Bookmarks("FullName2").Range.Text = Nz(rs!ClientName, "")
        wDoc.SaveAs2 "C:\filepath\" & "firstTest.docx"
        rs.MoveNext
    Loop


End Sub

Answer 1

Welcome to SO.

You shouldnt be opening the Word document, instead you should create a Word Template (.dotx) and add it to the documents collection by calling the .Add() method.

Once the document is filled with data, you need to call the .ExportAsFixedFormat() method to save as PDF.

See an example below.

Option Explicit

Private Sub RunMailMerge_Click()
    On Error GoTo Trap

    Const TEMPLATE_PATH As String = "YourTemplateFolder\WordTemplate.dotx"

    Dim wApp As Word.Application
    Dim wDoc As Word.Document
    Dim rs As DAO.Recordset
    Dim idx As Long

    Set wApp = New Word.Application
    wApp.Visible = False

    Set rs = CurrentDb.OpenRecordset("Detail Report - Individuals")
    If rs.EOF Then GoTo Leave

    With rs
        .MoveLast
        .MoveFirst
    End With

    For idx = 1 To rs.RecordCount
        Set wDoc = wApp.Documents.Add(TEMPLATE_PATH)
        With wDoc
            .Bookmarks("FullName1").Range.Text = Nz(rs!ClientName, vbNullString)
            .Bookmarks("FullName2").Range.Text = Nz(rs!ClientName, vbNullString)
            .ExportAsFixedFormat "DocumentPathWithExtension.pdf", wdExportFormatPDF, False, wdExportOptimizeForOnScreen
            .Close wdDoNotSaveChanges
        End With
        Set wDoc = Nothing
        rs.MoveNext
    Next

Leave:
    On Error Resume Next
    If Not rs Is Nothing Then rs.Close
    If Not wDoc Is Nothing Then wDoc.Close wdDoNotSaveChanges
    If Not wApp Is Nothing Then wApp.Quit wdDoNotSaveChanges
    On Error GoTo 0
    Exit Sub

Trap:
    MsgBox Err.Description, vbCritical
    Resume Leave
End Sub