Hello. I've read KB 825765 so I understand when and why I get prompted with
the "Opening this will run the following SQL command" message when you open
a Word mail merge main document that is linked to a data source.

However, I do not understand why I get prompted for the document after the
merge has run and my new document has been created and saved. When I close
and reopen the newly created document, I still get the prompt. There is
obviously a data attachment still existing. Is there any way to break that
attachment?

I may not be understanding clearly how the merge process worked. I thought
it brought data into the new document and that's it. Is it continuously
going to the data source to get the data?

Any help/advice would be appreciated.

Thanks,
Jennifer Dickens

Are you sure that you have actually executed the merge to a new document?

I suggest that you select Toolbars from the View menu and then check the
mailmerge item to display the mailmerge toolbar, then use the buttons on the
Right Hand end of the toolbar to execute the mailmerge.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
"Jeni Q" wrote in message
...
Hello. I've read KB 825765 so I understand when and why I get prompted
with
the "Opening this will run the following SQL command" message when you
open
a Word mail merge main document that is linked to a data source.

However, I do not understand why I get prompted for the document after the
merge has run and my new document has been created and saved. When I close
and reopen the newly created document, I still get the prompt. There is
obviously a data attachment still existing. Is there any way to break that
attachment?

I may not be understanding clearly how the merge process worked. I thought
it brought data into the new document and that's it. Is it continuously
going to the data source to get the data?

Any help/advice would be appreciated.

Thanks,
Jennifer Dickens

Yes, I'm pretty sure that the merge has been executed. Typically, when you
have not executed the merge, most of the toolbar buttons are available to
you. You can also see the field shading (I have mine set to Always). In this
completed document, there are no shaded fields appearing and the only
mailmerge toolbar buttons available are Main Document Setup and Open Data
Source. So it clearly seems to me that it has merged.

However, it's insistence on prompting to connect to the data source on open
proves otherwise. What could be keeping the connection?

Jennifer

"Doug Robbins" wrote in message
...
: Are you sure that you have actually executed the merge to a new document?
:
: I suggest that you select Toolbars from the View menu and then check the
: mailmerge item to display the mailmerge toolbar, then use the buttons on
the
: Right Hand end of the toolbar to execute the mailmerge.
:
: --
: Hope this helps.
:
: Please reply to the newsgroup unless you wish to avail yourself of my
: services on a paid consulting basis.
:
: Doug Robbins - Word MVP
: "Jeni Q" wrote in message
: ...
: Hello. I've read KB 825765 so I understand when and why I get prompted
: with
: the "Opening this will run the following SQL command" message when you
: open
: a Word mail merge main document that is linked to a data source.
:
: However, I do not understand why I get prompted for the document after
the
: merge has run and my new document has been created and saved. When I
close
: and reopen the newly created document, I still get the prompt. There is
: obviously a data attachment still existing. Is there any way to break
that
: attachment?
:
: I may not be understanding clearly how the merge process worked. I
thought
: it brought data into the new document and that's it. Is it continuously
: going to the data source to get the data?
:
: Any help/advice would be appreciated.
:
: Thanks,
: Jennifer Dickens
:
:
:
:

If you toggle on the field codes in the document (Alt+F9) what do you see?
Any mergefields?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
"Jeni Q" wrote in message
...
Yes, I'm pretty sure that the merge has been executed. Typically, when you
have not executed the merge, most of the toolbar buttons are available to
you. You can also see the field shading (I have mine set to Always). In
this
completed document, there are no shaded fields appearing and the only
mailmerge toolbar buttons available are Main Document Setup and Open Data
Source. So it clearly seems to me that it has merged.

However, it's insistence on prompting to connect to the data source on
open
proves otherwise. What could be keeping the connection?

Jennifer

"Doug Robbins" wrote in message
...
: Are you sure that you have actually executed the merge to a new
document?
:
: I suggest that you select Toolbars from the View menu and then check the
: mailmerge item to display the mailmerge toolbar, then use the buttons on
the
: Right Hand end of the toolbar to execute the mailmerge.
:
: --
: Hope this helps.
:
: Please reply to the newsgroup unless you wish to avail yourself of my
: services on a paid consulting basis.
:
: Doug Robbins - Word MVP
: "Jeni Q" wrote in message
: ...
: Hello. I've read KB 825765 so I understand when and why I get prompted
: with
: the "Opening this will run the following SQL command" message when you
: open
: a Word mail merge main document that is linked to a data source.
:
: However, I do not understand why I get prompted for the document after
the
: merge has run and my new document has been created and saved. When I
close
: and reopen the newly created document, I still get the prompt. There
is
: obviously a data attachment still existing. Is there any way to break
that
: attachment?
:
: I may not be understanding clearly how the merge process worked. I
thought
: it brought data into the new document and that's it. Is it
continuously
: going to the data source to get the data?
:
: Any help/advice would be appreciated.
:
: Thanks,
: Jennifer Dickens
:
:
:
:

In the MS Help, it says the following:
Restore a mail merge main document to a regular document
By removing the associated data source from a main document, you can
convert it to a regular document.

1.. Open the main document from which you want to remove the data
source.
2.. On the View menu, point to Toolbars, and then click Mail Merge.
3.. Click Main Document Setup .
4.. Click Normal Word document.


OK, so I tried this on one of these completed documents and the radio button
is already set to Normal Word Document.

It may be helpful to explain what I'm doing. I have a letter that has form
fields in it. These fields relate back to an excel spreadsheet. When the
user wants to create a new letter, he updates the fields in the spreadsheet.
He is typically creating one letter at a time, not many. The fields are
manually entered into the spreadsheet in one location, and they are copied
from that location to a temporary place in the spreadsheet. That way he can
keep a record of the letters he's sent, but has a separate page in which he
can paste one row of data for a single merge. The Word template is
associated to the data, and the location of the data always remains the
same.

It must be something about the template, the way it stores the connection to
the data fields...


