Delete line during mail merge if value is 0 or blank (or NULL)-CodePudding

I am needing help on adding to a macro that I found online that automates the process of generating PDFs from a mail merge. The current macro takes a mail merge and automatically generates individual PDFs as a result from the mail merge. See below:

Sub MailMergeToPdfBasic()                                                        ' Mark the start of the Subroutine (i.e. Macro) and name it "MailMergeToPdf"
' Macro created by Imnoss Ltd
' Please share freely while retaining attribution
' Last Updated 2021-05-03
    Dim masterDoc As Document, singleDoc As Document, lastRecordNum As Long   ' Create variables ("Post-it Notes") for later use
    Set masterDoc = ActiveDocument                                               ' Identify the ActiveDocument (foremost doc when Macro run) as "masterDoc"

    masterDoc.MailMerge.DataSource.ActiveRecord = wdLastRecord                   ' jump to the last active record (active = ticked in edit recipients)
    lastRecordNum = masterDoc.MailMerge.DataSource.ActiveRecord                  ' retrieve the record number of the last active record so we know when to stop

    masterDoc.MailMerge.DataSource.ActiveRecord = wdFirstRecord                  ' jump to the first active record (active = ticked in edit recipients)
    Do While lastRecordNum > 0                                                   ' create a loop, lastRecordNum is used to end the loop by setting to zero (see below)
        masterDoc.MailMerge.Destination = wdSendToNewDocument                    ' Identify that we are creating a word docx (and no e.g. an email)
        masterDoc.MailMerge.DataSource.FirstRecord = masterDoc.MailMerge.DataSource.ActiveRecord              ' Limit the selection to just one document by setting the start ...
        masterDoc.MailMerge.DataSource.LastRecord = masterDoc.MailMerge.DataSource.ActiveRecord               ' ... and end points to the active record
        masterDoc.MailMerge.Execute False                                        ' run the MailMerge based on the above settings (i.e. for one record)
        Set singleDoc = ActiveDocument                                           ' Identify the ActiveDocument (foremost doc after running the MailMerge) as "singleDoc"
        singleDoc.SaveAs2 _
            FileName:=masterDoc.MailMerge.DataSource.DataFields("DocFolderPath").Value & Application.PathSeparator & _
                masterDoc.MailMerge.DataSource.DataFields("DocFileName").Value & ".docx", _
            FileFormat:=wdFormatXMLDocument                                      ' Save "singleDoc" as a word docx with the details provided in the DocFolderPath and DocFileName fields in the MailMerge data
        singleDoc.ExportAsFixedFormat _
            OutputFileName:=masterDoc.MailMerge.DataSource.DataFields("PdfFolderPath").Value & Application.PathSeparator & _
                masterDoc.MailMerge.DataSource.DataFields("PdfFileName").Value & ".pdf", _
            ExportFormat:=wdExportFormatPDF                                      ' Export "singleDoc" as a PDF with the details provided in the PdfFolderPath and PdfFileName fields in the MailMerge data
        singleDoc.Close False                                                    ' Close "singleDoc", the variable "singleDoc" can now be used for the next record when created
        If masterDoc.MailMerge.DataSource.ActiveRecord >= lastRecordNum Then     ' test if we have just created a document for the last record
            lastRecordNum = 0                                                    ' if so we set lastRecordNum to zero to indicate that the loop should end
        Else
            masterDoc.MailMerge.DataSource.ActiveRecord = wdNextRecord           ' otherwise go to the next active record
        End If

    Loop                                                                         ' loop back to the Do start
End Sub                                                                          ' Mark the end of the Subroutine

In my Word doc, I have line items where the value may be 0 or blank. Each line item is on a separate line. Example:

Value A: 1234
Value B: 0
Value C: 2
Value D:

Is there a way to automate the removal of the line item if the value of the mail merge field is 0 or blank. Ideal outcome would be:

Value A: 1234
Value C: 2

If it helps narrow down the problem, I am able to replace the 0 and blank with the word "NULL" in my Excel spreadsheet:

Value A: 1234
Value B: NULL
Value C: 2
Value D: NULL

But I would still want the same outcome as above:

Value A: 1234
Value C: 2

I've Googled various combinations of "word macro delete line" but am not really understanding what I am reading online and how I can modify the code above to account for the deletion prior to the PDF generation. Any help would be appreciated.

CodePudding user response：

You don't need any VBA for this - it can all be handled in the mailmerge itself via field coding.

For example, for Value_A:

{IF{MERGEFIELD Value_A \# 0}<> 0 "Value A: {MERGEFIELD Value_A}¶
"}

and, for all the values:

{IF{MERGEFIELD Value_A \# 0}<> 0 "Value A: {MERGEFIELD Value_A}¶
"}{IF{MERGEFIELD Value_B \# 0}<> 0 "Value B: {MERGEFIELD Value_B}¶
"}{IF{MERGEFIELD Value_C \# 0}<> 0 "Value C: {MERGEFIELD Value_C}¶
"}{IF{MERGEFIELD Value_D \# 0}<> 0 "Value D: {MERGEFIELD Value_D}¶
"}

If the fields are always empty when not containing data to be output, you could reduce the field coding to:

{MERGEFIELD Value_A \b "Value A: " \f ¶
"}{MERGEFIELD Value_B \b "Value B: " \f ¶
"}{MERGEFIELD Value_C \b "Value C: " \f ¶
"}{MERGEFIELD Value_D \b "Value D: " \f ¶
"}

Note: The field brace pairs (i.e. '{ }') for the above example are all created in the document itself, via Ctrl-F9 (Cmd-F9 on a Mac or, if you’re using a laptop, you might need to use Ctrl-Fn-F9); you can't simply type them or copy & paste them from this message. Nor is it practical to add them via any of the standard Word dialogues. The spaces represented in the field constructions are all required. Instead of the ¶ symbols, you should use real line/paragraph breaks.