Macro to access Excel DB

April 26, 2010 at 14:29:24
Specs: Windows XP
My macro to create a catalog list from data in an Excel spreadsheet worked with MS Word 2000. With Word 2007 the filter in the DB doesn't work. What is the proper VBA call to 'filter' the Excel data?

See More: Macro to access Excel DB

Report •

April 27, 2010 at 04:17:22
We can't see your macro from where we're sitting so there is no way for us to what you mean by 'filter' the Excel data.

You need to tell us, in detail, what the code is supposed to do, what is not working, what line it is failing on, etc.

It would probably help if you included the code or at least the section that is failing.

If you are going to paste the code into your response, please click on the pre tag above the comments box and paste your code between those tags.

Report •

April 27, 2010 at 05:41:15
Here is the code to open the Excel spreadsheet:
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\CIC\CIC Member List.xls", ConfirmConversions:=False, ReadOnly:= _
False, LinkToSource:=True, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", WritePasswordDocument:="", WritePasswordTemplate:= _
"", Revert:=False, Format:=wdOpenFormatAuto, Connection:= _
"Entire Spreadsheet", SQLStatement:="", SQLStatement1:=""

And here is code from a WORD 2000 macro to 'filter' the data:
ActiveDocument.MailMerge.DataSource.QueryString = _
"SELECT * FROM C:\CIC\CIC Member List.xls WHERE ((Status = 'Active'))" _
& ""

The 'filter' code does NOT work with WORD 2007. My question is what 2007 VBA code will provide the 'filtering' function?

Report •

April 27, 2010 at 07:27:26
I finally figured it out. All I had to do was modify the SQL statement.

SQLStatement:="SELECT * FROM `Sheet1$`WHERE ((Status = 'Active'))"

Now it works with WORD 2007.

Bob Roberts (brob243)

Report •

Related Solutions

Ask Question