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

[maryjanice92]

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

[MichaelRead]

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 & "'"
--------------------------------------

[jscher2000]

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.

[maryjanice92]

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