"Jeni Q" wrote in message
...
: Hello. I've read KB 825765 so I understand when and why I get prompted
with
: the "Opening this will run the following SQL command" message when you
open
: a Word mail merge main document that is linked to a data source.
:
: However, I do not understand why I get prompted for the document after the
: merge has run and my new document has been created and saved. When I close
: and reopen the newly created document, I still get the prompt. There is
: obviously a data attachment still existing. Is there any way to break that
: attachment?
:
: I may not be understanding clearly how the merge process worked. I thought
: it brought data into the new document and that's it. Is it continuously
: going to the data source to get the data?
:
: Any help/advice would be appreciated.
:
: Thanks,
: Jennifer Dickens
:
:

There are better ways of doing what the user wants. One is contained in the
last of the following series of articles:

Please Fill Out This Form
Part 1: Create professional looking forms in Word
http://www.computorcompanion.com/LPMArticle.asp?ID=22

Part 2: Adding Automation to your Word forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=46

Part 3: Learn more VBA (macros) to automate your forms.
http://www.computorcompanion.com/LPMArticle.asp?ID=119

Part 4: Use custom dialog boxes in your Word forms
http://www.computorcompanion.com/LPMArticle.asp?ID=127

Part 5: Connect your AutoForm to a database to save input time and keep
better records!
http://www.computorcompanion.com/LPMArticle.asp?ID=136


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP
"Jeni Q" wrote in message
...
In the MS Help, it says the following:
Restore a mail merge main document to a regular document
By removing the associated data source from a main document, you can
convert it to a regular document.

1.. Open the main document from which you want to remove the data
source.
2.. On the View menu, point to Toolbars, and then click Mail Merge.
3.. Click Main Document Setup .
4.. Click Normal Word document.


OK, so I tried this on one of these completed documents and the radio
button
is already set to Normal Word Document.

It may be helpful to explain what I'm doing. I have a letter that has form
fields in it. These fields relate back to an excel spreadsheet. When the
user wants to create a new letter, he updates the fields in the
spreadsheet.
He is typically creating one letter at a time, not many. The fields are
manually entered into the spreadsheet in one location, and they are copied
from that location to a temporary place in the spreadsheet. That way he
can
keep a record of the letters he's sent, but has a separate page in which
he
can paste one row of data for a single merge. The Word template is
associated to the data, and the location of the data always remains the
same.

It must be something about the template, the way it stores the connection
to
the data fields...


"Jeni Q" wrote in message
...
: Hello. I've read KB 825765 so I understand when and why I get prompted
with
: the "Opening this will run the following SQL command" message when you
open
: a Word mail merge main document that is linked to a data source.
:
: However, I do not understand why I get prompted for the document after
the
: merge has run and my new document has been created and saved. When I
close
: and reopen the newly created document, I still get the prompt. There is
: obviously a data attachment still existing. Is there any way to break
that
: attachment?
:
: I may not be understanding clearly how the merge process worked. I
thought
: it brought data into the new document and that's it. Is it continuously
: going to the data source to get the data?
:
: Any help/advice would be appreciated.
:
: Thanks,
: Jennifer Dickens
:
:

Is your document based on (associated with) a template (.dot) that is
connected to a data source? If so, you might recieve this message in recent
versions of Word because when the template opens, it tries to connect to the
data source.

Peter Jamieson

"Jeni Q" wrote in message
...
Yes, I'm pretty sure that the merge has been executed. Typically, when you
have not executed the merge, most of the toolbar buttons are available to
you. You can also see the field shading (I have mine set to Always). In
this
completed document, there are no shaded fields appearing and the only
mailmerge toolbar buttons available are Main Document Setup and Open Data
Source. So it clearly seems to me that it has merged.

However, it's insistence on prompting to connect to the data source on
open
proves otherwise. What could be keeping the connection?

Jennifer

"Doug Robbins" wrote in message
...
: Are you sure that you have actually executed the merge to a new
document?
:
: I suggest that you select Toolbars from the View menu and then check the
: mailmerge item to display the mailmerge toolbar, then use the buttons on
the
: Right Hand end of the toolbar to execute the mailmerge.
:
: --
: Hope this helps.
:
: Please reply to the newsgroup unless you wish to avail yourself of my
: services on a paid consulting basis.
:
: Doug Robbins - Word MVP
: "Jeni Q" wrote in message
: ...
: Hello. I've read KB 825765 so I understand when and why I get prompted
: with
: the "Opening this will run the following SQL command" message when you
: open
: a Word mail merge main document that is linked to a data source.
:
: However, I do not understand why I get prompted for the document after
the
: merge has run and my new document has been created and saved. When I
close
: and reopen the newly created document, I still get the prompt. There
is
: obviously a data attachment still existing. Is there any way to break
that
: attachment?
:
: I may not be understanding clearly how the merge process worked. I
thought
: it brought data into the new document and that's it. Is it
continuously
: going to the data source to get the data?
:
: Any help/advice would be appreciated.
:
: Thanks,
: Jennifer Dickens
:
:
:
:

