Scribd
Upload
72 views1 page

Siyntax Merge

This VBA code performs a mail merge in Microsoft Word to create individual PDF files for each record in an Excel data source. It opens the data source, loops through each record, performs the mail merge to create a new Word document, saves the document as a PDF file using the record ID in the name, and deletes the temporary Word documents. The code cites the source it was adapted from with additional functionality to delete the intermediate docx files.

Uploaded by

Kien Eskaa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
72 views1 page

Siyntax Merge

This VBA code performs a mail merge in Microsoft Word to create individual PDF files for each record in an Excel data source. It opens the data source, loops through each record, performs the mail merge to create a new Word document, saves the document as a PDF file using the record ID in the name, and deletes the temporary Word documents. The code cites the source it was adapted from with additional functionality to delete the intermediate docx files.

Uploaded by

Kien Eskaa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

Option Explicit

Const FOLDER_SAVED As String = "C:\Users\Resika Arthana\Google Drive


(resika@undiksha.ac.id)\06 Kegiatan Undiksha\0 UPT TIK 2019\ICONMNS\LOA\LOA-
ICONMNS2019-"
Const SOURCE_FILE_PATH As String = "C:\Users\Resika Arthana\Google Drive
(resika@undiksha.ac.id)\06 Kegiatan Undiksha\0 UPT TIK 2019\ICONMNS\datapaper.xlsx"

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.SaveAs2 FOLDER_SAVED & .DataSource.DataFields("ID").Value &


".docx", wdFormatDocumentDefault
TargetDoc.ExportAsFixedFormat FOLDER_SAVED
& .DataSource.DataFields("ID").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

You might also like