Menyimpan multiple Document microsoft word dalam bentuk PDF dari mail merge

Uncategorized

nah jadi beberapa hari lalu saya di minta untuk membuat raport online salah satu sekolah di daerahku, raport yang sekarng ada dalam bentuk microsoft word mailmerge kedalam bentuk pdf secara otomatis dan ouputnya dalam bentuk PDF individual.

alt+f11

new module

Option Explicit

Const FOLDER_SAVED As String = "F:\mtsha\Nilai Upload Web semester gasal 2020-2021\saved\"
Const SOURCE_FILE_PATH As String = "F:\mtsha\Nilai Upload Web semester gasal 2020-2021\nilai upload web.xls"

Sub MailMergeToIndPDF()
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.ExportAsFixedFormat FOLDER_SAVED & .DataSource.DataFields("Kls").Value & "-" & .DataSource.DataFields("Nama").Value & "-" & .DataSource.DataFields("NISN").Value & ".pdf", exportformat:=wdExportFormatPDF
            TargetDoc.Close False
            Set TargetDoc = Nothing
        Next recordNumber
End With
    On Error Resume Next
    Kill FOLDER_SAVED & "*.docx"
    On Error GoTo 0
Set MainDoc = Nothing
End Sub
'adopted from https://learndataanalysis.org/automate-mail-merge-to-save-each-record-individually-with-word-vba/
'with additional delete docx file



Leave a Reply

Your email address will not be published. Required fields are marked *