SAMPLE: XLCLIENT: Automation Client for Excel

Last reviewed: October 10, 1997
Article ID: Q141759
4.00    | 4.00
WINDOWS | WINDOWS NT kbfile kbwebcontent

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.

You can find XLCLIENT.EXE (size: 142384 bytes) 
                         , a self-extracting file, on these services:

  • Microsoft's World Wide Web site on the Internet

          On the www.microsoft.com home page, click the Support icon
          Click Knowledge Base, and select the product
    
          Enter kbfile XLCLIENT.EXE (size: 142384 bytes) 
                                   , and click GO!
          Open the article, and click the button to download the file
    
    
  • Internet (anonymous FTP)

          ftp ftp.microsoft.com
          Change to the Softlib/Mslfiles folder
          Get XLCLIENT.EXE (size: 142384 bytes) 
    

    - Microsoft Download Service (MSDL)

          Dial (206) 936-6735 to connect to MSDL
          Download XLCLIENT.EXE (size: 142384 bytes) 
    
    
    For additional information about downloading, 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 reference words: Excel Automation Controller MFC Client
    KBCategory: kbfile kbwebcontent
    KBSubcategory: LeTwoAt
    Keywords : LeTwoAt kbfile kbwebcontent
    Version : 4.00 | 4.00
    Platform : NT WINDOWS


    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: October 10, 1997
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.