Microsoft Office Forums - View Single Post - [Solved] Delete blank table rows in merged document and split document in docx and pdf based on excel rows

Alex1s85 · 05-22-2021, 02:15 AM

Hello again,

I need some help here, please.

I've attached a sample letter and the pool from excel.

I have to delete empty table rows - after mail merge - and split the letters into word and pdf. The pages per letter may vary and I want to have the headers and the footers after the split.

I use the following code to split the letters into word and pdf but I haven't found a way to combine this code with the "Sub DeleteEmptyRows ()"

Code:

Option Explicit

Const FOLDER_SAVED As String = "destination folder" 'Makes sure your folder path ends with a backward slash
Const SOURCE_FILE_PATH As String = "your pool's full path"

Sub TestRun()
Dim MainDoc As Document, TargetDoc As Document
Dim dbPath As String
Dim recordNumber As Long, totalRecord As Long

Set MainDoc = ActiveDocument
With MainDoc.MailMerge
    
        ' if you want to specify your data, insert a WHERE clause in the SQL statement
        .OpenDataSource Name=SOURCE_FILE_PATH, sqlstatement=SELECT  FROM [Sheet1$]
            
        totalRecord = .DataSource.RecordCount

        For recordNumber = 1 To totalRecord
        
            With .DataSource
                .ActiveRecord = recordNumber
                .FirstRecord = recordNumber
                .LastRecord = recordNumber
            End With
            
            .Destination = wdSendToNewDocument
            .Execute False
            
            Set TargetDoc = ActiveDocument

            TargetDoc.SaveAs2 FOLDER_SAVED & .DataSource.DataFields(CID).Value & .docx, wdFormatDocumentDefault
            TargetDoc.ExportAsFixedFormat FOLDER_SAVED & .DataSource.DataFields(CID).Value & .pdf, exportformat=wdExportFormatPDF
            
            TargetDoc.Close False
            
            Set TargetDoc = Nothing
                    
        Next recordNumber

End With

Set MainDoc = Nothing
End Sub

Any help would be just valuable