I'm trying to create some Excel code to eventually produce a mail merge document in Word.
The code works fine, and a mail merged document comes out of other end, but, my problem is that I don't want to return all records in the range that I've defined in the Excel 'table'. This works fine if doing the process manually in Word using the 'select recipients' mail merge option but I can't get the code into Excel. All help I've come across suggests that a SQL statement is the answer, but whatever I put in I still get all rows returned.
An extract from my code (edited to make a bit easer to read) is below
Dim wdApp As Word.Application Dim WordWasNotRunning As Boolean Dim wdDoc As Word.Document 'Get existing instance of Word if it's open; otherwise create a new one On Error Resume Next Set wdApp = GetObject(, "Word.Application") If Err Then Set wdApp = New Word.Application WordWasNotRunning = True End If On Error GoTo Err_Handler wdApp.Visible = True wdApp.Activate Dim t t = "template name.dot" Set wdDoc = wdApp.Documents.Add(t) Dim sql sql = "SELECT * FROM `NamedRange` WHERE `FieldName` IS NOT NULL" With wdDoc.MailMerge .OpenDataSource _ Name:="source file.xls", _ Connection:="NamedRange", _ SqlStatement:=sql .Destination = wdSendToNewDocument .Execute End With
Note that NamedRange is a range named on a worksheet, and FieldName is the name of one of my Columns
Any suggestions would be much appreciated.