I think this must be what's happening. The weird thing is that one time I
got it to work right. But I can't remember how. :-O
"Peter Jamieson" wrote in message
...
: Is your document based on (associated with) a template (.dot) that is
: connected to a data source? If so, you might recieve this message in
recent
: versions of Word because when the template opens, it tries to connect to
the
: data source.
:
: Peter Jamieson
:
: "Jeni Q" wrote in message
: ...
: Yes, I'm pretty sure that the merge has been executed. Typically, when
you
: have not executed the merge, most of the toolbar buttons are available
to
: you. You can also see the field shading (I have mine set to Always). In
: this
: completed document, there are no shaded fields appearing and the only
: mailmerge toolbar buttons available are Main Document Setup and Open
Data
: Source. So it clearly seems to me that it has merged.
:
: However, it's insistence on prompting to connect to the data source on
: open
: proves otherwise. What could be keeping the connection?
:
: Jennifer
:
: "Doug Robbins" wrote in message
: ...
: : Are you sure that you have actually executed the merge to a new
: document?
: :
: : I suggest that you select Toolbars from the View menu and then check
the
: : mailmerge item to display the mailmerge toolbar, then use the buttons
on
: the
: : Right Hand end of the toolbar to execute the mailmerge.
: :
: : --
: : Hope this helps.
: :
: : Please reply to the newsgroup unless you wish to avail yourself of my
: : services on a paid consulting basis.
: :
: : Doug Robbins - Word MVP
: : "Jeni Q" wrote in message
: : ...
: : Hello. I've read KB 825765 so I understand when and why I get
prompted
: : with
: : the "Opening this will run the following SQL command" message when
you
: : open
: : a Word mail merge main document that is linked to a data source.
: :
: : However, I do not understand why I get prompted for the document
after
: the
: : merge has run and my new document has been created and saved. When I
: close
: : and reopen the newly created document, I still get the prompt. There
: is
: : obviously a data attachment still existing. Is there any way to
break
: that
: : attachment?
: :
: : I may not be understanding clearly how the merge process worked. I
: thought
: : it brought data into the new document and that's it. Is it
: continuously
: : going to the data source to get the data?
: :
: : Any help/advice would be appreciated.
: :
: : Thanks,
: : Jennifer Dickens
: :
: :
: :
: :
:
:
:
:

Don't know if that is what has happened, but if it is and you do not need
your mail merge main document to be connected to this template, disconnect
it, or alternatively, disconnect the template and set up the connection in a
VBA AutoNew macro if you know how to do that. In many cases, the sensible
thing to do when dealing with mailmerge is to develop your mail merge main
document, connect it to its data source, associate it with the normal.dot
template, then save it, and avoid using a special template - it's not how
Word is really supposed to work, but it probably causes fewer problems with
mailmerge, unless, for example, you need to use fields such as ASK, FILLIN,
and (especially) CREATEDATE, which tend to work differently when you create
a new document from a template.

recieve

can't believe I used that spelling, even by accident!

Peter Jamieson
"Jeni Q" wrote in message
...
I think this must be what's happening. The weird thing is that one time I
got it to work right. But I can't remember how. :-O
"Peter Jamieson" wrote in message
...
: Is your document based on (associated with) a template (.dot) that is
: connected to a data source? If so, you might recieve this message in
recent
: versions of Word because when the template opens, it tries to connect to
the
: data source.
:
: Peter Jamieson
:
: "Jeni Q" wrote in message
: ...
: Yes, I'm pretty sure that the merge has been executed. Typically, when
you
: have not executed the merge, most of the toolbar buttons are available
to
: you. You can also see the field shading (I have mine set to Always).
In
: this
: completed document, there are no shaded fields appearing and the only
: mailmerge toolbar buttons available are Main Document Setup and Open
Data
: Source. So it clearly seems to me that it has merged.
:
: However, it's insistence on prompting to connect to the data source on
: open
: proves otherwise. What could be keeping the connection?
:
: Jennifer
:
: "Doug Robbins" wrote in message
: ...
: : Are you sure that you have actually executed the merge to a new
: document?
: :
: : I suggest that you select Toolbars from the View menu and then check
the
: : mailmerge item to display the mailmerge toolbar, then use the
buttons
on
: the
: : Right Hand end of the toolbar to execute the mailmerge.
: :
: : --
: : Hope this helps.
: :
: : Please reply to the newsgroup unless you wish to avail yourself of
my
: : services on a paid consulting basis.
: :
: : Doug Robbins - Word MVP
: : "Jeni Q" wrote in message
: : ...
: : Hello. I've read KB 825765 so I understand when and why I get
prompted
: : with
: : the "Opening this will run the following SQL command" message when
you
: : open
: : a Word mail merge main document that is linked to a data source.
: :
: : However, I do not understand why I get prompted for the document
after
: the
: : merge has run and my new document has been created and saved. When
I
: close
: : and reopen the newly created document, I still get the prompt.
There
: is
: : obviously a data attachment still existing. Is there any way to
break
: that
: : attachment?
: :
: : I may not be understanding clearly how the merge process worked. I
: thought
: : it brought data into the new document and that's it. Is it
: continuously
: : going to the data source to get the data?
: :
: : Any help/advice would be appreciated.
: :
: : Thanks,
: : Jennifer Dickens
: :
: :
: :
: :
:
:
:
:

I really do need the mail merge to be connected to the template. That's the
whole point of this exercise. My user has a letter that he has to complete
several times a day, but never in a big batch job. It's always a single
letter at a time. The text is the same on the letter with the exception of a
few fields such as name, title, university, etc.

Previously in Word 2000, I created a template with 2 macros and several data
fields in it. The first macro is an AUTONEW macro that inserts a calculated
date at a bookmarked location. The date is based on the CREATEDATE which is
also inserted when the template is opened. The second macro (MergeFromExcel)
is a very simple recorded macro which consists of opening the data source,
finding the named range, and merging the data. The data fields are in the
template already, so the macro runs through and dumps the data into the
document, leaving me with a completed merged document that has a createdate,
a calculated date, and my 6 fields pulled from the excel spreadsheet.

