SAMPLE: XLCLIENT: Automation Client for Excel

ID: Q141759

The information in this article applies to:

Microsoft Excel 95
Microsoft Visual C++ 4.0

Microsoft OLE libraries included with:

    - Microsoft Windows NT 3.5.1
    - Microsoft Windows 95

SUMMARY

XLCLIENT demonstrates how to control Microsoft Excel using OLE Automation. The MFC directory contains a sample that uses MFC. The SDK directory contains a sample that does not use MFC.

MORE INFORMATION

The following file is available for download from the Microsoft Software Library:

 ~ XLCLIENT.EXE (size: 142384 bytes)

For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q119591
   TITLE     : How to Obtain Microsoft Support Files from
               Online Services

MORE INFORMATION

Excel exposes its objects, properties and methods so that a client can access them through OLE Automation. This sample shows how to write an automation client that is equivalent to the following Visual Basic 4.0 code which inserts values into cells and creates a chart of those values.

Const xlWorkSheet = -4167 Const xl3DPie = -4102 Const xlRows = 1

Set application = CreateObject("Excel.Application") application.Visible = True Set workbook = application.Workbooks.Add(template:=xlWorkSheet) Set worksheet = workbook.Worksheets(1) worksheet.Range("A1:D1").Value = Array("North", "South", "East", "West") worksheet.Range("A2").Value = 5.2 worksheet.Range("B2").Value = 10 worksheet.Range("C2").Value = 8 worksheet.Range("D2").Value = 20 Set range = worksheet.Range("A1:D2") Set chart = workbook.Charts.Add chart.ChartWizard Source:=range, gallery:=xl3DPie,

    Format:=7, plotBy:=xlRows, categoryLabels:=1,
    seriesLabels:=0, hasLegend:=2, Title:="Sales Percentages"

workbook.Saved = True

' So that Excel won't ask whether to save this document on close.

How to Write an Automation Client for Microsoft Excel

An automation client uses the IDispatch interface to control Microsoft Excel. The following information is applicable when writing an automation client for Microsoft Excel:

Read the documentation of the Microsoft Excel automation methods/properties that you plan to use in the Vba_xl.hlp file that is shipped with Excel.

The values of Microsoft Excel constants like xlWorkSheet, xl3DPie, xlRows etc. can be obtained from the following knowledge base article:

ARTICLE-ID: Q112671

   TITLE     : Excel AppNote: Built-in Constants in VB, Applications
               Edition

   These constant values can also be obtained from Microsoft Excel's type
   library by using a type library browser like Ole2vw32.exe (in the
   compiler's \bin directory). Open Microsoft Excel's type library (for
   example, Xl5en32.olb for the U.S. version of Microsoft Excel 95) using
   the File/ViewTypeLibrary menu of Ole2vw32.exe. Drop the typeinfos
   combobox and select "constants". The Microsoft Excel constants will be
   displayed in the Variables/DataMembers list box.

All arguments and return types of Microsoft Excel automation methods and types are of type VARIANT. The actual types that need to be filled into and retrieved from a VARIANT are not explicitly stated in Vba_xl.hlp. However the types can be easily guessed from the documentation. For example, a number can be passed as VT_I2, a string as VT_BSTR and an object as VT_DISPATCH. Microsoft Excel will do the appropriate coercions if required. Values returned by Microsoft Excel can be similarly guessed. If the exact type of the return value cannot be determined check the VARIANT.vt field or coerce the return type using VariantChangeType. As mentioned in the "Microsoft Excel Specifics" section of Chapter 7 in the Microsoft Excel Developer's Kit in the Office Developer's Kit on the MSDN CD, Microsoft Excel will never return a VARIANT with a datatype that is not in the following list:
```
   Data Type                               Variant Constant
   ---------                               ----------------
   Boolean                                 VT_BOOL
   Currency (scaled integer)               VT_CY
   Date                                    VT_DATE
   Error cell values (#N/A, #REF etc.)     VT_ERROR
   Integer                                 VT_I2
   Long (long integer)                     VT_I4
   Object                                  VT_DISPATCH
   Single (single-precision floating-point)VT_R4
   Double (double-precision floating-point)VT_R8
   String                                  VT_BSTR
```

The following information is applicable to any automation client:

OLE uses Unicode strings. The OLESTR macro can be used to convert string constants to Unicode strings. In MFC, variables that contain strings can be converted to and from Unicode using the T2OLE and OLE2T macros. See MFC TechNote 59 for details. Code that does not use MFC can find information for ANSI/Unicode conversion in the following knowledge base article:
How to Convert from ANSI to Unicode & Unicode to ANSI for OLE ID: Q138813
The caller of an OLE API or interface method is responsible for freeing arguments and return values.

How to Write an MFC Automation Client for Microsoft Excel

1. Use AppWizard to create an application with automation support.

2. Select the OLE Automation tab in Class Wizard and select AddClass/From

   an OLE TypeLibrary. Select Excel's type library (Xl5en32.olb for U.S.
   version of Microsoft Excel 95) in the Microsoft Excel directory. Select
   the Microsoft Excel objects that you will use. ClassWizard will create a
   COleDispatchDriver derived proxy class for each object that you select.
   The sample selected the Application, Range, Workbook, Workbooks,
   Worksheet, Chart and Charts Excel objects and ClassWizard created the
   new classes in Xl5en32.h and Xl5en32.h. Modify the name of the DialogBox
   method of the Range object to a different name (for example, DBox)
   because the system header files define DialogBox as DialogBoxA or
   DialogBoxW.

