locked
Mail Merge to Word problem when data source is an open Excel file RRS feed

  • Question

  • Using Office 2106 and Windows 10.

    I have an Excel workbook using VBA to prepare data for a mail merge to word and then to run the merge from within the Excel procedure.  The data is located in a named range within one worksheet of the open workbook.  I am using late binding because excel and word have to run on machines that use different versions of Office (2013 and 2016), but all have to save back to the Office 2013 machine.  I cannot therefore set the references to Word because it is different for each machine.  I have generated the code for the mail merge primarily by recording a macro in Word and then adapting it to run on excel.

    My problem lies in that when the code gets to the point when it defines the data source for the merge, it tries to open it, but finds that it is already open (because that is where the code is running  from!)  Extensive research has not found a suitable syntax to tell word to use the data source that is already open.

    In the code below, XFERDATA is the named range within one of the worksheets in the excel workbook where the code is located.  \ctPURCHASE ORDER.dotm is the Mail Merge document.

    ActiveWorkbook.Save
        On Error Resume Next
        Set ObjWord = GetObject(, "Word.Application")
        If ObjWord Is Nothing Then
            Set ObjWord = CreateObject("Word.Application")
        End If
        Set objDocument = GetObject(, ObjWord.Document)
        On Error GoTo 0

        Set objMergeDoc = ObjWord.Documents.Add(ThisWorkbook.Path & "\ctPURCHASE ORDER.dotm")
        
        objMergeDoc.MailMerge.MainDocumentType = 0
        objMergeDoc.MailMerge.OpenDataSource _           THE PROBLEM OCCURS HERE
            Name:=ThisWorkbook.Path & "\DailySheets.xlsm", _
            AddToRecentFiles:=False, _
            Revert:=False, _
            Format:=0, _
            Connection:="XFERDATA", _
            SQLStatement:="SELECT * FROM `'XFERDATA'`", SQLStatement1:=""

         With objMergeDoc.MailMerge
            .Destination = 0
            .SuppressBlankLines = True
            With .DataSource
                .FirstRecord = 1
                .LastRecord = -16
            End With
            .Execute Pause:=False
        End With
       
        ObjWord.Visible = True

    End sub

    At the problem point I get a box telling me that I cannot open the data source, but offering me a read only copy.  If I accept that I can proceed, but clearly that is not an acceptable answer.  I guess that the OpenDataSource method is the wrong one, but I cannot find an alternative method that tells Word that its data source is already open.  I realise that I could generate the data source into a new workbook and then close it before running the merge, but that is an inelegant solution.

    Any guidance on the appropriate method and syntax would be much appreciated.

    With thanks

    Andy C

    Saturday, May 14, 2016 2:30 PM

Answers

  • You can add a line to open a read-only connection to the workbook.

    You can use ActiveWorkbook.FullName instead of ThisWorkbook.Path & "\DailySheets.xlsm" to refer to the workbook running the code.

    Sub MergeIt()
        Dim objWord As Object
        Dim objMergeDoc As Object
        ActiveWorkbook.Save
        On Error Resume Next
        Set objWord = GetObject(Class:="Word.Application")
        If objWord Is Nothing Then
            Set objWord = CreateObject(Class:="Word.Application")
        End If
        On Error GoTo 0
        
        Set objMergeDoc = objWord.Documents.Add(ThisWorkbook.Path & "\ctPURCHASE ORDER.dotm")
        
        With objMergeDoc.MailMerge
            .MainDocumentType = 0
            .OpenDataSource _
                Name:=ThisWorkbook.FullName, _
                AddToRecentFiles:=False, _
                ReadOnly:=True, _
                Revert:=False, _
                Connection:="XFERDATA", _
                SQLStatement:="SELECT * FROM `XFERDATA`"
            .Destination = 0
            .SuppressBlankLines = True
            .Execute Pause:=False
        End With
        
        objWord.Visible = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by AndyColRomsey Saturday, May 14, 2016 5:40 PM
    Saturday, May 14, 2016 3:28 PM

All replies

  • You can add a line to open a read-only connection to the workbook.

    You can use ActiveWorkbook.FullName instead of ThisWorkbook.Path & "\DailySheets.xlsm" to refer to the workbook running the code.

    Sub MergeIt()
        Dim objWord As Object
        Dim objMergeDoc As Object
        ActiveWorkbook.Save
        On Error Resume Next
        Set objWord = GetObject(Class:="Word.Application")
        If objWord Is Nothing Then
            Set objWord = CreateObject(Class:="Word.Application")
        End If
        On Error GoTo 0
        
        Set objMergeDoc = objWord.Documents.Add(ThisWorkbook.Path & "\ctPURCHASE ORDER.dotm")
        
        With objMergeDoc.MailMerge
            .MainDocumentType = 0
            .OpenDataSource _
                Name:=ThisWorkbook.FullName, _
                AddToRecentFiles:=False, _
                ReadOnly:=True, _
                Revert:=False, _
                Connection:="XFERDATA", _
                SQLStatement:="SELECT * FROM `XFERDATA`"
            .Destination = 0
            .SuppressBlankLines = True
            .Execute Pause:=False
        End With
        
        objWord.Visible = True
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by AndyColRomsey Saturday, May 14, 2016 5:40 PM
    Saturday, May 14, 2016 3:28 PM
  • Hans

    As always, a positive mine of expertise!  Thank you; your solution worked exactly as I wanted it to.

    Andy C

    Saturday, May 14, 2016 5:40 PM