Skip to content

Instantly share code, notes, and snippets.

@DataSolveProblems
Last active October 1, 2020 09:38
Show Gist options
  • Save DataSolveProblems/ba148a72ee5bec552b672ed8bca80e89 to your computer and use it in GitHub Desktop.
Save DataSolveProblems/ba148a72ee5bec552b672ed8bca80e89 to your computer and use it in GitHub Desktop.
Option Explicit
Const FOLDER_SAVED As String = "<Destination Folder Path>" `Makes sure your folder path ends with a backward slash
Const SOURCE_FILE_PATH As String = "<Data File 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 [<Worksheet Name>$]"
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("Client_Name").Value & ".docx", wdFormatDocumentDefault
TargetDoc.ExportAsFixedFormat FOLDER_SAVED & .DataSource.DataFields("Client_Name").Value & ".pdf", exportformat:=wdExportFormatPDF
TargetDoc.Close False
Set TargetDoc = Nothing
Next recordNumber
End With
Set MainDoc = Nothing
End Sub
@DemoDrift
Copy link

DemoDrift commented Sep 16, 2019

I'm getting an error: There is an error "438" in the line of ".OpenDataSource Name:=source_file_path, SQLStatement:="SELECT * FROM [XXX$]" saying it couldn't find my source file. But I input the path correct.
Someone may help me. Thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment