Todd Harris
Todd Harris

Reputation: 17

Why does my excel macro open a new excel workbook in read only before doing mail merge rather than using the excel already open?

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

Answers (0)

Related Questions