Results 1 to 2 of 2
  1. #1

    Word Mail Merge Excel Data Source File (Word XP/Excel 97)

    I am trying to write a SQL statement to filter the records in the Excel data source file before the Word mail merge is executed, but the results file is not filtered--all Excel records come in! The Excel file table is a system table, and the column heading is "Code:" that should equal a zero ( "0"). No other records should be merged. Could someone please look at the following VBA code and tell me what I am doing wrong. I have very little experience with SQL statements in a mail merge. Attached is the Excel file if it helps. Many thanks!

    Sub BobReTest()

    Dim strVariable1 As String

    strVariable1 = "0"
    strVariable1 = "'" & strVariable1 & "'"

    Documents.Open FileName:="""C:My Documentsmerge code.doc""", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
    PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
    WritePasswordTemplate:="", Format:=wdOpenFormatAuto
    ActiveDocument.MailMerge.MainDocumentType = wdCatalog

    ActiveDocument.MailMerge.OpenDataSource Name:= _
    "C:Bob's FilesRobinsonEstimate.xls", ConfirmConversions:=False, _
    ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
    PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
    WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
    Connection:= _
    "Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er ID=Admin;Data Source=""C:Bob's FilesRobinsonEstimate.xls"";Mode=Read;Extended Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:E" _
    , SQLStatement:="SELECT * FROM [ActualEstimateMasterTest$] WHERE (([Code:] =" & strVariable1 & ") )" & "", _
    SQLStatement1:="", SubType:=wdMergeSubTypeAccess

    With ActiveDocument.MailMerge
    .Destination = wdSendToNewDocument
    .SuppressBlankLines = True
    With .DataSource
    .FirstRecord = wdDefaultFirstRecord
    .LastRecord = wdDefaultLastRecord
    End With
    .Execute Pause:=False
    End With
    End Sub
    Attached Files Attached Files

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Silicon Valley, USA
    Thanked 94 Times in 90 Posts

    Re: Word Mail Merge Excel Data Source File (Word XP/Excel 97)

    There is a duplicate of this message on the VB/VBA board, <!post=Post #140899,140899>Post #140899<!/post>. Please continue this discussion over there.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts