Suche

ManualsBrandsAlle Produkte ManualsSoftwareSupport

Alle Produkte | Support | Suche | Microsoft.com Guide

Home Knowledge Base Suche FAQs Downloads Newsgroups Kontakt Seite empfehlen Drucken Hilfe

HOWTO: Automate Mail Merge to Word97 SR-1 Using OLE and ODBC (Q181926)

The information in this article applies to:

Microsoft Visual FoxPro for Windows, versions 3.0 , 3.0b , 5.0 , 5.0a , 6.0

Microsoft Data Access Components version 2.5

SUMMARY

This article demonstrates how to use OLE automation to create a mail merge in Word for Windows 97 SR-1 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 is pointing the VFP5a\Samples\Data folder. These paths will most likely have to be changed to reflect the

users' paths.

NOTE: This code only works with the Service release-1 (SR-1) for Word 97. Using prior versions of Word 97 causes a Type Mismatch and

other OLE errors.

Create a program and enter the following code:

1 von 8

07.01.02 09:41

Summary of content (48 pages)

PAGE 1
Alle Produkte Home Knowledge Base Suche FAQs Downloads Newsgroups Kontakt | Support | Suche Seite empfehlen | Microsoft.com Guide Drucken Hilfe HOWTO: Automate Mail Merge to Word97 SR-1 Using OLE and ODBC (Q181926) The information in this article applies to: Microsoft Visual FoxPro for Windows, versions 3.0 , 3.0b , 5.0 , 5.0a , 6.0 Microsoft Data Access Components version 2.
PAGE 2
***/ 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. *** DECLARE Integer SQLConfigDataSource in odbccp32.dll Integer, ; Integer, String, String *** * Create a string containing the settings appropriate to the driver.
PAGE 3
* Assign values to variables dsname="D:\VFP\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=d:\vfp\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.
PAGE 4
.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 .
PAGE 5
.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. .
PAGE 6
@7,25 GET mquit FUNCTION '*T Quit' 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.
PAGE 7
=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(mlineno)) merror=LTRIM(STR(merror)) =MESSAGEBOX("An error occured on line "+mlineno+; ".
PAGE 8
[Visiting page] [Visual FoxPro] [News] [Downloads] [Opinions] [About me] [Links] FAQ (DataEnvironment) List questions: Why the trigger gives out a mistake? How it is necessary to establish a buffering mode for data sources of form? Why there can be problems with deleting of records from the table, having Primary Key? Why created in Visual FoxPro the tables have not code page 1251? I have created Local view with two tables and use a keyword Join from View designer, and it does not work how I expected.
PAGE 9
Questions and answers: To end Question: Why the trigger gives out a mistake?. Answer: Because on the moment of performance of the trigger the data, do not satisfy to its requirements. To understand, what exactly occurs, it is best to look after the appropriate site of a code in debugger. Without the analysis of a concrete situation it is possible to assume, that the buffering of records the changeable table does not corresponding to logic of modification registered in a code.
PAGE 10
should follow at once for appropriate Join, while designer LocalView creates group of parameters ON at the end of the list of parameters Join (though I am not absolutely sure concerning last). I write the utility ModeView.exe, which will help you to make the appropriate updating Local view of a database, the truth it should use after any changes with preservation yours of Local view, containing Join directly ahead of their use in the appendix [to receive a copy ModeView.exe]. See also http://support.
PAGE 11
RETURN .F. ENDIF USE MyDbc!MyTable IN 0 SHARED To list questions. Question: It seems, that simple task: for example, there is a table of approximately such structure: a code of the client, date of payment, sum. The codes of the client in the table repeat.Is it possible by one inquiry to receive result: a code of the client - date of last payment - sum. And that should be broken into two subquery - ugly :-(. Answer: Something as next: SELECT * FROM MyDbс!Payments p ; WHERE STR(p.idclient) + DTOS(p.
PAGE 12
CleanUp() RETURN .F. ENDIF IF TYPE(’loSec.oDataEnvironment’) # ’O’ OR ISNULL(loSec.oDataEnvironment) CleanUp() RETURN .F. ENDIF * *-- Check opening table SET DATASESSION TO loSec.DataSessionId IF !USED(loSec.oDataEnvironment.InitialSelectedAlias) CleanUp() RETURN .F. ENDIF * *-- Use MyclsSession object SELECT (loSec.oDataEnvironment.
PAGE 13
SET EXCLUSIVE OFF *... add if need ENDPROC ENDDEFINE * *-- My Session class DEFINE CLASS MyclsSession AS Session DataSession = N_DATASESSION Name = ’MySession’ oDataEnvironment = NULL FUNCTION Init * *-- Set DataSession IF This.DataSession <> 1 SET DATASESSION TO This.DataSessionId ENDIF * *-- Create DE object This.oDataEnvironment = CreateObject(’MyclsDE’) * *-- OpenTables in DE object IF VARTYPE(This.oDataEnvironment) = ’O’ This.oDataEnvironment.OpenTables() ELSE RETURN .F.
PAGE 14
overwrite into value of the last created session - if the created object has property DataSession = 1-Default DS, - then into DS Default (1) To list questions. Question: I don’t create Connection object in database. Simply in a code I write lnHndl = sqlconnect (lcDSN, lcLogin, lcPassw) and would like to prevent error message, to treat it in program. How to me to make it?. Answer: Try to do =SQLSETPROP(0,"DispWarnings", .F.) =SQLSETPROP(0,"DispLogin", 3) && DB_PROMPTNEVER To list questions.
PAGE 15
table it is impossible at me. Why and how it can be made?. Answer: Infortunately in Update trigger it is impossible to make modifications datas, concerning which it is called, as an infinite recursion :-( and in Update trigger it is possible only to make changes to other table have communication from an initial table type one-to-one. Otherwise you are able to do it: - in Record Rule for table to call SP - in MyBaseForm.Save() - in events Valid() appropriate controls To list questions.
PAGE 16
www.mimer.com/developer 6.1 SQLConfigDataSource The following specification of SQLConfigDataSource is from the Microsoft ODBC SDK Reference. Summary SQLConfigDataSource adds, modifies, or deletes data sources. Syntax BOOL SQLConfigDataSource( HWND hwndParent , WORD fRequest , LPCSTR lpszDriver , 1 von 6 07.01.
PAGE 17
LPCSTR lpszAttributes ); Arguments hwndParent [Input] Parent window handle. The function will not display any dialog boxes if the handle is null. fRequest [Input] Type of request. fRequest must contain one of the following values: ODBC_ADD_DSN : Add a new user data source. : Configure (modify) an existing user data source. ODBC_CONFIG_DSN ODBC_REMOVE_DSN : Remove an existing user data source. ODBC_ADD_SYS_DSN : Add a new system data source.
PAGE 18
ODBC_REMOVE_SYS_DSN : Remove an existing system data source. : Remove the default data source specification section from the system information. It also removes the default driver specification section from the ODBCINST.INI entry in the system information. (This fRequest performs the same function as the deprecated SQLRemoveDefaultDataSource function.) When this option is specified, all of the other parameters in the call to SQLConfigDataSource should be NULLs. If they are not NULL, they will be ignored.
PAGE 19
Diagnostics When SQLConfigDataSource returns FALSE, an associated *pfErrorCode value may be obtained by calling SQLInstallerError. The following table lists the *pfErrorCode values that can be returned by SQLInstallerError and explains each one in the context of this function. Error Description *pfErrorCode ODBC_ERROR_ GENERAL_ERR General installer error. An error occurred for which there was no specific installer error. ODBC_ERROR_ INVALID_HWND Invalid window handle.
PAGE 20
ODBC_ADD_DSN ODBC_CONFIG_DSN ODBC_ERROR_ INVALID_REQUEST_ TYPE ODBC_REMOVE_DSN Invalid type of request. ODBC_ADD_SYS_DSN ODBC_CONFIG_SYS_DSN ODBC_REMOVE_SYS_DSN ODBC_REMOVE_DEFAULT_DSN ODBC_ERROR_ INVALID_NAME The lpszDriver argument was invalid. Invalid driver or translator name. It could not be found in the registry. ODBC_ERROR_ INVALID_KEYWORD_ Invalid keyword value pairs. VALUE The lpszAttributes argument contained a syntax error.
PAGE 21
uses the value of lpszDriver to read the full path of the setup DLL for the driver from the system information. It loads the DLL and calls ConfigDSN with the same arguments that were passed to it. SQLConfigDataSource returns FALSE if it is unable to find or load the setup DLL, or if the user cancels the dialog box. Otherwise, it returns the status it received from ConfigDSN. SQLConfigDataSource Upright Database Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 dbtechnology@upright.
PAGE 22
Related Sites Free webspace Register your own domain FoxPro TIPs(02) • Report •••••X•/•Y• lastpage = 0 REPORT FORMAT format PDSETUP TO FILE null: NOCONSOLE lastpage = _PAGENO REPORT FORMAT format PDSETUP TO PRINTER NOCONSOLE lastpage is the "y" field in your report... ••••••AP••• 640x480 • 632x452 ••••? **************************************************** * Clear SYSMENU and STATUS BAR preparatory to the * resizing of the screen.
PAGE 23
CLEAR **************************************************** * Resize the screen so that the application will * appear consistently in all screen resolutions * 640X480 and up. Also, clear "FoxPro" from the * title bar. A "full" screen is now 632X452 * (assumes SYSMETRIC(3)=4 and SYSMETRIC(4)=4).
PAGE 24
EXTERNAL ARRAY regfn EXTERNAL ARRAY callfn jcRetVal = "" SET LIBRARY TO SYS(2004) + "FOXTOOLS" ADDITIVE IF m.jcAction = "WRITE" jnFunc = RegFn("WritePrivateProfileString", "CCCC", "I") IF m.jnFunc >= 0 jnRetval = CallFn(m.jnFunc, m.lpszSection, ; m.lpszEntry, m.lpszString, ; m.lpszFilename) ENDIF ELSE IF EMPTY(m.lpszString) lpszString = SPACE(10) ENDIF lpszReturnBuffer = REPLICATE(" ", 255) cbReturnBuffer = 255 jnFunc = RegFn("GetPrivateProfileString", "CCC@CIC", "I") IF m.
PAGE 25
RELEASE LIBRARY SYS(2004) + "FOXTOOLS" RETURN LEFT(m.jcRetVal, LEN(m.jcRetval) - 1) VFP •••• #includediscalimer.h ************************************************** * in this version the data is encrypted when you leave the field * so the only time u see the field decrypted is when you edit it! * next version will encrypt/decrypt a whole form (e.g. encrypt * only when the "save changes" button is clicked * *-- Class: txtencbox (c:\vfp\testproj\ontime.vcx) *-- ParentClass: ottextbox (c:\vfp\testproj\ontime.
PAGE 26
*-- temporary value PROTECTED pctmpval DIMENSION panpasschars[8,1] *-- The Encrypting/Decrypting alogarithm PROTECTED PROCEDURE crypt dimensionlanPassChars[8,1] =acopy(this.panPassChars,lanPassChars) lnbase1=this.pnbase1 lnbase2=this.pnBase2 new_expr="" j=0 for j=1 to this.pnLenVal step 1 cur_chr=asc(substr(this.
PAGE 27
this.pnBase2=this.pnBase2-this.panPassChars[6] ENDPROC PROCEDURE LostFocus this.pcTmpVal=alltrim(this.Value) this.pnlenVal=len(this.pcTmpVal) this.crypt this.Value=this.pcTmpVal ENDPROC PROCEDURE GotFocus this.pcTmpVal=alltrim(this.value) this.pnLenVal=len(this.pctmpval) this.crypt this.Value=this.pcTmpVal ENDPROC ENDDEFINE * *-- EndDefine: txtencbox VFP ODBC ••• ODBC utilities for Visual FoxPro 3.
PAGE 28
******************************************************************** PROCEDURE aDSN LPARAMETER tcNombreArray #define MAX_DSN_LENGTH 30 #define MAX_DSN_DESC_LENGTH 300 DECLARE Short SQLAllocEnv IN ODBC32 Long @phenv DECLARE Short SQLFreeEnv IN ODBC32 Long henv DECLARE Short SQLDataSources IN ODBC32 Long henv, ; Long Size1, ; String @szDSN, ; Long Size2, ; Long @pcbDSN, ; String @szDescription, ; Integer Size3, ; Long @pcbDescription PUBLIC (tcNombreArray) henv = 0 IF SQLAllocEnv( @henv ) == 0 szDSN = REPLICA
PAGE 29
********************** *** End of aDSN ********************** ************************************************************************* *** *** aDrivers - Function for get all Drivers into Array *** String @szDescription, ; Integer Size3, ; Long @pcbDescription PUBLIC (tcNombreArray) henv = 0 IF SQLAllocEnv( @henv ) == 0 LOCAL szDSN, cbDSN, szDescription, cbDescription, nCont szDSN = REPLICATE( chr(0), MAX_DSN_LENGTH ) cbDSN = 0 szDescription = REPLICATE( chr(0), MAX_DSN_DESC_LENGTH ) cbDescription = 0 nCon
PAGE 30
*********************** *** End of aDrivers *** *********************** ELSE RETURN .T. ENDIF *********************** *** End of DeleteDS *** *********************** ******************************************************************** *** *** ModifyDS - Function for modify a Data Source of ODBC *** *** Parameters : 1.- Driver *** 2.- Name *** 3.
PAGE 31
proc File_list_to_table lparameters ltSubDirectories local lcDD, llSubdirectories, ; fPath, fDosName, fLongName, fDosExt, fDosTime, llIsFile, ; lcRunCmd, lcRunSub, lcOldDate ** set date to the way a dos directory stores it lcOldDate=set('date') set date to mdy ** lcDD is drive and directory to process into a table lcDD=getdir() ** If ltSubDirectories is .T. then all directories within lcDD will be processed too. if empty(ltSubDirectories) then llSubDirectories=.F.
PAGE 32
scan all do case case upper(ftempa)='DIRECTORY OF' m.fpath=allt(substr(ftempa,13))+'\' llisfile=.f. case substr(ftempa,16,5)==' ' or ; empty(ctod(substr(ftempa,29,8))) llisfile=.f. otherwise llisfile=.t. m.fdosname=allt(left(ftempa,8)) m.flongname=allt(substr(ftempa,45)) m.fdosext=substr(ftempa,10,3) m.fdostime=ctot(substr(ftempa,29,15)) endcase if llisfile=.t. then insert into flist from memvar endif endscan use delete file getenv('temp')+'\ftemp.
PAGE 33
Back • 12 von 12 07.01.
PAGE 34
SQLConfigDataSource (ODBC) The Microsoft® SQL Server™ ODBC driver supports the following SQL Server-specific keyword/value pairs for data source configuration attribute strings. Keyword Address AnsiNPW Values yes no AttachDBFileName file_path 1 von 10 Description Network address of the SQL Server. Default. Specifies that ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDING, and CONCAT_NULL_YIELDS_NULL are set ON for each connection. This allows SQL Server to treat SQL statements as per SQL-92.
PAGE 35
path, and escape any \ characters if using a C character string variable: AttachDBFileName=c:\\AB\\MyDB.mdf This database is attached and becomes the default database for the connection. To use AttachDBFileName you must also specify the database name in either the SQLDriverConnnect DATABASE parameter or the SQL_COPT_CURRENT_CATALOG connection attribute. If the database was previously attached, SQL Server will not reattach it; it will use the attached database as the default for the connection.
PAGE 36
server: Client SQL_C_CHAR data sent to a SQL Server char, varchar, or text variable, parameter, or column is converted from character to Unicode using the client ANSI code page (ACP), then converted from Unicode to character using the ACP of the server. SQL Server char, varchar, or text data sent to a client SQL_C_CHAR variable is converted from character to Unicode using the server ACP, then converted from Unicode to character using the client ACP.
PAGE 37
These settings have no effect on the conversions that occur for these transfers: Unicode SQL_C_WCHAR client data sent to char, varchar, or text on the server. char, varchar, or text server data sent to a Unicode SQL_C_WCHAR variable on the client. ANSI SQL_C_CHAR client data sent to Unicode nchar, nvarchar, or ntext on the server. Unicode char, varchar, or text server data sent to an ANSI SQL_C_CHAR variable on the client. no 4 von 10 Do not perform character translation. 07.01.
PAGE 38
The SQL Server ODBC driver does not translate client ANSI character data sent to char, varchar, or text variables, parameters, or columns on the server. No translation is performed on char, varchar, or text data sent from the server to character variables on the client. If the client and SQL Server installation are using different ACPs, then extended characters can be misinterpreted. Database 5 von 10 Name of the default database for the connection.
PAGE 39
Description Driver {SQL Server} Fallback (SQL Server 6.5 only) yes no Language Network 6 von 10 Descriptive text. The description appears with the data source in the ODBC Management utility. Driver name. The braces are required when using version 2.65 or earlier of the SQL Server ODBC driver. Fallback connection attempts are made if a connection to the primary server fails. Available only when connecting to SQL Server 6.5. Default. Fallback connection attempts are not made. SQL Server language name.
PAGE 40
QueryLog_On yes no QueryLogFile QueryLogTime QuotedId yes no 7 von 10 Enables logging of long-running queries. Default. Disables logging of long-running queries. Full path and name of the file used to log long-running queries. Digit character string specifying the threshold (in milliseconds) for logging long-running queries. Any query that does not get a response in the time specified is written to the long-running query log file. Default.
PAGE 41
Regional yes no Server StatsLog_On 8 von 10 yes regarding the use of quotation marks in SQL statements. Respect client workstation settings for region when converting date, time, and currency values to character strings. This setting should only be specified for applications that only display data, not for applications that process data. Default. Use ODBC-defined character formats for date, time, and money conversion. Name of a server running SQL Server on the network.
PAGE 42
no StatsLogFile Trusted_Connection yes No UseProcForPrepare 0 (SQL Server 6.5 or earlier only) 1 2 9 von 10 Default. Disables driver performance logging. Full path and name of the file used to record SQL Server ODBC driver performance statistics. Windows NT Authentication is enabled for the data source. Default. SQL Server Authentication is enabled for the data source. A SQL Server login and password must be specified for each connection.
PAGE 43
Note Regional conversion settings apply to currency, numeric, date, and time data types. The conversion setting is only applicable to output conversion and is only visible when currency, numeric, date, or time values are converted to character strings. The driver uses the locale registry settings for the current user. The driver does not honor the current thread’s locale if the application sets it after connection by, for example, calling SetThreadLocale.
PAGE 44
About Us | Contact Us | Site Policies Click Above to Support Our Sponsor! HANDLE PASSWORD Be Notified Of Responses To Your Posts Keyword Search Turn Off Ad Banners One-Click Access To Your Favorite Forums Automated Signatures On Your Posts Tell Others About Yourself In Your Personal Profile Best Of All, It's Free! E-mail* Handle Select A Type 1 von 5 Find An Expert Home > Forums > Programmers > Languages Visual Basic(Microsoft) -Version 5 & 6 (17469 members) Remember Me Forgot Password? Join Us!
PAGE 45
Select A Type Password Verify P'word *Opt in e-mail system. Click Personal Profile after login to opt out. "Best Of Breed" Forums Add Stickiness To Your Site (Download This Button Today!) "...Just to let you know...what a great site you have. I posted a pretty generic question yesterday and have had 8 responses already, anyway thanks again and keep up the good work..." More... Server, such as SQL Server Database = "DATABASE=" & Trim(txtDatabase.
PAGE 46
End Sub Private Sub CreateAccessODBC() ’This common setup for an Access Database Dim Dim Dim Dim Dim Driver DSN Server Database Description As As As As As String String String String String DSN = "DSN=" & Trim(AccessDSNtxt.Text) Database = "DBQ=" & Trim(AccessDBQTxt.Text) ’this is your physical path to the *.mdb Description = "DESCRIPTION=" & Trim(AccessDesc.
PAGE 47
Public Public Public Public Public Public Public Public Public Public Public Public Public Public Public Public Public Public Public Public Public Public 22 Const Const Const Const Const Const Const Const Const Const Const Const Const Const Const Const Const Const Const Const Const Const ODBC_ERROR_GENERAL_ERR = 1 ODBC_ERROR_INVALID_BUFF_LEN = 2 ODBC_ERROR_INVALID_HWND = 3 ODBC_ERROR_INVALID_STR = 4 ODBC_ERROR_INVALID_REQUEST_TYPE = 5 ODBC_ERROR_COMPONENT_NOT_FOUND = 6 ODBC_ERROR_INVALID_NAME = 7 ODBC_ERR
PAGE 48
Back to Visual Basic(Microsoft) -Version 5 & 6 FAQ Index Back to Visual Basic(Microsoft) -Version 5 & 6 Forum Email This FAQ To A Friend Copyright © 1998-2001 Tecumseh Group, Inc. All rights reserved. Unauthorized reproduction forbidden. 5 von 5 07.01.