Automate PDF generation using mail merge with a LibreOffice Base database and Writer templates

[sbi-tech]

I’m working on a macro to automate PDF generation using mail merge with a LibreOffice Base database and Writer templates. The goal is to:

1. Select a template (e.g., “Test Template Option 1”).
2. Pull data from a Base table for a specific record.
3. Generate a PDF with merged fields (e.g., <NAME2>, <EMAIL2>, <DATE>).

The Problem

While the macro runs without errors, the generated PDF either:

• Shows raw field placeholders (e.g., <NAME2>) instead of actual data.
• Fails to insert the current date into the DATE_REPORTED field.

What I’ve Tried

1. Verified Database/Field Names:

   • Confirmed the table name (clients_table) and column names (Record, NAME2, etc.) match the template placeholders.
   • Example: <NAME2> in the template ↔ NAME2 in the database.

2. Added Explicit Field Refresh:

   • Used oDoc.refresh() after loading the template to activate database links.

3. Manual Testing:

   • The mail merge works manually (via File > Print > Mail Merge), but the macro doesn’t replicate this.

Sub MergeSelectedTemplateWithRecord(oEvent As Object)
    On Error GoTo ErrorHandler

    ' === GET DIALOG INSTANCE ===
    Dim oDialog As Object : oDialog = oEvent.Source.getContext()
    Dim sRecordID As String : sRecordID = Trim(oDialog.getControl("txt_SingleRecord").Text)
    Dim sTemplateName As String : sTemplateName = Trim(oDialog.getControl("cmb_TemplateSelect").Text)

    ' === INPUT VALIDATION ===
    If sRecordID = "" Then
        MsgBox "Please enter a record ID.", 48, "Missing Input"
        Exit Sub
    End If
    
    If sTemplateName = "" Then
        MsgBox "Please select a template.", 48, "Missing Template"
        Exit Sub
    End If

    ' === DATABASE CONNECTION ===
    Dim oConn As Object : oConn = ThisDatabaseDocument.CurrentController.ActiveConnection
    Dim oCheck As Object
    oCheck = oConn.prepareStatement("SELECT * FROM ""clients_table"" WHERE ""Record"" = ?") ' Updated table name
    oCheck.setString(1, sRecordID)
    Dim oResult As Object : oResult = oCheck.executeQuery()
    If Not oResult.Next() Then
        MsgBox "Record not found: " & sRecordID, 48, "Not Found"
        Exit Sub
    End If

    ' === PATH CONFIGURATION ===
    Dim sBasePath As String : sBasePath = ConvertFromURL(GetParentFolder(ThisDatabaseDocument.URL))
    Dim sTemplatePath As String : sTemplatePath = ConvertToURL(sBasePath & GetPathSeparator() & "Templates" & GetPathSeparator() & sTemplateName & ".odt")
    Dim sTempFolder As String : sTempFolder = sBasePath & GetPathSeparator() & "temp"
    Dim sReportsPath As String : sReportsPath = sBasePath & GetPathSeparator() & "2025REPORTS"

    ' === FOLDER CREATION ===
    Dim oFS As Object : oFS = CreateUnoService("com.sun.star.ucb.SimpleFileAccess")
    If Not oFS.exists(ConvertToURL(sTempFolder)) Then oFS.createFolder(ConvertToURL(sTempFolder))
    If Not oFS.exists(ConvertToURL(sReportsPath)) Then oFS.createFolder(ConvertToURL(sReportsPath))

    ' === TEMPLATE HANDLING ===
    Dim oDesktop As Object : oDesktop = CreateUnoService("com.sun.star.frame.Desktop")
    Dim oProps(1) As New com.sun.star.beans.PropertyValue
    oProps(0).Name = "Hidden" : oProps(0).Value = False
    oProps(1).Name = "AsTemplate" : oProps(1).Value = False

    Dim oDoc As Object : oDoc = oDesktop.loadComponentFromURL(sTemplatePath, "_blank", 0, oProps())
    If IsNull(oDoc) Then MsgBox "Failed to open template.": Exit Sub
    oDoc.refresh() ' Force field refresh

    ' === TEMPORARY FILE CREATION ===
    Dim sTempPath As String : sTempPath = ConvertToURL(sTempFolder & GetPathSeparator() & "~merge_temp.odt")
    oDoc.storeToURL(sTempPath, Array())
    oDoc.close(True)

    ' === MAIL MERGE EXECUTION ===
    Dim oMailMerge As Object : oMailMerge = CreateUnoService("com.sun.star.text.MailMerge")
    oMailMerge.DocumentURL = sTempPath
    oMailMerge.DataSourceName = ThisDatabaseDocument.DataSource.Name
    oMailMerge.CommandType = com.sun.star.sdb.CommandType.COMMAND
    oMailMerge.Command = "SELECT * FROM ""clients_table"" WHERE ""Record"" = '" & sRecordID & "'" ' Updated table name
    oMailMerge.OutputType = com.sun.star.text.MailMergeType.FILE
    oMailMerge.OutputURL = ConvertToURL(sReportsPath)
    oMailMerge.SaveAsSingleFile = False
    oMailMerge.FileNamePrefix = "Merged_" & sRecordID
    oMailMerge.execute(Array())

    ' === FIND GENERATED FILE ===
    Dim aFiles(), sODT As String, i As Integer
    aFiles = oFS.getFolderContents(ConvertToURL(sReportsPath), False)
    For i = LBound(aFiles) To UBound(aFiles)
        If Right(UCase(aFiles(i)), 4) = ".ODT" And InStr(aFiles(i), "Merged_" & sRecordID) > 0 Then
            sODT = aFiles(i)
            Exit For
        End If
    Next

    If sODT = "" Then
        MsgBox "Mail merge failed to generate output.", 16, "Error"
        Exit Sub
    End If

    ' === INSERT CURRENT DATE ===
    Dim oMergedDoc As Object : oMergedDoc = oDesktop.loadComponentFromURL(sODT, "_blank", 0, Array())
    Dim sCurrentDate As String : sCurrentDate = Format(Now(), "MM/DD/YYYY")
    Dim oTextFields As Object : oTextFields = oMergedDoc.TextFields
    Dim oField As Object
    For Each oField In oTextFields
        If oField.getPropertyValue("Content") = "<DATE>" Then
            oField.setPropertyValue("Content", sCurrentDate)
        End If
    Next

    ' === EXPORT TO PDF ===
    Dim sPDF As String : sPDF = Left(sODT, Len(sODT) - 4) & ".pdf"
    Dim oExport(0) As New com.sun.star.beans.PropertyValue
    oExport(0).Name = "FilterName" : oExport(0).Value = "writer_pdf_Export"
    oMergedDoc.storeToURL(sPDF, oExport())
    oMergedDoc.close(True)

    ' === CLEANUP ===
    If oFS.exists(sODT) Then oFS.kill(sODT)
    MsgBox "PDF created successfully: " & ConvertFromURL(sPDF), 64, "Done"
    Exit Sub

ErrorHandler:
    MsgBox "Error [" & Err & "] at step: " & Erl() & Chr(10) & Error$, 16, "Unexpected Error"
End Sub