Microsoft KB Archive/181926: Difference between revisions
m (Text replacement - ">" to ">") |
m (Text replacement - "&" to "&") |
||
(One intermediate revision by the same user not shown) | |||
Line 58: | Line 58: | ||
== MORE INFORMATION == | == MORE INFORMATION == | ||
Below is the Visual FoxPro for Windows code using the Word.Application object model that performs the mail merge using an ODBC data source to an existing Visual FoxPro table. This code can use an existing data source name (DSN), created in the ODBC Driver Manager, or it can create one on the fly utilizing an API call to add and use the DSN. In the example below, | Below is the Visual FoxPro for Windows code using the Word.Application object model that performs the mail merge using an ODBC data source to an existing Visual FoxPro table. This code can use an existing data source name (DSN), created in the ODBC Driver Manager, or it can create one on the fly utilizing an API call to add and use the DSN. In the example below, "Visual FoxPro Tables" is the DSN created in the ODBC Driver Manager and points to the \Samples\Data folder in the VFP home folder. These paths will most likely have to be changed to reflect the users' paths.<br /> | ||
<br /> | <br /> | ||
'''Note''' This code was tested with the Service release-1 (SR-1) for Word 97 and Microsoft Office Word 2003. Using prior versions of Word 97 causes a Type Mismatch and other OLE errors.<br /> | '''Note''' This code was tested with the Service release-1 (SR-1) for Word 97 and Microsoft Office Word 2003. Using prior versions of Word 97 causes a Type Mismatch and other OLE errors.<br /> | ||
Line 90: | Line 90: | ||
*** Change the path below to point to the Customer table *** | *** Change the path below to point to the Customer table *** | ||
*** in the \Samples\Data folder. *** | *** in the \Samples\Data folder. *** | ||
settings= | settings="DSN=visual foxpro tables"+chr(0)+; | ||
"Description=VFP ODBC Driver"+chr(0)+; | |||
"SourceDB=e:\vfp7\samples\data"+chr(0)+; | |||
"SourceType=DBF" | |||
=SQLConfigDataSource(0,1, | =SQLConfigDataSource(0,1,"Microsoft Visual FoxPro Driver",settings) | ||
* NOTE: Ensure there are no spaces on either side of the equal sign (=). | * NOTE: Ensure there are no spaces on either side of the equal sign (=). | ||
ON ERROR DO errhand WITH ERROR(), MESSAGE(), MESSAGE(1), PROGRAM( ), | ON ERROR DO errhand WITH ERROR(), MESSAGE(), MESSAGE(1), PROGRAM( ), | ||
LINENO( ) & | LINENO( ) && Trap OLE & other errors. | ||
* Initialize variables passed to Word to create form letter. | * Initialize variables passed to Word to create form letter. | ||
intro1= | intro1="Congratulations! You are one of our best customers since you have ; | ||
purchased $ | purchased $" | ||
intro2= | intro2=" each month from us. " | ||
intro3= | intro3="As a result, your maximum order amount has been increased by ; | ||
$2500.00. If you have any questions, please feel free to contact us. | $2500.00. If you have any questions, please feel free to contact us." | ||
oWord = CREATEOBJECT( | oWord = CREATEOBJECT("Word.Application") | ||
WITH oWord | WITH oWord | ||
* Assign values to variables | * Assign values to variables | ||
dsname= | dsname="e:\VFP7\SAMPLES\DATA\customer.DBF" | ||
wformat=0 | wformat=0 | ||
wconfirmconv=0 | wconfirmconv=0 | ||
Line 117: | Line 117: | ||
wlinktosource=0 | wlinktosource=0 | ||
waddtofilelist=0 | waddtofilelist=0 | ||
wpassworddoc= | wpassworddoc="" | ||
wpasswordtemp= | wpasswordtemp="" | ||
wrevert=0 | wrevert=0 | ||
wprotectdoc= | wprotectdoc="" | ||
wprotecttemp= | wprotecttemp="" | ||
wconn= | wconn="DSN=visual foxpro tables;uid=;pwd=;"+; | ||
"sourcedb=e:\vfp7\samples\data;sourcetype=dbf"+; | |||
"exclusive=no;backgroundfetch=yes;collate=machine;" | |||
wsqlstatement= | wsqlstatement="SELECT contact,company,title,address,city,; | ||
postalcode,STR(maxordamt,12,2) as maxordamt FROM customer ; | postalcode,STR(maxordamt,12,2) as maxordamt FROM customer ; | ||
WHERE (customer.maxordamt>$100000) | WHERE (customer.maxordamt>$100000)" | ||
.Visible=.T. & | .Visible=.T. && Make Word visible. | ||
.WindowState = 2 & | .WindowState = 2 && Minimize Word. | ||
.Documents.Add & | .Documents.Add && Add new document. | ||
.Selection.InsertParagraphAfter | .Selection.InsertParagraphAfter | ||
Line 141: | Line 141: | ||
wprotectdoc,wprotecttemp,wconn,wsqlstatement) | wprotectdoc,wprotecttemp,wconn,wsqlstatement) | ||
.ActiveDocument.MailMerge.EditMainDocument | .ActiveDocument.MailMerge.EditMainDocument | ||
.Selection.InsertDateTime( | .Selection.InsertDateTime("dddd, MMMM dd, yyyy", 1) | ||
.Selection.MoveRight | .Selection.MoveRight | ||
.Selection.InsertParagraphAfter | .Selection.InsertParagraphAfter | ||
.Selection.MoveDown | .Selection.MoveDown | ||
.ActiveDocument.MailMerge.Fields.Add; | .ActiveDocument.MailMerge.Fields.Add; | ||
(oWord.Selection.Range, | (oWord.Selection.Range,"contact") | ||
.Selection.InsertParagraphAfter | .Selection.InsertParagraphAfter | ||
.Selection.MoveDown | .Selection.MoveDown | ||
.ActiveDocument.MailMerge.Fields.Add; | .ActiveDocument.MailMerge.Fields.Add; | ||
(oWord.Selection.Range, | (oWord.Selection.Range,"company") | ||
.Selection.InsertParagraphAfter | .Selection.InsertParagraphAfter | ||
.Selection.MoveDown | .Selection.MoveDown | ||
.ActiveDocument.MailMerge.Fields.Add; | .ActiveDocument.MailMerge.Fields.Add; | ||
(oWord.Selection.Range, | (oWord.Selection.Range,"title") | ||
.Selection.InsertParagraphAfter | .Selection.InsertParagraphAfter | ||
.Selection.MoveDown | .Selection.MoveDown | ||
.ActiveDocument.MailMerge.Fields.Add; | .ActiveDocument.MailMerge.Fields.Add; | ||
(oWord.Selection.Range, | (oWord.Selection.Range,"address") | ||
.Selection.InsertAfter( | .Selection.InsertAfter(", ") | ||
.Selection.MoveRight | .Selection.MoveRight | ||
.ActiveDocument.MailMerge.Fields.Add(oWord.Selection.Range, | .ActiveDocument.MailMerge.Fields.Add(oWord.Selection.Range,"city") | ||
.Selection.InsertAfter( | .Selection.InsertAfter(" ") | ||
.Selection.MoveRight | .Selection.MoveRight | ||
.ActiveDocument.MailMerge.Fields.Add; | .ActiveDocument.MailMerge.Fields.Add; | ||
(oWord.Selection.Range, | (oWord.Selection.Range,"postalcode") | ||
.Selection.MoveRight | .Selection.MoveRight | ||
.Selection.InsertParagraphAfter | .Selection.InsertParagraphAfter | ||
.Selection.InsertParagraphAfter | .Selection.InsertParagraphAfter | ||
.Selection.InsertAfter( | .Selection.InsertAfter("Dear ") | ||
.Selection.MoveRight | .Selection.MoveRight | ||
.ActiveDocument.MailMerge.Fields.Add; | .ActiveDocument.MailMerge.Fields.Add; | ||
(oWord.Selection.Range, | (oWord.Selection.Range,"contact") | ||
.Selection.MoveRight | .Selection.MoveRight | ||
.Selection.InsertAfter( | .Selection.InsertAfter(":") | ||
.Selection.MoveRight | .Selection.MoveRight | ||
.Selection.InsertParagraphAfter | .Selection.InsertParagraphAfter | ||
Line 182: | Line 182: | ||
.Selection.MoveRight | .Selection.MoveRight | ||
.ActiveDocument.MailMerge.Fields.Add; | .ActiveDocument.MailMerge.Fields.Add; | ||
(oWord.Selection.Range, | (oWord.Selection.Range,"maxordamt") | ||
.Selection.MoveRight | .Selection.MoveRight | ||
.Selection.InsertAfter(intro2) | .Selection.InsertAfter(intro2) | ||
Line 191: | Line 191: | ||
.Selection.InsertParagraphAfter | .Selection.InsertParagraphAfter | ||
.Selection.MoveDown | .Selection.MoveDown | ||
.Selection.InsertAfter( | .Selection.InsertAfter("Thank you,") | ||
.Selection.MoveRight | .Selection.MoveRight | ||
.Selection.InsertParagraphAfter | .Selection.InsertParagraphAfter | ||
.Selection.InsertParagraphAfter | .Selection.InsertParagraphAfter | ||
.Selection.MoveDown | .Selection.MoveDown | ||
.Selection.Fields.Add(oWord.Selection.Range,-1, | .Selection.Fields.Add(oWord.Selection.Range,-1,"author") | ||
.ActiveDocument.MailMerge.Destination = 0 & | .ActiveDocument.MailMerge.Destination = 0 && new document. | ||
.ActiveDocument.MailMerge.Execute & | .ActiveDocument.MailMerge.Execute && Run the merge. | ||
nanswer = MESSAGEBOX( | nanswer = MESSAGEBOX("Would you like to view the finished ; | ||
document? | document?",36,"Switch to Word") | ||
DO CASE | DO CASE | ||
CASE nanswer = 6 | CASE nanswer = 6 | ||
.ActiveWindow.View.Type = 3 & | .ActiveWindow.View.Type = 3 && Page layout view. | ||
.Visible = .T. | .Visible = .T. | ||
.Application.Activate & | .Application.Activate && Bring Word forward. | ||
.WindowState = 0 & | .WindowState = 0 && Word in normal state. | ||
=INKEY(5) & | =INKEY(5) && Wait 5 seconds then bring VFP back. | ||
.WindowState = 2 & | .WindowState = 2 && Then minimize Word. | ||
ENDCASE | ENDCASE | ||
nanswer = MESSAGEBOX( | nanswer = MESSAGEBOX("Print the document?",36,; | ||
"Print the merged document.") | |||
DO CASE | DO CASE | ||
CASE nanswer = 6 & | CASE nanswer = 6 && Yes | ||
.ActiveDocument.PrintOut & | .ActiveDocument.PrintOut && Print the merged form letter. | ||
ENDCASE | ENDCASE | ||
nanswer = MESSAGEBOX( | nanswer = MESSAGEBOX("Save the document?",36,; | ||
"Enter the name to save the merged document.") | |||
DO CASE | DO CASE | ||
CASE nanswer = 6 & | CASE nanswer = 6 && Yes | ||
cbaddoc=SPACE(25) | cbaddoc=SPACE(25) | ||
DO WHILE .T. | DO WHILE .T. | ||
Line 229: | Line 229: | ||
mquit = SPACE(1) | mquit = SPACE(1) | ||
IF !EMPTY(cbaddoc) | IF !EMPTY(cbaddoc) | ||
@2,1 SAY | @2,1 SAY "Existing file name: "+cbaddoc COLOR R+/w | ||
ENDIF | ENDIF | ||
@4,1 SAY | @4,1 SAY "Enter a name for the Word document." | ||
@5,1 GET cdoc color b/w VALID emptyChk(cdoc) | @5,1 GET cdoc color b/w VALID emptyChk(cdoc) | ||
@7,25 GET mquit FUNCTION '*T Save' SIZE 2,10 | @7,25 GET mquit FUNCTION '*T Save' SIZE 2,10 | ||
Line 237: | Line 237: | ||
CLEAR WINDOW getname | CLEAR WINDOW getname | ||
cdoc=ALLTRIM(cdoc) | cdoc=ALLTRIM(cdoc) | ||
cpath= .Options.DefaultFilePath(0) & | cpath= .Options.DefaultFilePath(0) && Get Word's | ||
& | && default document path. | ||
IF FILE(cpath+ | IF FILE(cpath+"\"+cdoc+".doc") | ||
WAIT WINDOW | WAIT WINDOW "Document already exists! Enter another ; | ||
file name. | file name." TIME 1 | ||
cbaddoc=cdoc | cbaddoc=cdoc | ||
LOOP | LOOP | ||
Line 251: | Line 251: | ||
EXIT | EXIT | ||
ENDDO | ENDDO | ||
CASE nanswer = 7 & | CASE nanswer = 7 && No | ||
.Documents.Close(0) | .Documents.Close(0) | ||
.Application.Quit | .Application.Quit | ||
Line 257: | Line 257: | ||
ENDWITH | ENDWITH | ||
ON ERROR & | ON ERROR && Restore system error handler. | ||
IF VAL(SUBSTR(VERSION(4),2,1))>7 | IF VAL(SUBSTR(VERSION(4),2,1))>7 | ||
_screen.Themes=lthemes | _screen.Themes=lthemes | ||
Line 267: | Line 267: | ||
RETURN .T. | RETURN .T. | ||
ELSE | ELSE | ||
WAIT WINDOW | WAIT WINDOW "Enter a name for the Word Document" | ||
RETURN 0 | RETURN 0 | ||
ENDIF | ENDIF | ||
Line 277: | Line 277: | ||
mlineno=LTRIM(STR(LINENO())) | mlineno=LTRIM(STR(LINENO())) | ||
merror=LTRIM(STR(merror)) | merror=LTRIM(STR(merror)) | ||
=MESSAGEBOX( | =MESSAGEBOX("You have an OLE error. Usually this is caused by | ||
quitting Word or canceling out of a dialog box in Word. | quitting Word or canceling out of a dialog box in Word. "+CHR(13)+; | ||
"The error message is "+mess) | |||
ELSE | ELSE | ||
mlineno=LTRIM(STR(LINENO())) | mlineno=LTRIM(STR(LINENO())) | ||
merror=LTRIM(STR(merror)) | merror=LTRIM(STR(merror)) | ||
=MESSAGEBOX( | =MESSAGEBOX("An error occured on line "+mlineno+; | ||
". The error message is "+mess+" The error number is "+merror) | |||
ENDIF | ENDIF | ||
ON ERROR | ON ERROR | ||
Line 304: | Line 304: | ||
</div> | </div> | ||
Microsoft Visual Basic for Applications Help (Word '97); search on | Microsoft Visual Basic for Applications Help (Word '97); search on "OLE" | ||
</div> | </div> |
Latest revision as of 12:31, 21 July 2020
Article ID: 181926
Article Last Modified on 2/16/2005
APPLIES TO
- Microsoft Visual FoxPro 3.0 Standard Edition
- Microsoft Visual FoxPro 3.0b Standard Edition
- Microsoft Visual FoxPro 5.0 Standard Edition
- Microsoft Visual FoxPro 5.0a
- Microsoft Visual FoxPro 6.0 Professional Edition
- Microsoft Visual FoxPro 7.0 Professional Edition
- Microsoft Visual FoxPro 8.0 Professional Edition
- Microsoft Visual FoxPro 9.0 Professional Edition
This article was previously published under Q181926
SUMMARY
This article demonstrates how to use OLE automation to create a mail merge in Microsoft Office Word 2003 using data from a Visual FoxPro (VFP) for Windows table.
MORE INFORMATION
Below is the Visual FoxPro for Windows code using the Word.Application object model that performs the mail merge using an ODBC data source to an existing Visual FoxPro table. This code can use an existing data source name (DSN), created in the ODBC Driver Manager, or it can create one on the fly utilizing an API call to add and use the DSN. In the example below, "Visual FoxPro Tables" is the DSN created in the ODBC Driver Manager and points to the \Samples\Data folder in the VFP home folder. These paths will most likely have to be changed to reflect the users' paths.
Note This code was tested with the Service release-1 (SR-1) for Word 97 and Microsoft Office Word 2003. Using prior versions of Word 97 causes a Type Mismatch and other OLE errors.
Create a program and enter the following code:
***/ Begin program code /*** * Code to create a new data source to a VFP table. * * Use the Declare DLL function to prototype the * SQLConfigDataSource function. * Using SQLConfigDataSource prevents having to go into * the ODBC Driver Manager and create the DSN. *** IF VAL(SUBSTR(VERSION(4),2,1))>7 lthemes=_screen.Themes _screen.Themes=.f. ENDIF DECLARE Integer SQLConfigDataSource in odbccp32.dll Integer, ; Integer, String, String *** * Create a string containing the settings appropriate to the driver. * The following is an example for the Microsoft VFP ODBC driver * accessing the Customer.dbf file. *** *** Change the path below to point to the Customer table *** *** in the \Samples\Data folder. *** settings="DSN=visual foxpro tables"+chr(0)+; "Description=VFP ODBC Driver"+chr(0)+; "SourceDB=e:\vfp7\samples\data"+chr(0)+; "SourceType=DBF" =SQLConfigDataSource(0,1,"Microsoft Visual FoxPro Driver",settings) * NOTE: Ensure there are no spaces on either side of the equal sign (=). ON ERROR DO errhand WITH ERROR(), MESSAGE(), MESSAGE(1), PROGRAM( ), LINENO( ) && Trap OLE & other errors. * Initialize variables passed to Word to create form letter. intro1="Congratulations! You are one of our best customers since you have ; purchased $" intro2=" each month from us. " intro3="As a result, your maximum order amount has been increased by ; $2500.00. If you have any questions, please feel free to contact us." oWord = CREATEOBJECT("Word.Application") WITH oWord * Assign values to variables dsname="e:\VFP7\SAMPLES\DATA\customer.DBF" wformat=0 wconfirmconv=0 wreadonly=0 wlinktosource=0 waddtofilelist=0 wpassworddoc="" wpasswordtemp="" wrevert=0 wprotectdoc="" wprotecttemp="" wconn="DSN=visual foxpro tables;uid=;pwd=;"+; "sourcedb=e:\vfp7\samples\data;sourcetype=dbf"+; "exclusive=no;backgroundfetch=yes;collate=machine;" wsqlstatement="SELECT contact,company,title,address,city,; postalcode,STR(maxordamt,12,2) as maxordamt FROM customer ; WHERE (customer.maxordamt>$100000)" .Visible=.T. && Make Word visible. .WindowState = 2 && Minimize Word. .Documents.Add && Add new document. .Selection.InsertParagraphAfter .ActiveDocument.MailMerge.OpenDataSource; (dsname,wformat,wconfirmconv,wreadonly,wlinktosource,; waddtofilelist,wpassworddoc,wpasswordtemp,wrevert,; wprotectdoc,wprotecttemp,wconn,wsqlstatement) .ActiveDocument.MailMerge.EditMainDocument .Selection.InsertDateTime("dddd, MMMM dd, yyyy", 1) .Selection.MoveRight .Selection.InsertParagraphAfter .Selection.MoveDown .ActiveDocument.MailMerge.Fields.Add; (oWord.Selection.Range,"contact") .Selection.InsertParagraphAfter .Selection.MoveDown .ActiveDocument.MailMerge.Fields.Add; (oWord.Selection.Range,"company") .Selection.InsertParagraphAfter .Selection.MoveDown .ActiveDocument.MailMerge.Fields.Add; (oWord.Selection.Range,"title") .Selection.InsertParagraphAfter .Selection.MoveDown .ActiveDocument.MailMerge.Fields.Add; (oWord.Selection.Range,"address") .Selection.InsertAfter(", ") .Selection.MoveRight .ActiveDocument.MailMerge.Fields.Add(oWord.Selection.Range,"city") .Selection.InsertAfter(" ") .Selection.MoveRight .ActiveDocument.MailMerge.Fields.Add; (oWord.Selection.Range,"postalcode") .Selection.MoveRight .Selection.InsertParagraphAfter .Selection.InsertParagraphAfter .Selection.InsertAfter("Dear ") .Selection.MoveRight .ActiveDocument.MailMerge.Fields.Add; (oWord.Selection.Range,"contact") .Selection.MoveRight .Selection.InsertAfter(":") .Selection.MoveRight .Selection.InsertParagraphAfter .Selection.InsertParagraphAfter .Selection.MoveDown .Selection.InsertAfter(intro1) .Selection.MoveRight .ActiveDocument.MailMerge.Fields.Add; (oWord.Selection.Range,"maxordamt") .Selection.MoveRight .Selection.InsertAfter(intro2) .Selection.MoveRight .Selection.InsertAfter(intro3) .Selection.MoveRight .Selection.InsertParagraphAfter .Selection.InsertParagraphAfter .Selection.MoveDown .Selection.InsertAfter("Thank you,") .Selection.MoveRight .Selection.InsertParagraphAfter .Selection.InsertParagraphAfter .Selection.MoveDown .Selection.Fields.Add(oWord.Selection.Range,-1,"author") .ActiveDocument.MailMerge.Destination = 0 && new document. .ActiveDocument.MailMerge.Execute && Run the merge. nanswer = MESSAGEBOX("Would you like to view the finished ; document?",36,"Switch to Word") DO CASE CASE nanswer = 6 .ActiveWindow.View.Type = 3 && Page layout view. .Visible = .T. .Application.Activate && Bring Word forward. .WindowState = 0 && Word in normal state. =INKEY(5) && Wait 5 seconds then bring VFP back. .WindowState = 2 && Then minimize Word. ENDCASE nanswer = MESSAGEBOX("Print the document?",36,; "Print the merged document.") DO CASE CASE nanswer = 6 && Yes .ActiveDocument.PrintOut && Print the merged form letter. ENDCASE nanswer = MESSAGEBOX("Save the document?",36,; "Enter the name to save the merged document.") DO CASE CASE nanswer = 6 && Yes cbaddoc=SPACE(25) DO WHILE .T. DEFINE WINDOW getname FROM 1,1 TO 15,60 ; FONT 'COURIER NEW',10 FLOAT GROW ZOOM ACTIVATE WINDOW getname cdoc=SPACE(25) mquit = SPACE(1) IF !EMPTY(cbaddoc) @2,1 SAY "Existing file name: "+cbaddoc COLOR R+/w ENDIF @4,1 SAY "Enter a name for the Word document." @5,1 GET cdoc color b/w VALID emptyChk(cdoc) @7,25 GET mquit FUNCTION '*T Save' SIZE 2,10 READ CYCLE CLEAR WINDOW getname cdoc=ALLTRIM(cdoc) cpath= .Options.DefaultFilePath(0) && Get Word's && default document path. IF FILE(cpath+"\"+cdoc+".doc") WAIT WINDOW "Document already exists! Enter another ; file name." TIME 1 cbaddoc=cdoc LOOP ELSE .ActiveDocument.SaveAs(cdoc) ENDIF .Documents.Close(0) .Application.Quit EXIT ENDDO CASE nanswer = 7 && No .Documents.Close(0) .Application.Quit ENDCASE ENDWITH ON ERROR && Restore system error handler. IF VAL(SUBSTR(VERSION(4),2,1))>7 _screen.Themes=lthemes endif PROCEDURE emptychk PARAMETER pcdoc IF !EMPTY(pcdoc) RETURN .T. ELSE WAIT WINDOW "Enter a name for the Word Document" RETURN 0 ENDIF PROCEDURE errhand PARAMETER merror, mess, mess1, mprog, mlineno CLEAR IF merror=1426 mlineno=LTRIM(STR(LINENO())) merror=LTRIM(STR(merror)) =MESSAGEBOX("You have an OLE error. Usually this is caused by quitting Word or canceling out of a dialog box in Word. "+CHR(13)+; "The error message is "+mess) ELSE mlineno=LTRIM(STR(LINENO())) merror=LTRIM(STR(merror)) =MESSAGEBOX("An error occured on line "+mlineno+; ". The error message is "+mess+" The error number is "+merror) ENDIF ON ERROR RETURN ***/ End program code /***
REFERENCES
For additional information about how to obtain Word for Windows 97 Service release 1 (SR-1), click the following article number to view the article in the Microsoft Knowledge Base:
172475 How to obtain and install MS Office 97 SR-1
Microsoft Visual Basic for Applications Help (Word '97); search on "OLE"
Keywords: kbhowto kbinterop kbautomation KB181926