SAMPLE: XLCLIENT: Automation Client for Excel

• Microsoft Excel
• Microsoft Visual C++ for Windows
• Microsoft OLE Libraries, used 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 Download Center. Click the file name below to download the file:

Xlclient.exe

http://www.microsoft.com/downloads/search.asp

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

• 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:

  Q112671 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 

• 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

Keywords : kbole kbsample kbAutomation kbExcel kbNTOS351 kbVC400
Version : WINDOWS:95; :
Platform : WINDOWS
Issue type :