word 2003 mail merge using foxpro 9 tables as a data source fails

[Daniel Williams]

Hi, I am having trouble doing mail merges within foxpro 9. There are
three separate documents involved here, linked to three different
foxpro tables with ODBC. When I open these documents in Word by
itself, it is able to merge all of them.

When I try to do this programmaticaly or with the mail merge wizard in
foxpro, strangely enough, only one of the documents merges and two of
them do not. And this problem popped up only after switching to word
2003.

I have already asked this question before in a foxpro newsgroup and
got no response whatsoever, so I am wondering, do I need to do
something differently with word 2003, like change a certain setting in
it? Or is this definitely a foxpro problem?

 

[Dan Freeman]

Not sure which Foxpro group you asked in, but I've added the place you're
most likely to get an answer.

It's unclear exactly what you're doing here, but from context I'm guessing
that when you say "in code" you mean in *Foxpro* code.

What does that code look like? <g> What are you doing? What error messages
are you getting? What symptoms are you seeing?

Dan

 

[Peter Jamieson]

Have you applied the registry change described in

http://support.microsoft.com/kb/825765/en-us

?

 

[Daniel Williams]

Not sure which Foxpro group you asked in, but I've added the place you're
most likely to get an answer.

It's unclear exactly what you're doing here, but from context I'm guessing
that when you say "in code" you mean in *Foxpro* code.

What does that code look like? <g> What are you doing? What error messages
are you getting? What symptoms are you seeing?

Dan

Yeah, I meant Foxpro code. Sorry for the ambiguity. There's no code in
the documents themselves.

This is the error in Word when the merge is attempted :

"Invalid Merge Field"
"This field exists in the main document, but does not exist
in the data source"
"You can remove the invalid merge field from the document"
"Or, you can replace it with a valid merge field from the data source"
Then I'm given a choice between 'M__' and 'M__1'. Neither field exists
in the table, but somehow
they're a choice.

This same exact error message pops up for every single merge field,
all of which really do exist in the table.

Not sure how much of the code you want to see, but this is everything
I can see the merge depends upon :

#Define wdDefaultFirstRecord 1
#Define wdDefaultLastRecord -16
#Define wdSendToNewDocument 0
#Define wdFormatDocument 0
#Define wdDialogFilePrint 88

lcday=Dmy(Date()) &&day month year
lcdefaultpath=' ' &&default path
lcletterdir=' ' &&letter is stored here
lcdrive=Sys(5) &&default drive letter
lcdefaultpath=Alltrim(lcdefaultpath)+Sys(2003) &&gets default
path
lcletterdir=Alltrim(lcdrive)+Alltrim(lcdefaultpath) &&Gets the
letter directory
lcformletpath=Alltrim(lcletterdir)+"\letters\rental insp 1st
notice.doc" &&Gets the form letter
lcsaveletter=Alltrim(lcletterdir)+"\Sent letters\first"+lcday
+".doc" &&sETS PATH AND NEW NAME FOR MERGED LETTER

With Thisform.mailmerge1
.capptitle ="BICEPS First Notice Word Merge" && application name,
used in alerts
.nwordproc = 1 && word processor (1 = word 6+; 2 = commadelim; or
user-defined (see below);default = 1)
.nnewdoc = 2 && is it a new doc (1 = new, 2 = existing; default =
1)
.cdocname = lcformletpath
.ntemplate = 1 && type of main document (word only, 1 = form
letter; 2 = label; 3 = envelope; 4 =catalog; default = 1)
.calias = Alias() && alias of source table or view
.cdbctable = Dbf() && table name if table is part of a dbc
.cdbcname = Dbc() && dbc name if source table is part of a dbc
.makeoutput() && do the merge

With .oword
With .activedocument.mailmerge
* make new doc
.destination = wdsendtonewdocument

* remove blank lines
.suppressblanklines = .T.

With .Datasource
* set range of records
.firstrecord = wddefaultfirstrecord
.lastrecord = wddefaultlastrecord
Endwith && .datasource

* do the merge
.execute()

Endwith && .activedocument.mailmerge

* save merged document
.activedocument.SaveAs(lcsaveletter,wdformatdocument)

* make merge source form not visible
.Windows(1).Visible = .F.

* store old background printing setting
lloldprintbackground = .options.printbackground

* turn off background printing, it causes problems
.options.printbackground = .F.

* restore old background printing setting
.options.printbackground = lloldprintbackground

* close merged document
.activedocument.activewindow.Close(.F.)

* close mailmerge form without saving
.activedocument.Close(.F.)

* quit word
.Quit()
Endwith && .oword
.oword = ""

Endwith && thisform.mailmerge1

 

[Dan Freeman]

Does the error happen when you open the document or on the .Execute?
Elsewhere?

Dan

 

[Daniel Williams]

Does the error happen when you open the document or on the .Execute?
Elsewhere?

Dan

 

[swdev2]

Daniel -
AH !

I've seen this in production systems that I touch in the last year.

The BFAI approach to fix:
1. open the existing document.
2. copy all contents
3. close the existing document.
4. open new document
5. paste into new document
6. Setup the merge file, merge fields.
7. Save as the same file name as #1

W2003 gets confused sometimes, and near as I can tell, it's not a data
error, it's a W2003 error.
Recreating the document in the manner I described makes the problem go away.
Should take you 20 minutes
or less for these 7 steps.

It's a PITA.

Mondo Regards [Bill]

 

[Daniel Williams]

Okay, I did exactly as you said, and it didn't work.
So I went a step further and did everything over, this time copying
over only the document text (not the merge fields),
deleting all the .odc files and setting up a new one, and then putting
the merge fields back in, then saving over the old file. Still not
working.

Is there something I'm missing?

I'm certainly confused now as to whether or not a software program can
hate its user!

 

[Daniel Williams]

Daniel -
AH !

