Results 1 to 4 of 4
  1. #1
    maryjanice92
    Guest

    VBA Code with a SQL Statement to Filter Excel File (Word XP/Excel 97)

    I am trying to write a SQL statement in Word to filter the records of an Excel data source file before the Word mail merge is executed, but the results file is not filtered--all Excel the records come in! The Excel file table is a system table, and the column heading to be filtered 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
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Dallas plus 20 miles or so, Texas, USA
    Posts
    876
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VBA Code with a SQL Statement to Filter Excel File (Word XP/Excel 97)

    Well, I have even less experience and would certainly never, ever wish to be tasked with writing the code in your previous post, but could defining the strVariable1 twice be causing you problems?

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

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: VBA Code with a SQL Statement to Filter Excel File (Word XP/Excel 97)

    I don't have XP, and it sounds as though it works differently than in 2000. Have you read through: HOWTO: Automate Word 2002 with Visual Basic to Create a Mail Merge (Q285332)? It, along with PRB: Prompt to Select Table with Word 2002 Mail Merge Code for Excel or Access Data Source (Q289830), seems to suggest that your Connection string could be omitted completely. Not sure that's really true, but perhaps worth trying.

  4. #4
    maryjanice92
    Guest

    Re: VBA Code with a SQL Statement to Filter Excel File (Word XP/Excel 97)

    Many, many thanks for pointing me in the right direction again. I will check out the reference documents you mentioned and see if they help solve my problem. I have a feeling my Excel spreadsheet may need to be adjusted, too, in order to be a valid data source file. Thank you (and Mike) for responding so quickly to my call for help! Until next time....Mary

Posting Permissions

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