Word 2013 Mailmerge - Edit select statement for mailmerge from excel RRS feed

  • Question

  • Hello,

    I am currently trying to create a mailmerge from an excel spreadsheet.
    The spreadsheet is given with all firstnames of a person while I only need the first firstname.
    I've tried to edit the ActiveDocument.MailMerge.DataSource.QueryString property of the mailmerge with a custom SELECT statement : 
    ActiveDocument.MailMerge.DataSource.QueryString = "SELECT Name, Address, City, LEFT(Firstnames, INSTR(1, Firstnames, ' ') - 1) AS Firstnames FROM [Tab1$]"
    However it seems like word doesn't like the query as it returns an error 4198 'Execution has failed' upon the execution of this very line.

    I don't know if the SQL functions I am using are right nor if it is even possible to edit the fields in the Select statement...

    Any idea?
    Monday, July 30, 2018 8:10 AM

All replies

  • Mmmm. The following syntax works for me:

        qs = "SELECT Lastname, LEFT(Firstname, INSTR(Firstname, ' ')) AS Firstname FROM [Sheet1$]"
        ActiveDocument.MailMerge.DataSource.QueryString = qs

    I didn't specify the "Start" parameter of INSTR. Possibly, the string function capability of the Query engine doesn't support that?

    (Note my field names differ slightly from yours, as does my sheet name).

    Cindy Meister, Office Developer/Word MVP, <a href=""> my blog</a>

    Monday, July 30, 2018 4:24 PM