I've seen this in production systems that I touch in the last year.

The BFAI approach to fix:
1.  open the existing document.
2.  copy all contents
3.  close the existing document.
4.  open new document
5.  paste into new document
6.  Setup the merge file, merge fields.
7.  Save as the same file name as #1

W2003 gets confused sometimes, and near as I can tell, it's not a data
error, it's a W2003 error.
Recreating the document in the manner I described makes the problem go away.
Should take you 20 minutes
or less for these 7 steps.

It's a PITA.

Mondo Regards [Bill]
--
===================
William Sanders / EFG  VFP / mySql / MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.net www.viasqlserver.net

Does the error happen when you open the document or on the .Execute?
Elsewhere?

What "confuses" Word 2003?

 

[Peter Jamieson]

Not sure you saw this messge on another branch of the thread:

Have you applied the registry change described in

http://support.microsoft.com/kb/825765/en-us

?
If you don't do it, the chances are that your mail merge main document will
not be connected to its data source when you open it.

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

Daniel -
AH !

I've seen this in production systems that I touch in the last year.

The BFAI approach to fix:
1. open the existing document.
2. copy all contents
3. close the existing document.
4. open new document
5. paste into new document
6. Setup the merge file, merge fields.
7. Save as the same file name as #1

W2003 gets confused sometimes, and near as I can tell, it's not a data
error, it's a W2003 error.
Recreating the document in the manner I described makes the problem go
away.
Should take you 20 minutes
or less for these 7 steps.

It's a PITA.

Mondo Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql /
MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.net www.viasqlserver.net

Does the error happen when you open the document or on the .Execute?
Elsewhere?

What "confuses" Word 2003?

 

[Suzanne S. Barnhill]

Why is this so, Peter? As I read the article, the reason for the Registry
edit is just to suppress the prompt "Opening this document will run the
following SQL command...," which I'm used to, so it doesn't bother me. The
article seems to suggest that if you answer yes, everything will be
copacetic, but I'm finding that's not always true.

I never had a problem on my old machine, but that's largely because most of
my mail merges were linked by DDE. When I moved to a new machine, all the
paths were incorrect because Windows assigned "Suzanne" as the name for my
profile (not my choice) instead of "Suzanne S Barnhill" (or whatever it was
before). So I have to reattach everything, but if I just relink to the
source, it doesn't give me the option, despite my having "Confirm conversion
at Open" enabled. I have to say No to the prompt and then go in and
specifically find the data source and link by DDE. Of course, those are
slower to open than the ones linked by OLEDB, but at least they seem to stay
attached. I'm getting really tired of having to relink the same merge to the
same data source week after week.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

Not sure you saw this messge on another branch of the thread:

Have you applied the registry change described in

http://support.microsoft.com/kb/825765/en-us

?
If you don't do it, the chances are that your mail merge main document
will not be connected to its data source when you open it.

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

Daniel -
AH !

I've seen this in production systems that I touch in the last year.

The BFAI approach to fix:
1. open the existing document.
2. copy all contents
3. close the existing document.
4. open new document
5. paste into new document
6. Setup the merge file, merge fields.
7. Save as the same file name as #1

W2003 gets confused sometimes, and near as I can tell, it's not a data
error, it's a W2003 error.
Recreating the document in the manner I described makes the problem go
away.
Should take you 20 minutes
or less for these 7 steps.

It's a PITA.

Mondo Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql /
MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.net www.viasqlserver.net

Does the error happen when you open the document or on the .Execute?
Elsewhere?

Daniel Williams wrote:
Not sure which Foxpro group you asked in, but I've added the place
you're most likely to get an answer.

It's unclear exactly what you're doing here, but from context I'm
guessing that when you say "in code" you mean in *Foxpro* code.

What does that code look like? <g> What are you doing? What error
messages are you getting? What symptoms are you seeing?

Daniel Williams wrote:
Hi, I am having trouble doing mail merges within foxpro 9. There
are
three separate documents involved here, linked to three different
foxpro tables with ODBC. When I open these documents in Word by
itself, it is able to merge all of them.

When I try to do this programmatically or with the mail merge
wizard in foxpro, strangely enough, only one of the documents
merges and two of them do not. And this problem popped up only
after switching to word 2003.

I have already asked this question before in a foxpro newsgroup and
got no response whatsoever, so I am wondering, do I need to do
something differently with word 2003, like change a certain setting
in it? Or is this definitely a foxpro problem?

Yeah, I meant Foxpro code. Sorry for the ambiguity. There's no code
in
the documents themselves.

This is the error in Word when the merge is attempted :

"Invalid Merge Field"
"This field exists in the main document, but does not exist
in the data source"
"You can remove the invalid merge field from the document"
"Or, you can replace it with a valid merge field from the data
source"
Then I'm given a choice between 'M__' and 'M__1'. Neither field
exists
in the table, but somehow
they're a choice.

This same exact error message pops up for every single merge field,
all of which really do exist in the table.

Not sure how much of the code you want to see, but this is everything
I can see the merge depends upon :

#Define wdDefaultFirstRecord 1
#Define wdDefaultLastRecord -16
#Define wdSendToNewDocument 0
#Define wdFormatDocument 0
#Define wdDialogFilePrint 88

lcday=Dmy(Date()) &&day month year
lcdefaultpath=' ' &&default path
lcletterdir=' ' &&letter is stored here
lcdrive=Sys(5) &&default drive letter
lcdefaultpath=Alltrim(lcdefaultpath)+Sys(2003) &&gets default
path
lcletterdir=Alltrim(lcdrive)+Alltrim(lcdefaultpath) &&Gets the
letter directory
lcformletpath=Alltrim(lcletterdir)+"\letters\rental insp 1st
notice.doc" &&Gets the form letter
lcsaveletter=Alltrim(lcletterdir)+"\Sent letters\first"+lcday
+".doc" &&sETS PATH AND NEW NAME FOR MERGED LETTER

