Filtering Mail merge using Word with Excel data source

[Mike UK]

Ultimately I am aiming to have a mail merge document whereby the user inputs a variable which filters on that input and produces the mail merge purely for the row that has that code.

At the moment the coding doesn't include the variable as I've been unsuccessful in getting the filtering to work when the variable is entered directly into the VBA code.

Main$ is the name of the excel worksheet, whilst P Code is header for Column C which contains the codes the user can use to filter via the variable.

Sub MailMerge()
' Open Data Source
         ActiveDocument.MailMerge.OpenDataSource Name:= _
        "…. Data Source.xlsx", ConfirmConversions:=False, _
        ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
        PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
        WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
        Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=M:\SheetTemplates\DLAM\Holdings Data.xlsx;Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locki" _
        , SQLStatement:="SELECT * FROM `Main$`", SQLStatement1:="", SubType:= _
        wdMergeSubTypeAccess
 
  ' Filter and complete mail merge
    Dim strQry As String
    strQry = "SELECT * FROM 'Main$' WHERE 'P Code'= 'P0000844650'"
    With ActiveDocument.MailMerge
    .DataSource.QueryString = strQry
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
        With .DataSource
        .FirstRecord = wdDefaultFirstRecord
        .LastRecord = wdDefaultLastRecord
        End With
    .Execute Pause:=False
    End With
End Sub

Many thanks for any help,
Mike

 

[Macropod]

You can do all of this without recourse to VBA, via a SKIPIF field in the mailmerge main document, or by applying a mailmerge filter there (see under Mailings|Edit Recipient List>Filter). The SKIPIF field might be coded along the lines of:
{SKIPIF{MERGEFIELD P_Code}<> {FILLIN "Filter record" \o}}

 

[Mike UK]

Thanks Paul, Genius and far simpler than VBA! Thanks so much. Struggled initially with not using CTRL F9 to enter the field details but got there in the end.