psctornado
10-06-2017, 05:50 AM
I currently have a setup where I trigger a mail merge from an excel file after the user fills out several fields & once completed triggers lit by pressing a button. The doc template has only 4 pages, but when the doc is created it creates 34 pages. After the initial 4 pages is generated with the merged data, the following pages is just template junk with no additional merged fields. I am baffled as to why, so I was looking for some help. Below is my word VBA (found in the word doc template file.)
Private Sub Document_Open()
Application.DisplayAlerts = wdAlertsNone
Dim MMSource As String
MMSource = "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\Lit Workbook\Lit Workbook.xls"
With ActiveDocument
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=MMSource, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=MMSource;Mode=Read;Extended Properties=" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:Database Loc", SQLStatement:="SELECT * FROM `Master1$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
.MainDocumentType = wdNotAMergeDocument
End With
Application.WindowState = wdWindowStateMaximize
Application.DisplayAlerts = wdAlertsAll
.Saved = True
.Close SaveChanges:=wdDoNotSaveChanges
End With
End Sub
The VBA from excel tied into the button is :
Private Sub Lit_Click()
Application.ScreenUpdating = False
Run "Save"
Call Lit_merge
Application.ScreenUpdating = True
End Sub
Sub Save()
ActiveWorkbook.SaveCopyAs CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\Lit Workbook\Lit Workbook.xls"
End Sub
Sub Lit_merge()
Set appWD = CreateObject("Word.Application")
appWD.Visible = True
appWD.Documents.Open Filename:="C:\Documents And Settings\" & Environ("UserName") & "\My Documents\Lit Workbook\Lit Templates\Lit Template.doc"
End Sub
Sub Master_Lit()
Sheet25.Range("A1:S3").ClearContents
Sheet25.Range("A4:S5").Copy
Sheet25.Range("A1").PasteSpecial Paste:=xlPasteFormulas
End Sub
Sheet 25 = Master1 tab where I have the data pulling for the mail merge. I clear out the 1st 3 rows & paste what is found in rows 4 & 5.
While testing on my end, I've tried reducing the mail merge to just a 1 page template & it's still creating > 10 pages from that 1 page alone. I really can't determine if its a excel vba issue or word vba issue or maybe a word doc setting. I'm baffled!
Any help would be appreciated.
Private Sub Document_Open()
Application.DisplayAlerts = wdAlertsNone
Dim MMSource As String
MMSource = "C:\Documents And Settings\" & Environ("UserName") & "\My Documents\Lit Workbook\Lit Workbook.xls"
With ActiveDocument
With .MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource Name:=MMSource, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=MMSource;Mode=Read;Extended Properties=" & _
"HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=35;" & _
"Jet OLEDB:Database Loc", SQLStatement:="SELECT * FROM `Master1$`", SQLStatement1:="", SubType:=wdMergeSubTypeAccess
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
.MainDocumentType = wdNotAMergeDocument
End With
Application.WindowState = wdWindowStateMaximize
Application.DisplayAlerts = wdAlertsAll
.Saved = True
.Close SaveChanges:=wdDoNotSaveChanges
End With
End Sub
The VBA from excel tied into the button is :
Private Sub Lit_Click()
Application.ScreenUpdating = False
Run "Save"
Call Lit_merge
Application.ScreenUpdating = True
End Sub
Sub Save()
ActiveWorkbook.SaveCopyAs CreateObject("WScript.Shell").SpecialFolders("MyDocuments") & "\Lit Workbook\Lit Workbook.xls"
End Sub
Sub Lit_merge()
Set appWD = CreateObject("Word.Application")
appWD.Visible = True
appWD.Documents.Open Filename:="C:\Documents And Settings\" & Environ("UserName") & "\My Documents\Lit Workbook\Lit Templates\Lit Template.doc"
End Sub
Sub Master_Lit()
Sheet25.Range("A1:S3").ClearContents
Sheet25.Range("A4:S5").Copy
Sheet25.Range("A1").PasteSpecial Paste:=xlPasteFormulas
End Sub
Sheet 25 = Master1 tab where I have the data pulling for the mail merge. I clear out the 1st 3 rows & paste what is found in rows 4 & 5.
While testing on my end, I've tried reducing the mail merge to just a 1 page template & it's still creating > 10 pages from that 1 page alone. I really can't determine if its a excel vba issue or word vba issue or maybe a word doc setting. I'm baffled!
Any help would be appreciated.