[vicchic01]

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?

[Peter Jamieson]

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?

[vicchic01]

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?

[Peter Jamieson]

Do you think I might need to create another macro for AutoClose() to
reset the SELECT statement or something?


Well, AutoClose does /a/ job, but if you do the following
Save a document (say mydoc.doc)
Save As the same document (say mydoc1.doc)
Close the document

then AutoClose only "fires" for mydoc1.doc

To catch every "Save" event you really need to use the Application Event
DocumentBeforeSave (in Word 2000 and later). It's not something I've gone
into in any depth but there's a useful page at

http://word.mvps.org/faqs/macrosvba/AppClassEvents.htm

However, because this is an /Application/ event, it will apply to every
document. Unless you want to disconnect the data source for /every/ mail
merge main document (personally, as a user I don't take kindly to having the
default behaviour of Word altered) then you really need to put something in
the document that the event handler can test to see if it should detach the
data source. It could be a document variable, something in the body of the
document, a document property etc.

I would use

Doc.MailMerge.MainDocumentType = wdNotAMergeDocument

to disconnect the data source. Although there's a DataSource.Close method,
a. you need to test that the DataSource object exists before you invoke it
(actually, making that test is another VBA question I don't know the best
answer to but I think you have to do something like

On Error Resume Next
If Not IsNull(Doc.MailMerge.DataSource) Then
Doc.MailMerge.MainDocumentType = wdNotAMergeDocument
End If

b. it's not very helpful because if you use it, then close the document,
that SQL prompt still pops up when you re-open even though the data source
is not actually re-opened.


By the way my surname is Jamieson too
... SPOOKY ... could be related ???


:-) If you or your Jamieson ancestors are from Shetland then it's possible -
you can always have a look around http://www.bayanne.info/Shetland

Peter Jamieson


"vicchic01" wrote in message
...
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?