this vba word code work properly, but i want to modified for setting "Folder Saved & Source File Path" is not manually again
this my steps:
1. i need like show message box like e.g. "please, select Folder Saved...." & "select "Source file path database......." then next step/OK
2. save each record as their own individual Word document only in .docx format not pdf with file name using data field "name_file"
here complete code:
Code: Select all
Option Explicit
Const [b]FOLDER_SAVED[/b] As String = "D:\TUTORIAL\PART1\Surat Tugas-" 'sesuaikan direktorinya
Const [b]SOURCE_FILE_PATH[/b] As String = "D:\TUTORIAL\PART1\database.xlsx" 'sesuaikan direktorinya
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("[b]Name_File[/b]").Value & ".docx", wdFormatDocumentDefault '
TargetDoc.ExportAsFixedFormat FOLDER_SAVED & .DataSource.DataFields("[b]Name_File[/b]").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
susanto