4.00 | 4.00
WINDOWS | WINDOWS NT
kbfile kbwebcontent
The information in this article applies to:
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:
How to Write an MFC Automation Client for Microsoft Excel
- Use AppWizard to create an application with automation support.
- 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.
- Include the header file generated in Step 2 into the .cpp file that will
contain the code to control Microsoft Excel.
- Optional arguments which are not passed can be indicated by passing a
VARIANT containing VT_ERROR/DISP_E_PARAMNOTFOUND.
- 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.
- MFC's COleDispatchDriver class doesn't support named arguments.
Arguments must be passed by position.
- 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.
- 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
- 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.
- The Invoke helper function doesn't support named arguments. Arguments
must be passed by position.
- 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.
- The code in Xlclient.cpp in the sample demonstrates how to control
Microsoft Excel.
|