Word 2003, How to Save Filters Permanently?

[Kate]

I use Word 2003 to produce a report with Mailmerge, I set up filters for the
specific data in the complex database. If I’ll keep same datasource for next
report, but some changes have been made in the database, all advanced filter
that were set up will disappear and I need to set up all filters all over
again. Is there a way to keep filters permanently?

 

[Peter Jamieson]

What type of data source are you using, and which facilities in Word are you
using to filter? (Word does different things depending on whether you e.g.
select specfic records using their checkboxes in Mailmerge Recipients, or
using the drop-downs in the column headers in that dialog, or using the
Query Options dialog)

but some changes have been made in the database, all advanced filter

Are these structural changes (e.g. someone has added columns) or changes to
the database content (e.g. someone has added/deleted rows?

There are general problems in this area and there may be an error in Word
that will lose your sorts/filters however you set them up, but if that is
the case, then what I had in mind was that once you set up the sorts/filters
you want, you use a bit of VBA to save the SQL query that Word constructs
from your specifications, and when you re-open the document, you use another
bit of VBA to re-instate that query. Not very nice, but I think it is at
least feasible, as long as you are allowed to run VBA macros (and
preferably, as long as you are the only user). But if you are selecting
individual records in the MailMerge Recipients, Word does not modify the SQL
query's WHERE clause - it just stores a list of selected records - so that
approach would not be enough.

 

[Kate]

Dear Peter,
Thank you very much for your detailed response, truly appreciate that!
Can I ask you for another advice?

With Mailmerge Report production (Word 2003), and using Excel spreadsheet as
a data source, in the option for filters, when I click on the column and use
drop down menu with selection of Advanced - Filter Records, I can see my
filters option in blue and can add or modify my filter options.

I would set up few filters, after that if I made some changes in database
(not the columns or rows, but just different content). With next report
filters are still in place but as a default, my spreadsheet still filtered,
but I don’t see my selections in Advanced Filter options.

Am I doing something incorrectly or this is the case with filters in this
situation? Can I set up filters the way that I’ll be able to see my filtering
options?

Thanks again,

--
Thanks,
Kate

What type of data source are you using, and which facilities in Word are you
using to filter? (Word does different things depending on whether you e.g.
select specfic records using their checkboxes in Mailmerge Recipients, or
using the drop-downs in the column headers in that dialog, or using the
Query Options dialog)

but some changes have been made in the database, all advanced filter

Are these structural changes (e.g. someone has added columns) or changes to
the database content (e.g. someone has added/deleted rows?

There are general problems in this area and there may be an error in Word
that will lose your sorts/filters however you set them up, but if that is
the case, then what I had in mind was that once you set up the sorts/filters
you want, you use a bit of VBA to save the SQL query that Word constructs
from your specifications, and when you re-open the document, you use another
bit of VBA to re-instate that query. Not very nice, but I think it is at
least feasible, as long as you are allowed to run VBA macros (and
preferably, as long as you are the only user). But if you are selecting
individual records in the MailMerge Recipients, Word does not modify the SQL
query's WHERE clause - it just stores a list of selected records - so that
approach would not be enough.

 

[Peter Jamieson]

OK, on a really simple example here, everything works OK. But you do have to
save the Mail Merge Main document, because that is where the filters are
stored. (And you can only insert so many filters.)

If you can open up the VBA Editor, open the immediate window and type

print activedocument.mailmerge.datasource.querystring

(then enter)

you should see the query that Word last built from your query options, e.g.

SELECT * FROM `Sheet1$` WHERE `t` = 'text4' And `d` = #10/25/2007#

If you save that somewhere - e.g. in a Notepad document, if you lose the
filter when you re-open the Word document then you should be able to use

activedocument.mailmerge.datasource.querystring = "SELECT * FROM `Sheet1$`
WHERE `t` = 'text4' And `d` = #10/25/2007#"

to set the filters back again. (If there are any double-quote characters in
the query, you have to double them up).

--
Peter Jamieson
http://tips.pjmsn.me.uk

Dear Peter,
Thank you very much for your detailed response, truly appreciate that!
Can I ask you for another advice?

With Mailmerge Report production (Word 2003), and using Excel spreadsheet
as
a data source, in the option for filters, when I click on the column and
use
drop down menu with selection of Advanced - Filter Records, I can see my
filters option in blue and can add or modify my filter options.

I would set up few filters, after that if I made some changes in database
(not the columns or rows, but just different content). With next report
filters are still in place but as a default, my spreadsheet still
filtered,
but I don’t see my selections in Advanced Filter options.

Am I doing something incorrectly or this is the case with filters in this
situation? Can I set up filters the way that I’ll be able to see my
filtering
options?

Thanks again,

 

[Kate]

Thanks for your prompt response,