We have just upgraded to 2003 recently. So with this template, everything is
working OK now but two things. One is that when I run the MergeFromExcel
macro, I get prompted to select the table. This was previously included in
the macro. This I can fix by rerecording the macro or editing it if I'm
really clever. (I don't know any VBA, which limits my abilities.) The second
problem is a larger problem. The completed merged documents seem to be
retaining both their macros and their database connection. So when I open
one of the completed documents, I'm promted to enable/disable macros and I'm
also prompted with the SQL warning.

What's really weird about Word 2003 is that it seems to remember some of the
data connections, sometimes, but it's not consistent. What's up with that?

So, anyway, it seems to me that I do need the merge associated with the
special template. There may be a real obvious answer to this problem that
I'm missing. Any advice?



"Peter Jamieson" wrote in message
...
: Don't know if that is what has happened, but if it is and you do not need
: your mail merge main document to be connected to this template, disconnect
: it, or alternatively, disconnect the template and set up the connection in
a
: VBA AutoNew macro if you know how to do that. In many cases, the sensible
: thing to do when dealing with mailmerge is to develop your mail merge main
: document, connect it to its data source, associate it with the normal.dot
: template, then save it, and avoid using a special template - it's not how
: Word is really supposed to work, but it probably causes fewer problems
with
: mailmerge, unless, for example, you need to use fields such as ASK,
FILLIN,
: and (especially) CREATEDATE, which tend to work differently when you
create
: a new document from a template.
:
: recieve
:
: can't believe I used that spelling, even by accident!
:
: Peter Jamieson
: "Jeni Q" wrote in message
: ...
: I think this must be what's happening. The weird thing is that one time I
: got it to work right. But I can't remember how. :-O
: "Peter Jamieson" wrote in message
: ...
: : Is your document based on (associated with) a template (.dot) that is
: : connected to a data source? If so, you might recieve this message in
: recent
: : versions of Word because when the template opens, it tries to connect
to
: the
: : data source.
: :
: : Peter Jamieson
: :
: : "Jeni Q" wrote in message
: : ...
: : Yes, I'm pretty sure that the merge has been executed. Typically,
when
: you
: : have not executed the merge, most of the toolbar buttons are
available
: to
: : you. You can also see the field shading (I have mine set to Always).

: In
: : this
: : completed document, there are no shaded fields appearing and the
only
: : mailmerge toolbar buttons available are Main Document Setup and Open
: Data
: : Source. So it clearly seems to me that it has merged.
: :
: : However, it's insistence on prompting to connect to the data source
on
: : open
: : proves otherwise. What could be keeping the connection?
: :
: : Jennifer
: :
: : "Doug Robbins" wrote in message
: : ...
: : : Are you sure that you have actually executed the merge to a new
: : document?
: : :
: : : I suggest that you select Toolbars from the View menu and then
check
: the
: : : mailmerge item to display the mailmerge toolbar, then use the
: buttons
: on
: : the
: : : Right Hand end of the toolbar to execute the mailmerge.
: : :
: : : --
: : : Hope this helps.
: : :
: : : Please reply to the newsgroup unless you wish to avail yourself of
: my
: : : services on a paid consulting basis.
: : :
: : : Doug Robbins - Word MVP
: : : "Jeni Q" wrote in message
: : : ...
: : : Hello. I've read KB 825765 so I understand when and why I get
: prompted
: : : with
: : : the "Opening this will run the following SQL command" message
when
: you
: : : open
: : : a Word mail merge main document that is linked to a data source.
: : :
: : : However, I do not understand why I get prompted for the document
: after
: : the
: : : merge has run and my new document has been created and saved.
When
: I
: : close
: : : and reopen the newly created document, I still get the prompt.
: There
: : is
: : : obviously a data attachment still existing. Is there any way to
: break
: : that
: : : attachment?
: : :
: : : I may not be understanding clearly how the merge process worked.
I
: : thought
: : : it brought data into the new document and that's it. Is it
: : continuously
: : : going to the data source to get the data?
: : :
: : : Any help/advice would be appreciated.
: : :
: : : Thanks,
: : : Jennifer Dickens
: : :
: : :
: : :
: : :
: :
: :
: :
: :
:
:
:
:

We're using Excel, not Access. Will this work the same? Do I use this
library for the connection: Microsoft ActiveX Data Object 2.5 Library?

Also, I do not want to automatically input data from my form into the
database. I only want the data to come from the spreadsheet into the letter.
Does the example to which you pointed me allow me to have this sort of
one-way connection?

Thanks,
Jennifer


"Doug Robbins" wrote in message
...
: There are better ways of doing what the user wants. One is contained in
the
: last of the following series of articles:
:
: Please Fill Out This Form
: Part 1: Create professional looking forms in Word
: http://www.computorcompanion.com/LPMArticle.asp?ID=22
:
: Part 2: Adding Automation to your Word forms.
: http://www.computorcompanion.com/LPMArticle.asp?ID=46
:
: Part 3: Learn more VBA (macros) to automate your forms.
: http://www.computorcompanion.com/LPMArticle.asp?ID=119
:
: Part 4: Use custom dialog boxes in your Word forms
: http://www.computorcompanion.com/LPMArticle.asp?ID=127
:
: Part 5: Connect your AutoForm to a database to save input time and keep
: better records!
: http://www.computorcompanion.com/LPMArticle.asp?ID=136
:
:
: --
: Hope this helps.
:
: Please reply to the newsgroup unless you wish to avail yourself of my
: services on a paid consulting basis.
:
: Doug Robbins - Word MVP
: "Jeni Q" wrote in message
: ...
: In the MS Help, it says the following:
: Restore a mail merge main document to a regular document
: By removing the associated data source from a main document, you
can
: convert it to a regular document.
:
: 1.. Open the main document from which you want to remove the data
: source.
: 2.. On the View menu, point to Toolbars, and then click Mail
Merge.
: 3.. Click Main Document Setup .
: 4.. Click Normal Word document.
:
:
: OK, so I tried this on one of these completed documents and the radio
: button
: is already set to Normal Word Document.
:
: It may be helpful to explain what I'm doing. I have a letter that has
form
: fields in it. These fields relate back to an excel spreadsheet. When the
: user wants to create a new letter, he updates the fields in the
: spreadsheet.
: He is typically creating one letter at a time, not many. The fields are
: manually entered into the spreadsheet in one location, and they are
copied
: from that location to a temporary place in the spreadsheet. That way he
: can
: keep a record of the letters he's sent, but has a separate page in which
: he
: can paste one row of data for a single merge. The Word template is
: associated to the data, and the location of the data always remains the
: same.
:
: It must be something about the template, the way it stores the
connection
: to
: the data fields...
:
:
: "Jeni Q" wrote in message
: ...
: : Hello. I've read KB 825765 so I understand when and why I get prompted
: with
: : the "Opening this will run the following SQL command" message when you
: open
: : a Word mail merge main document that is linked to a data source.
: :
: : However, I do not understand why I get prompted for the document after
: the
: : merge has run and my new document has been created and saved. When I
: close
: : and reopen the newly created document, I still get the prompt. There
is
: : obviously a data attachment still existing. Is there any way to break
: that
: : attachment?
: :
: : I may not be understanding clearly how the merge process worked. I
: thought
: : it brought data into the new document and that's it. Is it
continuously
: : going to the data source to get the data?
: :
: : Any help/advice would be appreciated.
: :
: : Thanks,
: : Jennifer Dickens
: :
: :
:
:
:
:
:

First, if you open the Template (e.g. using Word File|Open, rather than
creating a new document based on it), is it connected to the data source?
(e.g. if you display the MailMerge toolbar using Tools|Customize,
a. are most/all the buttons after the first two greyed out?
b. if you click the first button, what type of document is selected?)

Second...

One is that when I run the MergeFromExcel
macro, I get prompted to select the table. This was previously included in
the macro.

....does your macro issue an OpenDataSource call? If so, can you copy/paste
the code for that call into a message in this thread?

The completed merged documents seem to be
retaining both their macros

I'll look at this separately.

Peter Jamieson
"Jeni Q" wrote in message
...
I really do need the mail merge to be connected to the template. That's the
whole point of this exercise. My user has a letter that he has to complete
several times a day, but never in a big batch job. It's always a single
letter at a time. The text is the same on the letter with the exception of
a
few fields such as name, title, university, etc.

Previously in Word 2000, I created a template with 2 macros and several
data
fields in it. The first macro is an AUTONEW macro that inserts a
calculated
date at a bookmarked location. The date is based on the CREATEDATE which
is
also inserted when the template is opened. The second macro
(MergeFromExcel)
is a very simple recorded macro which consists of opening the data source,
finding the named range, and merging the data. The data fields are in the
template already, so the macro runs through and dumps the data into the
document, leaving me with a completed merged document that has a
createdate,
a calculated date, and my 6 fields pulled from the excel spreadsheet.

We have just upgraded to 2003 recently. So with this template, everything
is
working OK now but two things. One is that when I run the MergeFromExcel
macro, I get prompted to select the table. This was previously included in
the macro. This I can fix by rerecording the macro or editing it if I'm
really clever. (I don't know any VBA, which limits my abilities.) The
second
problem is a larger problem. The completed merged documents seem to be
retaining both their macros and their database connection. So when I open
one of the completed documents, I'm promted to enable/disable macros and
I'm
also prompted with the SQL warning.

What's really weird about Word 2003 is that it seems to remember some of
the
data connections, sometimes, but it's not consistent. What's up with that?

So, anyway, it seems to me that I do need the merge associated with the
special template. There may be a real obvious answer to this problem that
I'm missing. Any advice?



"Peter Jamieson" wrote in message
...
: Don't know if that is what has happened, but if it is and you do not
need
: your mail merge main document to be connected to this template,
disconnect
: it, or alternatively, disconnect the template and set up the connection
in
a
: VBA AutoNew macro if you know how to do that. In many cases, the
sensible
: thing to do when dealing with mailmerge is to develop your mail merge
main
: document, connect it to its data source, associate it with the
normal.dot
: template, then save it, and avoid using a special template - it's not
how
: Word is really supposed to work, but it probably causes fewer problems
with
: mailmerge, unless, for example, you need to use fields such as ASK,
FILLIN,
: and (especially) CREATEDATE, which tend to work differently when you
create
: a new document from a template.
:
: recieve
:
: can't believe I used that spelling, even by accident!
:
: Peter Jamieson
: "Jeni Q" wrote in message
: ...
: I think this must be what's happening. The weird thing is that one time
I
: got it to work right. But I can't remember how. :-O
: "Peter Jamieson" wrote in message
: ...
: : Is your document based on (associated with) a template (.dot) that
is
: : connected to a data source? If so, you might recieve this message in
: recent
: : versions of Word because when the template opens, it tries to
connect
to
: the
: : data source.
: :
: : Peter Jamieson
: :
: : "Jeni Q" wrote in message
: : ...
: : Yes, I'm pretty sure that the merge has been executed. Typically,
when
: you
: : have not executed the merge, most of the toolbar buttons are
available
: to
: : you. You can also see the field shading (I have mine set to
Always).

: In
: : this
: : completed document, there are no shaded fields appearing and the
only
: : mailmerge toolbar buttons available are Main Document Setup and
Open
: Data
: : Source. So it clearly seems to me that it has merged.
: :
: : However, it's insistence on prompting to connect to the data
source
on
: : open
: : proves otherwise. What could be keeping the connection?
: :
: : Jennifer
: :
: : "Doug Robbins" wrote in message
: : ...
: : : Are you sure that you have actually executed the merge to a new
: : document?
: : :
: : : I suggest that you select Toolbars from the View menu and then
check
: the
: : : mailmerge item to display the mailmerge toolbar, then use the
: buttons
: on
: : the
: : : Right Hand end of the toolbar to execute the mailmerge.
: : :
: : : --
: : : Hope this helps.
: : :
: : : Please reply to the newsgroup unless you wish to avail yourself
of
: my
: : : services on a paid consulting basis.
: : :
: : : Doug Robbins - Word MVP
: : : "Jeni Q" wrote in message
: : : ...
: : : Hello. I've read KB 825765 so I understand when and why I get
: prompted
: : : with
: : : the "Opening this will run the following SQL command" message
when
: you
: : : open
: : : a Word mail merge main document that is linked to a data
source.
: : :
: : : However, I do not understand why I get prompted for the
document
: after
: : the
: : : merge has run and my new document has been created and saved.
When
: I
: : close
: : : and reopen the newly created document, I still get the prompt.
: There
: : is
: : : obviously a data attachment still existing. Is there any way
to
: break
: : that
: : : attachment?
: : :
: : : I may not be understanding clearly how the merge process
worked.
I
: : thought
: : : it brought data into the new document and that's it. Is it
: : continuously
: : : going to the data source to get the data?
: : :
: : : Any help/advice would be appreciated.
: : :
: : : Thanks,
: : : Jennifer Dickens
: : :
: : :
: : :
: : :
: :
: :
: :
: :
:
:
:
:

"Peter Jamieson" wrote in message
...
: First, if you open the Template (e.g. using Word File|Open, rather than
: creating a new document based on it), is it connected to the data source?
: (e.g. if you display the MailMerge toolbar using Tools|Customize,
: a. are most/all the buttons after the first two greyed out?
: b. if you click the first button, what type of document is selected?)

If I open the template (rather than creating a new doc based on the
template) I am prompted with the SQL prompt.
If I choose No, then all the buttons but the first two are grayed out and
Normal Word Doc is selected as the Main Document Type.
If I choose Yes, then all the buttons are available and Letters is selected
as the Main Document Type.

(Good questions!)

: Second...
:
: One is that when I run the MergeFromExcel
: macro, I get prompted to select the table. This was previously included
in
: the macro.
:
: ...does your macro issue an OpenDataSource call? If so, can you copy/paste
: the code for that call into a message in this thread?

I don't know. Below is the code in the Macro (both macros, actually). It's a
recorded macro, nothing fancy. I figured I could re-record the macro to fix
this problem. It's the problem mentioned below that I don't know how to
fix.

Sub AutoNew()
'
' AutoNew Macro
' Macro created 12/18/2003 by Jennifer Q Dickens
'
Selection.GoTo What:=wdGoToBookmark, Name:="DueDate"
Selection.InsertBefore Format((Date + 14), "d MMMM, yyyy")
End Sub

Sub MergeFromExcel()
'
' MergeFromExcel Macro
' Macro recorded 2/19/2004 by Scot J dobson
'
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"K:\AA documents\SRC IP Tracking spreadsheet.xls",
ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False,
_
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
Connection:="CurrentData", SQLStatement:="", SQLStatement1:=""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
End Sub


: The completed merged documents seem to be
: retaining both their macros
:
: I'll look at this separately.

I think I love you. Thanks.

: Peter Jamieson
: "Jeni Q" wrote in message
: ...
: I really do need the mail merge to be connected to the template. That's
the
: whole point of this exercise. My user has a letter that he has to
complete
: several times a day, but never in a big batch job. It's always a single
: letter at a time. The text is the same on the letter with the exception
of
: a
: few fields such as name, title, university, etc.
:
: Previously in Word 2000, I created a template with 2 macros and several
: data
: fields in it. The first macro is an AUTONEW macro that inserts a
: calculated
: date at a bookmarked location. The date is based on the CREATEDATE which
: is
: also inserted when the template is opened. The second macro
: (MergeFromExcel)
: is a very simple recorded macro which consists of opening the data
source,
: finding the named range, and merging the data. The data fields are in
the
: template already, so the macro runs through and dumps the data into the
: document, leaving me with a completed merged document that has a
: createdate,
: a calculated date, and my 6 fields pulled from the excel spreadsheet.
:
: We have just upgraded to 2003 recently. So with this template,
everything
: is
: working OK now but two things. One is that when I run the MergeFromExcel
: macro, I get prompted to select the table. This was previously included
in
: the macro. This I can fix by rerecording the macro or editing it if I'm
: really clever. (I don't know any VBA, which limits my abilities.) The
: second
: problem is a larger problem. The completed merged documents seem to be
: retaining both their macros and their database connection. So when I
open
: one of the completed documents, I'm promted to enable/disable macros and
: I'm
: also prompted with the SQL warning.
:
: What's really weird about Word 2003 is that it seems to remember some of
: the
: data connections, sometimes, but it's not consistent. What's up with
that?
:
: So, anyway, it seems to me that I do need the merge associated with the
: special template. There may be a real obvious answer to this problem
that
: I'm missing. Any advice?
:
:
:
: "Peter Jamieson" wrote in message
: ...
: : Don't know if that is what has happened, but if it is and you do not
: need
: : your mail merge main document to be connected to this template,
: disconnect
: : it, or alternatively, disconnect the template and set up the
connection
: in
: a
: : VBA AutoNew macro if you know how to do that. In many cases, the
: sensible
: : thing to do when dealing with mailmerge is to develop your mail merge
: main
: : document, connect it to its data source, associate it with the
: normal.dot
: : template, then save it, and avoid using a special template - it's not
: how
: : Word is really supposed to work, but it probably causes fewer problems
: with
: : mailmerge, unless, for example, you need to use fields such as ASK,
: FILLIN,
: : and (especially) CREATEDATE, which tend to work differently when you
: create
: : a new document from a template.
: :
: : recieve
: :
: : can't believe I used that spelling, even by accident!
: :
: : Peter Jamieson
: : "Jeni Q" wrote in message
: : ...
: : I think this must be what's happening. The weird thing is that one
time
: I
: : got it to work right. But I can't remember how. :-O
: : "Peter Jamieson" wrote in message
: : ...
: : : Is your document based on (associated with) a template (.dot) that
: is
: : : connected to a data source? If so, you might recieve this message
in
: : recent
: : : versions of Word because when the template opens, it tries to
: connect
: to
: : the
: : : data source.
: : :
: : : Peter Jamieson
: : :
: : : "Jeni Q" wrote in message
: : : ...
: : : Yes, I'm pretty sure that the merge has been executed.
Typically,
: when
: : you
: : : have not executed the merge, most of the toolbar buttons are
: available
: : to
: : : you. You can also see the field shading (I have mine set to
: Always).
:
: : In
: : : this
: : : completed document, there are no shaded fields appearing and the
: only
: : : mailmerge toolbar buttons available are Main Document Setup and
: Open
: : Data
: : : Source. So it clearly seems to me that it has merged.
: : :
: : : However, it's insistence on prompting to connect to the data
: source
: on
: : : open
: : : proves otherwise. What could be keeping the connection?
: : :
: : : Jennifer
: : :
: : : "Doug Robbins" wrote in message
: : : ...
: : : : Are you sure that you have actually executed the merge to a
new
: : : document?
: : : :
: : : : I suggest that you select Toolbars from the View menu and then
: check
: : the
: : : : mailmerge item to display the mailmerge toolbar, then use the
: : buttons
: : on
: : : the
: : : : Right Hand end of the toolbar to execute the mailmerge.
: : : :
: : : : --
: : : : Hope this helps.
: : : :
: : : : Please reply to the newsgroup unless you wish to avail
yourself
: of
: : my
: : : : services on a paid consulting basis.
: : : :
: : : : Doug Robbins - Word MVP
: : : : "Jeni Q" wrote in message
: : : : ...
: : : : Hello. I've read KB 825765 so I understand when and why I
get
: : prompted
: : : : with
: : : : the "Opening this will run the following SQL command"
message
: when
: : you
: : : : open
: : : : a Word mail merge main document that is linked to a data
: source.
: : : :
: : : : However, I do not understand why I get prompted for the
: document
: : after
: : : the
: : : : merge has run and my new document has been created and
saved.
: When
: : I
: : : close
: : : : and reopen the newly created document, I still get the
prompt.
: : There
: : : is
: : : : obviously a data attachment still existing. Is there any way
: to
: : break
: : : that
: : : : attachment?
: : : :
: : : : I may not be understanding clearly how the merge process
: worked.
: I
: : : thought
: : : : it brought data into the new document and that's it. Is it
: : : continuously
: : : : going to the data source to get the data?
: : : :
: : : : Any help/advice would be appreciated.
: : : :
: : : : Thanks,
: : : : Jennifer Dickens
: : : :
: : : :
: : : :
: : : :
: : :
: : :
: : :
: : :
: :
: :
: :
: :
:
:
:
:

If I open the template (rather than creating a new doc based on the
template) I am prompted with the SQL prompt.

OK, in that case I suggest you open the template, go through your connection
process, then click the first button, select the "Normal Word Document"
option, and save the template. At least that should stop the /template/ from
trying to connect when it loads.

I don't know. Below is the code in the Macro (both macros, actually). It's
a
recorded macro, nothing fancy. I figured I could re-record the macro to
fix
this problem.

Always worth a try - unfortunately I don't think re-recording will help as
Word records this stuff very badly.

It's the problem mentioned below that I don't know how to
fix.

OK, the key statement is this one - I'll just chop out stuff I'm pretty sure
is not needed for starters:

ActiveDocument.MailMerge.OpenDataSource _
Name:="K:\AA documents\SRC IP Tracking spreadsheet.xls", _
Connection:="CurrentData", _
SQLStatement:=""

The main questions are
a. does this need to work in Word 2000 as well as Word 2003 - if so, is it
easy for you to maintain two different versions? Or does it just need to
work in Word 2003?
b. are you connecting to a named range called CurrentData? Is it in the
first sheet in your Excel workbook?

: The completed merged documents seem to be
: retaining both their macros
:
: I'll look at this separately.

....eventually :-)

Peter Jamieson

"Jeni Q" wrote in message
...

"Peter Jamieson" wrote in message
...
: First, if you open the Template (e.g. using Word File|Open, rather than
: creating a new document based on it), is it connected to the data
source?
: (e.g. if you display the MailMerge toolbar using Tools|Customize,
: a. are most/all the buttons after the first two greyed out?
: b. if you click the first button, what type of document is selected?)

If I open the template (rather than creating a new doc based on the
template) I am prompted with the SQL prompt.
If I choose No, then all the buttons but the first two are grayed out and
Normal Word Doc is selected as the Main Document Type.
If I choose Yes, then all the buttons are available and Letters is
selected
as the Main Document Type.

(Good questions!)

: Second...
:
: One is that when I run the MergeFromExcel
: macro, I get prompted to select the table. This was previously
included
in
: the macro.
:
: ...does your macro issue an OpenDataSource call? If so, can you
copy/paste
: the code for that call into a message in this thread?

I don't know. Below is the code in the Macro (both macros, actually). It's
a
recorded macro, nothing fancy. I figured I could re-record the macro to
fix
this problem. It's the problem mentioned below that I don't know how to
fix.

Sub AutoNew()
'
' AutoNew Macro
' Macro created 12/18/2003 by Jennifer Q Dickens
'
Selection.GoTo What:=wdGoToBookmark, Name:="DueDate"
Selection.InsertBefore Format((Date + 14), "d MMMM, yyyy")
End Sub

Sub MergeFromExcel()
'
' MergeFromExcel Macro
' Macro recorded 2/19/2004 by Scot J dobson
'
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"K:\AA documents\SRC IP Tracking spreadsheet.xls",
ConfirmConversions:= _
False, ReadOnly:=False, LinkToSource:=True,
AddToRecentFiles:=False,
_
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto,
_
Connection:="CurrentData", SQLStatement:="", SQLStatement1:=""
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
End Sub


: The completed merged documents seem to be
: retaining both their macros
:
: I'll look at this separately.

I think I love you. Thanks.

: Peter Jamieson
: "Jeni Q" wrote in message
: ...
: I really do need the mail merge to be connected to the template. That's
the
: whole point of this exercise. My user has a letter that he has to
complete
: several times a day, but never in a big batch job. It's always a
single
: letter at a time. The text is the same on the letter with the
exception
of
: a
: few fields such as name, title, university, etc.
:
: Previously in Word 2000, I created a template with 2 macros and
several
: data
: fields in it. The first macro is an AUTONEW macro that inserts a
: calculated
: date at a bookmarked location. The date is based on the CREATEDATE
which
: is
: also inserted when the template is opened. The second macro
: (MergeFromExcel)
: is a very simple recorded macro which consists of opening the data
source,
: finding the named range, and merging the data. The data fields are in
the
: template already, so the macro runs through and dumps the data into
the
: document, leaving me with a completed merged document that has a
: createdate,
: a calculated date, and my 6 fields pulled from the excel spreadsheet.
:
: We have just upgraded to 2003 recently. So with this template,
everything
: is
: working OK now but two things. One is that when I run the
MergeFromExcel
: macro, I get prompted to select the table. This was previously
included
in
: the macro. This I can fix by rerecording the macro or editing it if
I'm
: really clever. (I don't know any VBA, which limits my abilities.) The
: second
: problem is a larger problem. The completed merged documents seem to be
: retaining both their macros and their database connection. So when I
open
: one of the completed documents, I'm promted to enable/disable macros
and
: I'm
: also prompted with the SQL warning.
:
: What's really weird about Word 2003 is that it seems to remember some
of
: the
: data connections, sometimes, but it's not consistent. What's up with
that?
:
: So, anyway, it seems to me that I do need the merge associated with
the
: special template. There may be a real obvious answer to this problem
that
: I'm missing. Any advice?
:
:
:
: "Peter Jamieson" wrote in message
: ...
: : Don't know if that is what has happened, but if it is and you do not
: need
: : your mail merge main document to be connected to this template,
: disconnect
: : it, or alternatively, disconnect the template and set up the
connection
: in
: a
: : VBA AutoNew macro if you know how to do that. In many cases, the
: sensible
: : thing to do when dealing with mailmerge is to develop your mail
merge
: main
: : document, connect it to its data source, associate it with the
: normal.dot
: : template, then save it, and avoid using a special template - it's
not
: how
: : Word is really supposed to work, but it probably causes fewer
problems
: with
: : mailmerge, unless, for example, you need to use fields such as ASK,
: FILLIN,
: : and (especially) CREATEDATE, which tend to work differently when you
: create
: : a new document from a template.
: :
: : recieve
: :
: : can't believe I used that spelling, even by accident!
: :
: : Peter Jamieson
: : "Jeni Q" wrote in message
: : ...
: : I think this must be what's happening. The weird thing is that one
time
: I
: : got it to work right. But I can't remember how. :-O
: : "Peter Jamieson" wrote in message
: : ...
: : : Is your document based on (associated with) a template (.dot)
that
: is
: : : connected to a data source? If so, you might recieve this
message
in
: : recent
: : : versions of Word because when the template opens, it tries to
: connect
: to
: : the
: : : data source.
: : :
: : : Peter Jamieson
: : :
: : : "Jeni Q" wrote in message
: : : ...
: : : Yes, I'm pretty sure that the merge has been executed.
Typically,
: when
: : you
: : : have not executed the merge, most of the toolbar buttons are
: available
: : to
: : : you. You can also see the field shading (I have mine set to
: Always).
:
: : In
: : : this
: : : completed document, there are no shaded fields appearing and
the
: only
: : : mailmerge toolbar buttons available are Main Document Setup
and
: Open
: : Data
: : : Source. So it clearly seems to me that it has merged.
: : :
: : : However, it's insistence on prompting to connect to the data
: source
: on
: : : open
: : : proves otherwise. What could be keeping the connection?
: : :
: : : Jennifer
: : :
: : : "Doug Robbins" wrote in message
: : : ...
: : : : Are you sure that you have actually executed the merge to a
new
: : : document?
: : : :
: : : : I suggest that you select Toolbars from the View menu and
then
: check
: : the
: : : : mailmerge item to display the mailmerge toolbar, then use
the
: : buttons
: : on
: : : the
: : : : Right Hand end of the toolbar to execute the mailmerge.
: : : :
: : : : --
: : : : Hope this helps.
: : : :
: : : : Please reply to the newsgroup unless you wish to avail
yourself
: of
: : my
: : : : services on a paid consulting basis.
: : : :
: : : : Doug Robbins - Word MVP
: : : : "Jeni Q" wrote in message
: : : : ...
: : : : Hello. I've read KB 825765 so I understand when and why I
get
: : prompted
: : : : with
: : : : the "Opening this will run the following SQL command"
message
: when
: : you
: : : : open
: : : : a Word mail merge main document that is linked to a data
: source.
: : : :
: : : : However, I do not understand why I get prompted for the
: document
: : after
: : : the
: : : : merge has run and my new document has been created and
saved.
: When
: : I
: : : close
: : : : and reopen the newly created document, I still get the
prompt.
: : There
: : : is
: : : : obviously a data attachment still existing. Is there any
way
: to
: : break
: : : that
: : : : attachment?
: : : :
: : : : I may not be understanding clearly how the merge process
: worked.
: I
: : : thought
: : : : it brought data into the new document and that's it. Is it
: : : continuously
: : : : going to the data source to get the data?
: : : :
: : : : Any help/advice would be appreciated.
: : : :
: : : : Thanks,
: : : : Jennifer Dickens
: : : :
: : : :
: : : :
: : : :
: : :
: : :
: : :
: : :
: :
: :
: :
: :
:
:
:
:

"Peter Jamieson" wrote in message
...
: If I open the template (rather than creating a new doc based on the
: template) I am prompted with the SQL prompt.
:
: OK, in that case I suggest you open the template, go through your
connection
: process, then click the first button, select the "Normal Word Document"
: option, and save the template. At least that should stop the /template/
from
: trying to connect when it loads.

This works beautifully. When I first started messing around with this I
thought for a little while that I could just keep changing the data in the
data source (it's a short row of data) and then every time I opened the
template the data would already be there. This seemed like a very cool
option. However, the side effect of that was that the documents I saved from
the templates still kept the data connection, which was no good.

:
: OK, the key statement is this one - I'll just chop out stuff I'm pretty
sure
: is not needed for starters:
:
: ActiveDocument.MailMerge.OpenDataSource _
: Name:="K:\AA documents\SRC IP Tracking spreadsheet.xls", _
: Connection:="CurrentData", _
: SQLStatement:=""
:
: The main questions are
: a. does this need to work in Word 2000 as well as Word 2003 - if so, is
it
: easy for you to maintain two different versions? Or does it just need to
: work in Word 2003?
: b. are you connecting to a named range called CurrentData? Is it in the
: first sheet in your Excel workbook?

No, it only needs to work in 2003. Yes, we are connecting to a named range
called CurrentData and it is in the first sheet of the workbook.