With Thisform.mailmerge1
.capptitle ="BICEPS First Notice Word Merge" && application name,
used in alerts
.nwordproc = 1 && word processor (1 = word 6+; 2 = commadelim; or
user-defined (see below);default = 1)
.nnewdoc = 2 && is it a new doc (1 = new, 2 = existing; default =
1)
.cdocname = lcformletpath
.ntemplate = 1 && type of main document (word only, 1 = form
letter; 2 = label; 3 = envelope; 4 =catalog; default = 1)
.calias = Alias() && alias of source table or view
.cdbctable = Dbf() && table name if table is part of a dbc
.cdbcname = Dbc() && dbc name if source table is part of a dbc
.makeoutput() && do the merge

With .oword
With .activedocument.mailmerge
* make new doc
.destination = wdsendtonewdocument

* remove blank lines
.suppressblanklines = .T.

With .Datasource
* set range of records
.firstrecord = wddefaultfirstrecord
.lastrecord = wddefaultlastrecord
Endwith && .datasource

* do the merge
.execute()

Endwith && .activedocument.mailmerge

* save merged document
.activedocument.SaveAs(lcsaveletter,wdformatdocument)

* make merge source form not visible
.Windows(1).Visible = .F.

* store old background printing setting
lloldprintbackground = .options.printbackground

* turn off background printing, it causes problems
.options.printbackground = .F.

* restore old background printing setting
.options.printbackground = lloldprintbackground

* close merged document
.activedocument.activewindow.Close(.F.)

* close mailmerge form without saving
.activedocument.Close(.F.)

* quit word
.Quit()
Endwith && .oword
.oword = ""

Endwith && thisform.mailmerge1

What "confuses" Word 2003?

 

[Peter Jamieson]

There are two problems:
a. if you don't modify the registry, you get the prompt (broadly speaking)
when you open a document manually
b. if you don't modify the registry, when you open the document via
automation, Word does not display the problem and in effect answers "NO" for
you. So any code that relies on the document being attached to its data
source then fails. The automation side of things is mentioned in the
article:

<<
A mail merge main document that is opened by using Microsoft Visual Basic
for Applications(VBA) does not have the data source attached.
Note It could be that you are using a program, for example Microsoft Access
or Microsoft Visual FoxPro, to programmatically open a mail merge main
document file when you experience the symptoms in this article.
However, there certainly do seem to be mail merge documents that do not
suffer from this problem. I believe (but not with a whole bunch of evidence)
that the difference really does come down to whether an SQL query has been
defined (as the message suggests). I suppose many people assume that WOrd
would only issue an SQL query to something that's a "recognised database",
such as Access or SQL Server. But in fact Word uses a very simple dialect of
SQL even when it connects to documents that it opens using one of its
internal converters (e.g. Word documents and, in some cases, delimited text
files) or external converters (e.g. the now obsolete Excel converter). By
default, it uses an OLE DB provider and a simple "SELECT * FROM tablename"
for Access tables and queries, Excel worksheets, and delimited text files).
So mostly it's issuing SQL. However, in the case where it connects via DDE,
it may not be - typically Word actualy specifies a connection string such as

TABLE mytable

in that case, and may not actuallly define a SQL statement. In fact you can
sort of test this by saving a MailMerge document as RTF and removing the bit
of RTF that specifies the query. For some types of data source there is
still enough information for Word to open the data source, and in that case
you do not get the SQL prompt.