3. Include the header file generated in Step 2 into the .cpp file that will

   contain the code to control Microsoft Excel.

4. Optional arguments which are not passed can be indicated by passing a

   VARIANT containing VT_ERROR/DISP_E_PARAMNOTFOUND.

5. Some Microsoft Excel methods can be called using two syntaxes, as

   documented in Vba_xl.hlp. For example, the Workbooks method is
   documented as follows:

   Syntax 1
   object.Workbooks(index)

   Syntax 2
   object.Workbooks

   ClassWizard will generate only one method. So if you need to use the
   other syntax, modify the generated .h and .cpp file to add another
   method for the other syntax. For example this sample added Workbooks2 to
   the Application class as follows to correspond to syntax 2 of the
   WorkBooks method:

   VARIANT Application::Workbooks2()
   {
   VARIANT result;
   InvokeHelper(0x23c, DISPATCH_METHOD, VT_VARIANT, (void*)&result, NULL);
   return result;
   }

   Workbooks2 has the same DISPID (0x23c) as Workbooks but does not have
   any arguments. This problem cannot be resolved by passing
   VT_ERROR/DISP_E_PAMNOTFOUND. All methods marked as having two syntaxes
   must be handled in this manner.

   The sample uses the Range, Workbooks and Charts methods, all of which
   have two syntaxes. Consequently it adds Range1, Workbooks2 and Charts2.

6. MFC's COleDispatchDriver class doesn't support named arguments.

   Arguments must be passed by position.

7. If Microsoft Excel raises an exception it will return DISP_E_EXCEPTION

   from IDispatch::Invoke and will fill EXCEPINFO parameter of this method.
   MFC will throw a COleDispatchException when this happens and the client
   can obtain the error information in the EXCEPINFO structure by catching
   this exception.

8. The code in Doc.cpp in the sample demonstrates how to control Microsoft

   Excel using the MFC classes.

How to write a non-MFC automation client for Microsoft Excel

1. The helper functions, CreateObject and Invoke, in Invhelp.cpp and

   Invhelp.h of the BROWSE sample in the Win32 SDK can be used to easily
   write a non-MFC automation client. Another approach is to call
   IDispatch::Invoke directly. Invoke uses late-binding to control a
   server. It can be made more efficient by modifying it to use id-binding
   in which the DISPID is obtained from the type-library rather than
   through IDispatch::GetIDsOfNames.

2. The Invoke helper function doesn't support named arguments. Arguments

   must be passed by position.

3. The Invoke helper function allows an EXCEPINFO structure to be passed as

   one of the parameters. Microsoft Excel will fill this structure with
   error information if it raises an exception and if an EXCEPINFO
   structure is provided by the client. Invoke will return DISP_E_EXCEPTION
   when Microsoft Excel raises an exception. The strings in the EXCEPINFO
   structure must be freed by the client. The sample code does not pass an
   EXCEPINFO structure to Microsoft Excel.

4. The code in Xlclient.cpp in the sample demonstrates how to control

   Microsoft Excel.

Additional query words: Excel Automation Controller MFC Client KBCategory: kbfile kbwebcontent KBSubcategory: LeTwoAt

Version           : 4.00 | 4.00
Platform          : NT WINDOWS

Last Reviewed: December 8, 1998