locked
word mail merge to excel worksheet using vba RRS feed

  • Question

  • To All,

    I am trying do a simple mail merge using excel worksheet as datasource.

    The datasource for the merge is all located on one worksheet named "merge data"

    I'm using the follow code snippet to do the merge:

    Dim oWordPath As String 'path and file name for the template
        If oWordApp Is Nothing Then
            Set oWordApp = CreateObject("word.application")      'word application
        End If
       
        oWordApp.documents.Open Filename:=ActiveWorkbook.Path & "\1- Tooling Quote_merge.doc"
        'show the application...turn off !
        oWordApp.Visible = True
        With oWordApp.activedocument.mailmerge
            .opendatasource Name:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name, ReadOnly:=True, sqlstatement:="select * from [merge_data$]"
           
            .Destination = wdSendToNewDocument
            .Execute
        End With

     

    I'm confuse on why word still ask me what worksheet the data is coming from when word is fired up. The word doc is working fine if I do it manually by clicking on the merge preview button.

    Thanks

    PHIL


    phil Chin
    Thursday, October 14, 2010 2:41 PM

Answers

  • Hi Phil

    It would help to know which version of Word is involved, here. Since version 2002, opening a mail merge document via automation will cut off the data source, so I don't think Doug's suggestion would apply...

    Some of the connection information required also changed in the same Word version. Excel went from a default DDE connection to OLE DB. With that change, alterations in the connection information for OpenDataSource are required.

    Probably the fastest and simplest thing for you to do would be to

    1. Open a/the document as an end-user
    2. Record a macro
    3. Start a mail merge and attach to the data source

    Stop the macro recorder and look at the recorded OpenDataSource method. Compare it to what you have, currently. Execute the merge to a new document and check that the result is what you expect.

    FWIW my best guess is that you previously had a DDE connection. The question is, whether the OLE DB default you'll probably get when you record the macro gives you the right result, or if something in your mail merge requires a DDE connection. In that case, once I know the version of Word, I can explain how you force a DDE link, so you can record a macro with that connection information.


    Cindy Meister, VSTO/Word MVP
    • Proposed as answer by Amy Li Thursday, October 21, 2010 1:48 AM
    • Marked as answer by Amy Li Monday, October 25, 2010 2:05 AM
    Wednesday, October 20, 2010 2:51 PM

All replies

  • If the Word document already has the data source attached to it, you do not need the

            .opendatasource Name:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name, ReadOnly:=True, sqlstatement:="select * from [merge_data$]"


    -- Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "philchin" wrote in message news:60c1b786-1128-4e10-af05-f57a296a5e1e@communitybridge.codeplex.com...

    To All,

    I am trying do a simple mail merge using excel worksheet as datasource.

    The datasource for the merge is all located on one worksheet named "merge data"

    I'm using the follow code snippet to do the merge:

    Dim oWordPath As String 'path and file name for the template
        If oWordApp Is Nothing Then
            Set oWordApp = CreateObject("word.application")      'word application
        End If

        oWordApp.documents.Open Filename:=ActiveWorkbook.Path & "\1- Tooling Quote_merge.doc"
        'show the application...turn off !
        oWordApp.Visible = True
        With oWordApp.activedocument.mailmerge
            .opendatasource Name:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name, ReadOnly:=True, sqlstatement:="select * from [merge_data$]"

            .Destination = wdSendToNewDocument
            .Execute
        End With

    I'm confuse on why word still ask me what worksheet the data is coming from when word is fired up. The word doc is working fine if I do it manually by clicking on the merge preview button.

    Thanks

    PHIL

    -- phil Chin


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Thursday, October 14, 2010 11:24 PM
  • Hi philchin,

    I'm writing to check the status of the thread. Could you please let me know if you have solved the problem?

    Best Regards,
    Amy Li
    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Wednesday, October 20, 2010 2:00 AM
  • Hi Phil

    It would help to know which version of Word is involved, here. Since version 2002, opening a mail merge document via automation will cut off the data source, so I don't think Doug's suggestion would apply...

    Some of the connection information required also changed in the same Word version. Excel went from a default DDE connection to OLE DB. With that change, alterations in the connection information for OpenDataSource are required.

    Probably the fastest and simplest thing for you to do would be to

    1. Open a/the document as an end-user
    2. Record a macro
    3. Start a mail merge and attach to the data source

    Stop the macro recorder and look at the recorded OpenDataSource method. Compare it to what you have, currently. Execute the merge to a new document and check that the result is what you expect.

    FWIW my best guess is that you previously had a DDE connection. The question is, whether the OLE DB default you'll probably get when you record the macro gives you the right result, or if something in your mail merge requires a DDE connection. In that case, once I know the version of Word, I can explain how you force a DDE link, so you can record a macro with that connection information.


    Cindy Meister, VSTO/Word MVP
    • Proposed as answer by Amy Li Thursday, October 21, 2010 1:48 AM
    • Marked as answer by Amy Li Monday, October 25, 2010 2:05 AM
    Wednesday, October 20, 2010 2:51 PM
  • If the sheet is really called "merge data" then your sqlstatement should be "select * from [merge data$]", not  "select * from [merge_data$]" - otherwise, you will probably see the ODBC connection dialog (the one with an "Options" button in it.
    Peter Jamieson
    Thursday, October 21, 2010 8:08 AM