As to "Why do we have to endure this prompt at all?" I don't like it either.
It's a classic case of identific a potential security problem and throwing
it back at the users (or in many cases, their IT people). Everyone has to
ignore the prompt to do their work (which I would say means that it has
little effect on the user's real security), and one change to the registry
and /all/ such prompts disappear anyway.

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

Why is this so, Peter? As I read the article, the reason for the Registry
edit is just to suppress the prompt "Opening this document will run the
following SQL command...," which I'm used to, so it doesn't bother me. The
article seems to suggest that if you answer yes, everything will be
copacetic, but I'm finding that's not always true.

I never had a problem on my old machine, but that's largely because most
of my mail merges were linked by DDE. When I moved to a new machine, all
the paths were incorrect because Windows assigned "Suzanne" as the name
for my profile (not my choice) instead of "Suzanne S Barnhill" (or
whatever it was before). So I have to reattach everything, but if I just
relink to the source, it doesn't give me the option, despite my having
"Confirm conversion at Open" enabled. I have to say No to the prompt and
then go in and specifically find the data source and link by DDE. Of
course, those are slower to open than the ones linked by OLEDB, but at
least they seem to stay attached. I'm getting really tired of having to
relink the same merge to the same data source week after week.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

Not sure you saw this messge on another branch of the thread:

Have you applied the registry change described in

http://support.microsoft.com/kb/825765/en-us

?
If you don't do it, the chances are that your mail merge main document
will not be connected to its data source when you open it.

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

Daniel -
AH !

I've seen this in production systems that I touch in the last year.

The BFAI approach to fix:
1. open the existing document.
2. copy all contents
3. close the existing document.
4. open new document
5. paste into new document
6. Setup the merge file, merge fields.
7. Save as the same file name as #1

W2003 gets confused sometimes, and near as I can tell, it's not a data
error, it's a W2003 error.
Recreating the document in the manner I described makes the problem go
away.
Should take you 20 minutes
or less for these 7 steps.

It's a PITA.

Mondo Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql /
MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.net www.viasqlserver.net


On the .execute.

Does the error happen when you open the document or on the .Execute?
Elsewhere?

Dan

Daniel Williams wrote:
Not sure which Foxpro group you asked in, but I've added the place
you're most likely to get an answer.

It's unclear exactly what you're doing here, but from context I'm
guessing that when you say "in code" you mean in *Foxpro* code.

What does that code look like? <g> What are you doing? What error
messages are you getting? What symptoms are you seeing?

Dan

Daniel Williams wrote:
Hi, I am having trouble doing mail merges within foxpro 9. There
are
three separate documents involved here, linked to three different
foxpro tables with ODBC. When I open these documents in Word by
itself, it is able to merge all of them.

When I try to do this programmatically or with the mail merge
wizard in foxpro, strangely enough, only one of the documents
merges and two of them do not. And this problem popped up only
after switching to word 2003.

I have already asked this question before in a foxpro newsgroup
and
got no response whatsoever, so I am wondering, do I need to do
something differently with word 2003, like change a certain
setting
in it? Or is this definitely a foxpro problem?

Yeah, I meant Foxpro code. Sorry for the ambiguity. There's no code
in
the documents themselves.

This is the error in Word when the merge is attempted :

"Invalid Merge Field"
"This field exists in the main document, but does not exist
in the data source"
"You can remove the invalid merge field from the document"
"Or, you can replace it with a valid merge field from the data
source"
Then I'm given a choice between 'M__' and 'M__1'. Neither field
exists
in the table, but somehow
they're a choice.

This same exact error message pops up for every single merge field,
all of which really do exist in the table.

Not sure how much of the code you want to see, but this is
everything
I can see the merge depends upon :

#Define wdDefaultFirstRecord 1
#Define wdDefaultLastRecord -16
#Define wdSendToNewDocument 0
#Define wdFormatDocument 0
#Define wdDialogFilePrint 88

lcday=Dmy(Date()) &&day month year
lcdefaultpath=' ' &&default path
lcletterdir=' ' &&letter is stored here
lcdrive=Sys(5) &&default drive letter
lcdefaultpath=Alltrim(lcdefaultpath)+Sys(2003) &&gets default
path
lcletterdir=Alltrim(lcdrive)+Alltrim(lcdefaultpath) &&Gets the
letter directory
lcformletpath=Alltrim(lcletterdir)+"\letters\rental insp 1st
notice.doc" &&Gets the form letter
lcsaveletter=Alltrim(lcletterdir)+"\Sent letters\first"+lcday
+".doc" &&sETS PATH AND NEW NAME FOR MERGED LETTER

With Thisform.mailmerge1
.capptitle ="BICEPS First Notice Word Merge" && application name,
used in alerts
.nwordproc = 1 && word processor (1 = word 6+; 2 = commadelim; or
user-defined (see below);default = 1)
.nnewdoc = 2 && is it a new doc (1 = new, 2 = existing; default =
1)
.cdocname = lcformletpath
.ntemplate = 1 && type of main document (word only, 1 = form
letter; 2 = label; 3 = envelope; 4 =catalog; default = 1)
.calias = Alias() && alias of source table or view
.cdbctable = Dbf() && table name if table is part of a dbc
.cdbcname = Dbc() && dbc name if source table is part of a dbc
.makeoutput() && do the merge

With .oword
With .activedocument.mailmerge
* make new doc
.destination = wdsendtonewdocument

* remove blank lines
.suppressblanklines = .T.

With .Datasource
* set range of records
.firstrecord = wddefaultfirstrecord
.lastrecord = wddefaultlastrecord
Endwith && .datasource

* do the merge
.execute()

Endwith && .activedocument.mailmerge

* save merged document
.activedocument.SaveAs(lcsaveletter,wdformatdocument)

* make merge source form not visible
.Windows(1).Visible = .F.

* store old background printing setting
lloldprintbackground = .options.printbackground

* turn off background printing, it causes problems
.options.printbackground = .F.

* restore old background printing setting
.options.printbackground = lloldprintbackground

* close merged document
.activedocument.activewindow.Close(.F.)

* close mailmerge form without saving
.activedocument.Close(.F.)

* quit word
.Quit()
Endwith && .oword
.oword = ""

Endwith && thisform.mailmerge1

What "confuses" Word 2003?

 

[Daniel Williams]

Not sure you saw this messge on another branch of the thread:

Have you applied the registry change described in

http://support.microsoft.com/kb/825765/en-us

?
If you don't do it, the chances are that your mail merge main document will
not be connected to its data source when you open it.

--
Peter Jamiesonhttp://tips.pjmsn.me.uk

Daniel -
AH !

I've seen this in production systems that I touch in the last year.

The BFAI approach to fix:
1. open the existing document.
2. copy all contents
3. close the existing document.
4. open new document
5. paste into new document
6. Setup the merge file, merge fields.
7. Save as the same file name as #1

W2003 gets confused sometimes, and near as I can tell, it's not a data
error, it's a W2003 error.
Recreating the document in the manner I described makes the problem go
away.
Should take you 20 minutes
or less for these 7 steps.

It's a PITA.

Mondo Regards [Bill]

"Daniel Williams" <[email protected]> wrote in message

On the .execute.

What "confuses" Word 2003?

Yes, I changed the registry. One month ago when this problem first
popped up on a different machine, and again this week on the one I'm
using now. The result's the same : the SQL prompt disappears, but I
still lose my data source whenever Foxpro opens Word.

 

[Peter Jamieson]

Have you tried issuing an OpenDataSource in your FoxPro code?

(I don't see one in the sample code you posted, but I think in this case you
probably have all the necesary info. to do it. If you are using the ODBC
driver as mentioned in your openeing message, for a machine (system/user)
DSN:
set the Name parameter to ""
set the Connection parameter to "DSN=yourDSNname;followed by anything else
you need;"
It will be something like..

DSN=Visual FoxPro
Database;UID=;PWD=;SourceDB=C:\wbdb\fp\nw.dbc;SourceType=DBC(other stuff);

for a DBC

Set the SQLStatement parameter to something like

SELECT m.* FROM mytable m

Set the subtype to 8 (wdMergeSubTypeWord2000)

For a file DSN, use the .dspathname n as the Name parameter,, use
"FILEDSN=the .dsn pathname;anything else you need;" as the COnnection, and
everything else is the same.

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

Not sure you saw this messge on another branch of the thread:

Have you applied the registry change described in

http://support.microsoft.com/kb/825765/en-us

?
If you don't do it, the chances are that your mail merge main document
will
not be connected to its data source when you open it.

--
Peter Jamiesonhttp://tips.pjmsn.me.uk

Daniel -
AH !

I've seen this in production systems that I touch in the last year.

The BFAI approach to fix:
1. open the existing document.
2. copy all contents
3. close the existing document.
4. open new document
5. paste into new document
6. Setup the merge file, merge fields.
7. Save as the same file name as #1

W2003 gets confused sometimes, and near as I can tell, it's not a data
error, it's a W2003 error.
Recreating the document in the manner I described makes the problem go
away.
Should take you 20 minutes
or less for these 7 steps.

It's a PITA.

Mondo Regards [Bill]

"Daniel Williams" <[email protected]> wrote in message

On the .execute.

What "confuses" Word 2003?

Yes, I changed the registry. One month ago when this problem first
popped up on a different machine, and again this week on the one I'm
using now. The result's the same : the SQL prompt disappears, but I
still lose my data source whenever Foxpro opens Word.

 

[Daniel Williams]

Have you tried issuing an OpenDataSource in your FoxPro code?

(I don't see one in the sample code you posted, but I think in this case you
probably have all the necesary info. to do it. If you are using the ODBC
driver as mentioned in your openeing message, for a machine (system/user)
DSN:
set the Name parameter to ""
set the Connection parameter to "DSN=yourDSNname;followed by anything else
you need;"
It will be something like..

DSN=Visual FoxPro
Database;UID=;PWD=;SourceDB=C:\wbdb\fp\nw.dbc;SourceType=DBC(otherstuff);

for a DBC

Set the SQLStatement parameter to something like

SELECT m.* FROM mytable m

Set the subtype to 8 (wdMergeSubTypeWord2000)

For a file DSN, use the .dspathname n as the Name parameter,, use
"FILEDSN=the .dsn pathname;anything else you need;" as the COnnection, and
everything else is the same.

--
Peter Jamiesonhttp://tips.pjmsn.me.uk

Not sure you saw this messge on another branch of the thread:

Have you applied the registry change described in

?
If you don't do it, the chances are that your mail merge main document
will
not be connected to its data source when you open it.

"Daniel Williams" <[email protected]> wrote in message

On Jan 17, 2:41 pm, "swdev2" <[email protected]>
wrote:

Daniel -
AH !
I've seen this in production systems that I touch in the last year.
The BFAI approach to fix:
1. open the existing document.
2. copy all contents
3. close the existing document.
4. open new document
5. paste into new document
6. Setup the merge file, merge fields.
7. Save as the same file name as #1
W2003 gets confused sometimes, and near as I can tell, it's not a data
error, it's a W2003 error.
Recreating the document in the manner I described makes the problem go
away.
Should take you 20 minutes
or less for these 7 steps.
It's a PITA.
Mondo Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql /
MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.netwww.viasqlserver.net
On the .execute.
Does the error happen when you open the document or on the .Execute?
Elsewhere?
Dan
Daniel Williams wrote:
Not sure which Foxpro group you asked in, but I've added the place
you're most likely to get an answer.
It's unclear exactly what you're doing here, but from context I'm
guessing that when you say "in code" you mean in *Foxpro* code.
What does that code look like? <g> What are you doing? What error
messages are you getting? What symptoms are you seeing?
Dan
Daniel Williams wrote:
Hi, I am having trouble doing mail merges within foxpro 9. There
are
three separate documents involved here, linked to three different
foxpro tables with ODBC. When I open these documents in Word by
itself, it is able to merge all of them.
When I try to do this programmatically or with the mail merge
wizard in foxpro, strangely enough, only one of the documents
merges and two of them do not. And this problem popped up only
after switching to word 2003.
I have already asked this question before in a foxpro newsgroup
and
got no response whatsoever, so I am wondering, do I need to do
something differently with word 2003, like change a certain
setting
in it? Or is this definitely a foxpro problem?
Yeah, I meant Foxpro code. Sorry for the ambiguity. There's no code
in
the documents themselves.
This is the error in Word when the merge is attempted :
"Invalid Merge Field"
"This field exists in the main document, but does not exist
in the data source"
"You can remove the invalid merge field from the document"
"Or, you can replace it with a valid merge field from the data
source"
Then I'm given a choice between 'M__' and 'M__1'. Neither field
exists
in the table, but somehow
they're a choice.
This same exact error message pops up for every single merge field,
all of which really do exist in the table.
Not sure how much of the code you want to see, but this is
everything
I can see the merge depends upon :
#Define wdDefaultFirstRecord 1
#Define wdDefaultLastRecord -16
#Define wdSendToNewDocument 0
#Define wdFormatDocument 0
#Define wdDialogFilePrint 88
lcday=Dmy(Date()) &&day month year
lcdefaultpath=' ' &&default path
lcletterdir=' ' &&letter is stored here
lcdrive=Sys(5) &&default drive letter
lcdefaultpath=Alltrim(lcdefaultpath)+Sys(2003) &&gets default
path
lcletterdir=Alltrim(lcdrive)+Alltrim(lcdefaultpath) &&Gets the
letter directory
lcformletpath=Alltrim(lcletterdir)+"\letters\rental insp 1st
notice.doc" &&Gets the form letter
lcsaveletter=Alltrim(lcletterdir)+"\Sent letters\first"+lcday
+".doc" &&sETS PATH AND NEW NAME FOR MERGED LETTER
With Thisform.mailmerge1
.capptitle ="BICEPS First Notice Word Merge" && application name,
used in alerts
.nwordproc = 1 && word processor (1 = word 6+; 2 = commadelim; or
user-defined (see below);default = 1)
.nnewdoc = 2 && is it a new doc (1 = new, 2 = existing; default =
1)
.cdocname = lcformletpath
.ntemplate = 1 && type of main document (word only, 1 = form
letter; 2 = label; 3 = envelope; 4 =catalog; default = 1)
.calias = Alias() && alias of source table or view
.cdbctable = Dbf() && table name if table is part of a dbc
.cdbcname = Dbc() && dbc name if source table is part of a dbc
.makeoutput() && do the merge
With .oword
With .activedocument.mailmerge
* make new doc
.destination = wdsendtonewdocument
* remove blank lines
.suppressblanklines = .T.
With .Datasource
* set range of records
.firstrecord = wddefaultfirstrecord
.lastrecord = wddefaultlastrecord
Endwith && .datasource
* do the merge
.execute()
Endwith && .activedocument.mailmerge
* save merged document
.activedocument.SaveAs(lcsaveletter,wdformatdocument)
* make merge source form not visible
.Windows(1).Visible = .F.
* store old background printing setting
lloldprintbackground = .options.printbackground
* turn off background printing, it causes problems
.options.printbackground = .F.
* restore old background printing setting
.options.printbackground = lloldprintbackground
* close merged document
.activedocument.activewindow.Close(.F.)
* close mailmerge form without saving
.activedocument.Close(.F.)
* quit word
.Quit()
Endwith && .oword
.oword = ""
Endwith && thisform.mailmerge1

What "confuses" Word 2003?

Yes, I changed the registry. One month ago when this problem first
popped up on a different machine, and again this week on the one I'm
using now. The result's the same : the SQL prompt disappears, but I
still lose my data source whenever Foxpro opens Word.

Alright, this is what I have for OpenDataSource :

.OpenDataSource("", "DSN=Visual Foxpro
Tables;UID=;PWD=;SourceDB="+lctablepath+";SourceType=DBF", "SELECT *
FROM firstnotice", wdMergeSubTypeWord2000)

I am getting an error on that function : "OLE error code 0x80020005:
Type mismatch." The only thing I can think of is that last parameter,
but it's already defined as 8 in the start of the code, so I don't
know... does it look like anything is missing or incorrect?

 

[Daniel Williams]

Have you tried issuing an OpenDataSource in your FoxPro code?

(I don't see one in the sample code you posted, but I think in this caseyou
probably have all the necesary info. to do it. If you are using the ODBC
driver as mentioned in your openeing message, for a machine (system/user)
DSN:
set the Name parameter to ""
set the Connection parameter to "DSN=yourDSNname;followed by anything else
you need;"
It will be something like..

DSN=Visual FoxPro
Database;UID=;PWD=;SourceDB=C:\wbdb\fp\nw.dbc;SourceType=DBC(other stuff);

for a DBC

Set the SQLStatement parameter to something like

SELECT m.* FROM mytable m

Set the subtype to 8 (wdMergeSubTypeWord2000)

For a file DSN, use the .dspathname n as the Name parameter,, use
"FILEDSN=the .dsn pathname;anything else you need;" as the COnnection,and
everything else is the same.

"Daniel Williams" <[email protected]> wrote in message

On Jan 18, 5:05 pm, "Peter Jamieson" <[email protected]>
wrote:

Not sure you saw this messge on another branch of the thread:
Have you applied the registry change described in
http://support.microsoft.com/kb/825765/en-us
?
If you don't do it, the chances are that your mail merge main document
will
not be connected to its data source when you open it.
--
Peter Jamiesonhttp://tips.pjmsn.me.uk
On Jan 17, 2:41 pm, "swdev2" <[email protected]>
wrote:
Daniel -
AH !
I've seen this in production systems that I touch in the last year.
The BFAI approach to fix:
1. open the existing document.
2. copy all contents
3. close the existing document.
4. open new document
5. paste into new document
6. Setup the merge file, merge fields.
7. Save as the same file name as #1
W2003 gets confused sometimes, and near as I can tell, it's not a data
error, it's a W2003 error.
Recreating the document in the manner I described makes the problem go
away.
Should take you 20 minutes
or less for these 7 steps.
It's a PITA.
Mondo Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql /
MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.netwww.viasqlserver.net
On the .execute.
Does the error happen when you open the document or on the .Execute?
Elsewhere?
Dan
Daniel Williams wrote:
Not sure which Foxpro group you asked in, but I've added the place
you're most likely to get an answer.
It's unclear exactly what you're doing here, but from context I'm
guessing that when you say "in code" you mean in *Foxpro* code.
What does that code look like? <g> What are you doing? What error
messages are you getting? What symptoms are you seeing?
Dan
Daniel Williams wrote:
Hi, I am having trouble doing mail merges within foxpro 9. There
are
three separate documents involved here, linked to three different
foxpro tables with ODBC. When I open these documents in Word by
itself, it is able to merge all of them.
When I try to do this programmatically or with the mail merge
wizard in foxpro, strangely enough, only one of the documents
merges and two of them do not. And this problem popped up only
after switching to word 2003.
I have already asked this question before in a foxpro newsgroup
and
got no response whatsoever, so I am wondering, do I need to do
something differently with word 2003, like change a certain
setting
in it? Or is this definitely a foxpro problem?
Yeah, I meant Foxpro code. Sorry for the ambiguity. There's no code
in
the documents themselves.
This is the error in Word when the merge is attempted :
"Invalid Merge Field"
"This field exists in the main document, but does not exist
in the data source"
"You can remove the invalid merge field from the document"
"Or, you can replace it with a valid merge field from the data
source"
Then I'm given a choice between 'M__' and 'M__1'. Neither field
exists
in the table, but somehow
they're a choice.
This same exact error message pops up for every single merge field,
all of which really do exist in the table.
Not sure how much of the code you want to see, but this is
everything
I can see the merge depends upon :
#Define wdDefaultFirstRecord 1
#Define wdDefaultLastRecord -16
#Define wdSendToNewDocument 0
#Define wdFormatDocument 0
#Define wdDialogFilePrint 88
lcday=Dmy(Date()) &&day month year
lcdefaultpath=' ' &&default path
lcletterdir=' ' &&letter is stored here
lcdrive=Sys(5) &&default drive letter
lcdefaultpath=Alltrim(lcdefaultpath)+Sys(2003) &&gets default
path
lcletterdir=Alltrim(lcdrive)+Alltrim(lcdefaultpath) &&Gets the
letter directory
lcformletpath=Alltrim(lcletterdir)+"\letters\rental insp 1st
notice.doc" &&Gets the form letter
lcsaveletter=Alltrim(lcletterdir)+"\Sent letters\first"+lcday
+".doc" &&sETS PATH AND NEW NAME FOR MERGED LETTER
With Thisform.mailmerge1
.capptitle ="BICEPS First Notice Word Merge" && application name,
used in alerts
.nwordproc = 1 && word processor (1 = word 6+; 2 = commadelim; or
user-defined (see below);default = 1)
.nnewdoc = 2 && is it a new doc (1 = new, 2 = existing; default =
1)
.cdocname = lcformletpath
.ntemplate = 1 && type of main document (word only, 1 = form
letter; 2 = label; 3 = envelope; 4 =catalog; default = 1)
.calias = Alias() && alias of source table or view
.cdbctable = Dbf() && table name if table is part of a dbc
.cdbcname = Dbc() && dbc name if source table is part of a dbc
.makeoutput() && do the merge
With .oword
With .activedocument.mailmerge
* make new doc
.destination = wdsendtonewdocument
* remove blank lines
.suppressblanklines = .T.
With .Datasource
* set range of records
.firstrecord = wddefaultfirstrecord
.lastrecord = wddefaultlastrecord
Endwith && .datasource
* do the merge
.execute()
Endwith && .activedocument.mailmerge
* save merged document
.activedocument.SaveAs(lcsaveletter,wdformatdocument)
* make merge source form not visible
.Windows(1).Visible = .F.
* store old background printing setting
lloldprintbackground = .options.printbackground
* turn off background printing, it causes problems
.options.printbackground = .F.
* restore old background printing setting
.options.printbackground = lloldprintbackground
* close merged document
.activedocument.activewindow.Close(.F.)
* close mailmerge form without saving
.activedocument.Close(.F.)
* quit word
.Quit()
Endwith && .oword
.oword = ""
Endwith && thisform.mailmerge1
What "confuses" Word 2003?

Yes, I changed the registry. One month ago when this problem first
popped up on a different machine, and again this week on the one I'm
using now. The result's the same : the SQL prompt disappears, but I
still lose my data source whenever Foxpro opens Word.

Alright, this is what I have for OpenDataSource :

.OpenDataSource("", "DSN=Visual Foxpro
Tables;UID=;PWD=;SourceDB="+lctablepath+";SourceType=DBF", "SELECT*
FROM firstnotice", wdMergeSubTypeWord2000)

I am getting an error on that function : "OLE error code 0x80020005:
Type mismatch." The only thing I can think of is that last parameter,
but it's already defined as 8 in the start of the code, so I don't
know... does it look like anything is missing or incorrect?

I guess it'd help if I also included what values the two variables are
set to...

lctablepath = C:\DOCUMENTS AND SETTINGS\DWILLIAMS\MY DOCUMENTS\VISUAL
FOXPRO PROJECTS\CODEENFORCE_WORDMERGE\firstnotice.dbf
wdMergeSubTypeWord2000 = 8

 

[Peter Jamieson]

Hi, sorry, been away, then lost track of this one.

At the very least, you need some placeholder parameters in your
OpenDataSource - I don't know how you do that in FoxPro code but...

.OpenDataSource("",placeholder,placeholder, placeholder, placeholder,
placeholder, placeholder,placeholder, placeholder, placeholder, placeholder,
"DSN=Visual Foxpro
Tables;UID=;PWD=;SourceDB="+lctablepath+";SourceType=DBF", "SELECT * FROM
firstnotice", "", placeholder, wdMergeSubTypeWord2000)

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

Have you tried issuing an OpenDataSource in your FoxPro code?

(I don't see one in the sample code you posted, but I think in this case
you
probably have all the necesary info. to do it. If you are using the ODBC
driver as mentioned in your openeing message, for a machine
(system/user)
DSN:
set the Name parameter to ""
set the Connection parameter to "DSN=yourDSNname;followed by anything
else
you need;"
It will be something like..

DSN=Visual FoxPro
Database;UID=;PWD=;SourceDB=C:\wbdb\fp\nw.dbc;SourceType=DBC(other
stuff);

for a DBC

Set the SQLStatement parameter to something like

SELECT m.* FROM mytable m

Set the subtype to 8 (wdMergeSubTypeWord2000)

For a file DSN, use the .dspathname n as the Name parameter,, use
"FILEDSN=the .dsn pathname;anything else you need;" as the COnnection,
and
everything else is the same.

"Daniel Williams" <[email protected]> wrote in message

On Jan 18, 5:05 pm, "Peter Jamieson" <[email protected]>
wrote:

Not sure you saw this messge on another branch of the thread:
Have you applied the registry change described in
http://support.microsoft.com/kb/825765/en-us
?
If you don't do it, the chances are that your mail merge main document
will
not be connected to its data source when you open it.
--
Peter Jamiesonhttp://tips.pjmsn.me.uk
On Jan 17, 2:41 pm, "swdev2" <[email protected]>
wrote:
Daniel -
AH !
I've seen this in production systems that I touch in the last year.
The BFAI approach to fix:
1. open the existing document.
2. copy all contents
3. close the existing document.
4. open new document
5. paste into new document
6. Setup the merge file, merge fields.
7. Save as the same file name as #1
W2003 gets confused sometimes, and near as I can tell, it's not a
data
error, it's a W2003 error.
Recreating the document in the manner I described makes the problem
go
away.
Should take you 20 minutes
or less for these 7 steps.
It's a PITA.
Mondo Regards [Bill]
--
===================
William Sanders / EFG VFP / mySql /
MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.netwww.viasqlserver.net
On the .execute.
Does the error happen when you open the document or on the
.Execute?
Elsewhere?
Dan
Daniel Williams wrote:
Not sure which Foxpro group you asked in, but I've added the
place
you're most likely to get an answer.
It's unclear exactly what you're doing here, but from context
I'm
guessing that when you say "in code" you mean in *Foxpro* code.
What does that code look like? <g> What are you doing? What
error
messages are you getting? What symptoms are you seeing?
Dan
Daniel Williams wrote:
Hi, I am having trouble doing mail merges within foxpro 9.
There
are
three separate documents involved here, linked to three
different
foxpro tables with ODBC. When I open these documents in Word
by
itself, it is able to merge all of them.
When I try to do this programmatically or with the mail merge
wizard in foxpro, strangely enough, only one of the documents
merges and two of them do not. And this problem popped up only
after switching to word 2003.
I have already asked this question before in a foxpro
newsgroup
and
got no response whatsoever, so I am wondering, do I need to do
something differently with word 2003, like change a certain
setting
in it? Or is this definitely a foxpro problem?
Yeah, I meant Foxpro code. Sorry for the ambiguity. There's no
code
in
the documents themselves.
This is the error in Word when the merge is attempted :
"Invalid Merge Field"
"This field exists in the main document, but does not exist
in the data source"
"You can remove the invalid merge field from the document"
"Or, you can replace it with a valid merge field from the data
source"
Then I'm given a choice between 'M__' and 'M__1'. Neither field
exists
in the table, but somehow
they're a choice.
This same exact error message pops up for every single merge
field,
all of which really do exist in the table.
Not sure how much of the code you want to see, but this is
everything
I can see the merge depends upon :
#Define wdDefaultFirstRecord 1
#Define wdDefaultLastRecord -16
#Define wdSendToNewDocument 0
#Define wdFormatDocument 0
#Define wdDialogFilePrint 88
lcday=Dmy(Date()) &&day month year
lcdefaultpath=' ' &&default path
lcletterdir=' ' &&letter is stored here
lcdrive=Sys(5) &&default drive letter
lcdefaultpath=Alltrim(lcdefaultpath)+Sys(2003) &&gets default
path
lcletterdir=Alltrim(lcdrive)+Alltrim(lcdefaultpath) &&Gets the
letter directory
lcformletpath=Alltrim(lcletterdir)+"\letters\rental insp 1st
notice.doc" &&Gets the form letter
lcsaveletter=Alltrim(lcletterdir)+"\Sent letters\first"+lcday
+".doc" &&sETS PATH AND NEW NAME FOR MERGED LETTER
With Thisform.mailmerge1
.capptitle ="BICEPS First Notice Word Merge" && application
name,
used in alerts
.nwordproc = 1 && word processor (1 = word 6+; 2 = commadelim;
or
user-defined (see below);default = 1)
.nnewdoc = 2 && is it a new doc (1 = new, 2 = existing; default
=
1)
.cdocname = lcformletpath
.ntemplate = 1 && type of main document (word only, 1 = form
letter; 2 = label; 3 = envelope; 4 =catalog; default = 1)
.calias = Alias() && alias of source table or view
.cdbctable = Dbf() && table name if table is part of a dbc
.cdbcname = Dbc() && dbc name if source table is part of a dbc
.makeoutput() && do the merge
With .oword
With .activedocument.mailmerge
* make new doc
.destination = wdsendtonewdocument
* remove blank lines
.suppressblanklines = .T.
With .Datasource
* set range of records
.firstrecord = wddefaultfirstrecord
.lastrecord = wddefaultlastrecord
Endwith && .datasource
* do the merge
.execute()
Endwith && .activedocument.mailmerge
* save merged document
.activedocument.SaveAs(lcsaveletter,wdformatdocument)
* make merge source form not visible
.Windows(1).Visible = .F.
* store old background printing setting
lloldprintbackground = .options.printbackground
* turn off background printing, it causes problems
.options.printbackground = .F.
* restore old background printing setting
.options.printbackground = lloldprintbackground
* close merged document
.activedocument.activewindow.Close(.F.)
* close mailmerge form without saving
.activedocument.Close(.F.)
* quit word
.Quit()
Endwith && .oword
.oword = ""
Endwith && thisform.mailmerge1
What "confuses" Word 2003?

Yes, I changed the registry. One month ago when this problem first
popped up on a different machine, and again this week on the one I'm
using now. The result's the same : the SQL prompt disappears, but I
still lose my data source whenever Foxpro opens Word.

Alright, this is what I have for OpenDataSource :

.OpenDataSource("", "DSN=Visual Foxpro
Tables;UID=;PWD=;SourceDB="+lctablepath+";SourceType=DBF", "SELECT *
FROM firstnotice", wdMergeSubTypeWord2000)

I am getting an error on that function : "OLE error code 0x80020005:
Type mismatch." The only thing I can think of is that last parameter,
but it's already defined as 8 in the start of the code, so I don't
know... does it look like anything is missing or incorrect?

I guess it'd help if I also included what values the two variables are
set to...

lctablepath = C:\DOCUMENTS AND SETTINGS\DWILLIAMS\MY DOCUMENTS\VISUAL
FOXPRO PROJECTS\CODEENFORCE_WORDMERGE\firstnotice.dbf
wdMergeSubTypeWord2000 = 8