Peter,
Thank you for your response and help. By the way my surname is Jamieson too
.... SPOOKY ... could be related ???
Anyway, I created the AutoOpen() macro that you suggested. The mail merge
connection only worked when the Mail Merge document was opened for the very
first time. Process below ...
The 1st time the Mail Merge doc is opened, the UNFILTERED SELECT statment is
applied:
SELECT * FROM `Flats$`
The AutoOpen() command is then activated and the FILTERED SELECT statement
is applied:
SELECT * FROM `Flats$` WHERE `Print` = 'p'
When you close and reopen the Mail Merge document, the FILTERED SELECT
statement is still applied so the database connection is lost and has to be
reset manually.
I understand the process but don't know how to resolve this problem. Any
ideas? Do you think I might need to create another macro for AutoClose() to
reset the SELECT statement or something?
Kelly Jamieson (aka vicchic01)
"Peter Jamieson" wrote:
There are some problems in this area (although I thought there was a
knowledgebase article covering it, I can't find it right now).
As far as I know, the only thing you can really do (other than stuff like
"change your data source to be something else") is to use VBA code in an
"Autoopen" macro to open the data source with the appropriate query to
filter the data. That is only really feasible if
a. you always save the mail merge main document disconnected from its data
source
b. you or your users are not changing the filter criteria all the time
c. you are not selecting/deselecting individual records in the Recipients
dialog box.
To do it, you need
d. to determine the query your mail merge main document is using. You
should be able to determine that by opening the mail merge main document,
setting up the mail merge data source (again :-( ) and filter/sort criteria,
then starting the VBA Editor, opening the Immediate window, and typing
print activedocument.mailmerge.datasource.querystring
you should see a response such as "SELECT * FROM tablename WHERE conditions"
e. Create an autoopen macro that does
Sub AutoOpen()
' Substitute the type of mail merge you need
With ActiveDocument.MailMerge
' Substitute the type of mail merge you need
.MainDocumentType = wdFormLetters
' Substitute the destination you need
.Destination = wdSendToNewDocument
' substitute the Excel file you need, and the query you got from step (d)
.OpenDataSource _
Name:="the full path name of your Excel file", _
.SQLStatement:="the SELECT statement you got from step (d)"
End With
End Sub
For further info. on VBA etc. see e.g.
http://word.mvps.org/FAQs/MacrosVBA/index.htm
especially
http://word.mvps.org/FAQs/MacrosVBA/CreateAMacro.htm
and
http://word.mvps.org/FAQs/MacrosVBA/DocumentEvents.htm
It is also possible that Office 2002 SP3 fixes this problem, although I do
not think it does.
Peter Jamieson
"vicchic01" wrote in message
...
I am using Word 2002 and have created a mail merged word document.
If the mail merge is not restricted (all records in the data source are
connected with the word document) then the main merge document keeps its
connection with the data source (an excel 2002 spreadsheet) every time you
open it. If, however, you apply a filter (see step 3 of the mail merge
wizard process) and save the word document, then you must find the data
source every time you open the document again. Very frustrating !
I have searched the internet for a possible solution but have not found
anything? Any ideas?