Reputation: 17
When I use this code at home in a test environment it will just open the word doc with the merged data without opening another excel workbook. But when I use the exact same code with only the word doc file path/name changed at work it first opens a new version of the exact same excel workbook in read only while keeping the original open and then opens the word doc after that. What could be causing it to open the extra read only workbook? Can I stop this from happening? If it isn't possible to stop then can I tell it to close the read only one after the word doc has printed?
Sub DoMailMerge()
'Note: A VBA Reference to the Word Object Model is required, via Tools|References
Dim wdApp As New Word.Application, wdDoc As Word.Document
Dim strWorkbookName As String: strWorkbookName = ThisWorkbook.FullName
Dim r As Range
Dim nLastRow As Long, nLastColumn As Long
Dim nFirstRow As Long, nFirstColumn As Long
Set r = Selection
nLastRow = r.Rows.Count + r.Row - 2
nFirstRow = r.Row - 1
Dim WFile As String
WFile = Range("A2").Value
Dim sheetname As String
sheetname = ActiveSheet.Name
ActiveWorkbook.Save
With wdApp
'Disable alerts to prevent an SQL prompt
.DisplayAlerts = wdAlertsNone
'Open the mailmerge main document
Set wdDoc = .Documents.Open("S:\ISO\ISO - Form Templates\All certificates\" & WFile, _
ConfirmConversions:=False, ReadOnly:=True, AddToRecentfiles:=False)
With wdDoc
With .MailMerge
'Define the mailmerge type
.MainDocumentType = wdFormLetters
'Define the output
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
'Connect to the data source
.OpenDataSource Name:=strWorkbookName, ReadOnly:=False, _
LinkToSource:=False, AddToRecentfiles:=False, _
Format:=wdOpenFormatAuto, _
Connection:="Provider=Microsoft.ACE.OLEDB.12.0;" & _
"User ID=Admin;Data Source=strWorkbookName;" & _
"Mode=Read;Extended Properties=""HDR=YES;IMEX=1"";", _
SQLStatement:="SELECT * FROM `" & sheetname & "$`", _
SubType:=wdMergeSubTypeAccess
With .DataSource
.FirstRecord = nFirstRow
.LastRecord = nLastRow
End With
'Excecute the merge
.Execute
'Disconnect from the data source
.MainDocumentType = wdNotAMergeDocument
End With
'Close the mailmerge main document
.Close False
End With
'Restore the Word alerts
.DisplayAlerts = wdAlertsAll
'Display Word and the document
.Visible = True
.Activate
.PrintOut
wdApp.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges
wdApp.Quit
End With
End Sub
Upvotes: 0
Views: 41