 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Word / Document Management / January 2008word 2003 mail merge using foxpro 9 tables as a data source fails
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? 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 > Hi, I am having trouble doing mail merges within foxpro 9. There are > three separate documents involved here, linked to three different [quoted text clipped - 10 lines] > something differently with word 2003, like change a certain setting in > it? Or is this definitely a foxpro problem? > Not sure which Foxpro group you asked in, but I've added the place you're > most likely to get an answer. [quoted text clipped - 21 lines] > > 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 Does the error happen when you open the document or on the .Execute? Elsewhere? Dan >> Not sure which Foxpro group you asked in, but I've added the place >> you're most likely to get an answer. [quoted text clipped - 122 lines] > > Endwith && thisform.mailmerge1 On the .execute. > Does the error happen when you open the document or on the .Execute? > Elsewhere? [quoted text clipped - 127 lines] > > > Endwith && thisform.mailmerge1 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-SQL www.efgroup.net/vfpwebhosting www.terrafox.net www.viasqlserver.net On Jan 17, 11:47 am, "Dan Freeman" <s...@microsoft.com> wrote: On the .execute. > Does the error happen when you open the document or on the .Execute? > Elsewhere? [quoted text clipped - 130 lines] > > > Endwith && thisform.mailmerge1 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! On Jan 17, 2:41 pm, "swdev2" <wsand...@dotnetconversions.bob.com> wrote: > Daniel - > AH ! [quoted text clipped - 22 lines] > =================== > William Sanders / EFG VFP / mySql / MS-SQLwww.efgroup.net/vfpwebhostingwww.terrafox.net www.viasqlserver.net On Jan 17, 2:41 pm, "swdev2" <wsand...@dotnetconversions.bob.com> wrote: > Daniel - > AH ! [quoted text clipped - 160 lines] > > > > Endwith && thisform.mailmerge1 What "confuses" Word 2003? 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.  SignaturePeter Jamieson http://tips.pjmsn.me.uk On Jan 17, 2:41 pm, "swdev2" <wsand...@dotnetconversions.bob.com> wrote: > Daniel - > AH ! [quoted text clipped - 162 lines] > > > > Endwith && thisform.mailmerge1 What "confuses" Word 2003? 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. SignatureSuzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA > Not sure you saw this messge on another branch of the thread: > [quoted text clipped - 180 lines] > > What "confuses" Word 2003? 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. SignaturePeter 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 [quoted text clipped - 201 lines] >> >> What "confuses" Word 2003? On Jan 18, 5:05 pm, "Peter Jamieson" <p...@KillmapSpjjnet.demon.co.uk> wrote: > Not sure you saw this messge on another branch of the thread: > [quoted text clipped - 180 lines] > > 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. 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. SignaturePeter Jamieson http://tips.pjmsn.me.uk On Jan 18, 5:05 pm, "Peter Jamieson" <p...@KillmapSpjjnet.demon.co.uk> wrote: > Not sure you saw this messge on another branch of the thread: > [quoted text clipped - 194 lines] > > 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. > Have you tried issuing an OpenDataSource in your FoxPro code? > [quoted text clipped - 231 lines] > 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? > > Have you tried issuing an OpenDataSource in your FoxPro code? > [quoted text clipped - 245 lines] > 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 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) SignaturePeter Jamieson http://tips.pjmsn.me.uk > On Jan 20, 6:27 am, "Peter Jamieson" <p...@KillmapSpjjnet.demon.co.uk> > wrote: [quoted text clipped - 266 lines] > 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 Have you applied the registry change described in http://support.microsoft.com/kb/825765/en-us ? SignaturePeter Jamieson http://tips.pjmsn.me.uk > Hi, I am having trouble doing mail merges within foxpro 9. There are > three separate documents involved here, linked to three different [quoted text clipped - 10 lines] > something differently with word 2003, like change a certain setting in > it? Or is this definitely a foxpro problem? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.