Click here to Skip to main content
Click here to Skip to main content

How To Get Properties and Methods in Late Binding COM-Apps Like Excel

, 29 Jun 2005 CPOL
Rate this:
Please Sign up or sign in to vote.
The article describes how to find the methods and properties of an COM-Object (like Excel), which is unknown at compilation time (late binding).

Introduction

If you want to automate Microsoft Excel (or any other COM-Object) from a C#-app, you have two possibilities:

  • Early Binding: If you know, which Excel-version is on the target-machine of your customer (and you are so lucky, that you have this version too, you can do an early binding, i.e. the binding between your app and Excel will be done at compilation-time. You can read more about this here.
  • Late Binding: If you do not know, which version will run on your target-machine, you have to use late binding. The binding to Excel will be done at runtime.

In late binding, there is unfortunately (almost) no support of the methods and properties of the Excel-Object at compile time in Visual Studio .NET. I had to automate Excel with a chart and it was a hard trial and error time to get the appropriate methods and properties. But there is a way to go through (of course, I found it at the very end...) .

Step 1: Write a VBA-script in Excel

Just to be sure, write a VisualBasic Application (VBA) macro in Excel. Then you are sure, that the steps you want to go are really possible. Here you have the support even for one version.

Step 2: Open Excel from your C# App

The technique to do a late binding is described here. First, you have to start the Application out of the registry.

First some definitions:

using System;
using System.Reflection;
...
public class ExcelCommander
{
    const int xlAutomatic=-4105;
    const int xlManual   =-4135;
    const int xlUpward    =-4171;
    const int xlWait    =2;
    object oApp;
    object oBook;
    object oBooks;
    object oSheets;
    object oSheet;
    object objRange_Late;
    object oCharts;
    object oChart;
    object oPoints;
    object oPoint;
    object oAxisX;
    object oAxisY;
    object oCursor;
    object[] Parameters;

(If you look for the values of the xlFoo constants: Open the direct-window in VBA and enter the name of the constant. If you are looking for the constant-names itself: seek in the object-browser in VBA.)

Now we do the job: start Excel:

// Get the class type and instantiate Excel.
Type objClassType;
objClassType = Type.GetTypeFromProgID("Excel.Application");
oApp = Activator.CreateInstance(objClassType);

Now we have a reference to the Excel-application.

Step 3: Invoke the Members, Methods and Properties

Before I describe, how to get the members and properties here's the calling technique: it is done in one call:

obj.GetType().InvokeMember(sProperty,BindingFlags, null, obj, oParam );

With:

  • sProperty: Name of the property or method (string)
  • BindingFlags.GetProperty to get a property
  • BindingFlags.SetProperty to set a property
  • BindingFlags.InvokeMethod to call a method
  • obj: Reference to the COM-Object
  • oParam: List of parameters for this call

For convenience, I wrote some wrappers for these calls:

#region private Wrappers
private void SetProperty(object obj,string sProperty,object oValue)
{
    object[] oParam=new object[1];
    oParam[0]=oValue;
    obj.GetType().InvokeMember(sProperty,BindingFlags.SetProperty, null, obj, oParam );
}
private object GetProperty(object obj,string sProperty,object oValue)
{
    object[] oParam=new object[1];
    oParam[0]=oValue;
    return obj.GetType().InvokeMember
        (sProperty,BindingFlags.GetProperty, null, obj, oParam );
}
private object GetProperty(object obj,string sProperty,object oValue1,object oValue2)
{
    object[] oParam=new object[2];
    oParam[0]=oValue1;
    oParam[1]=oValue2;
    return obj.GetType().InvokeMember
    (sProperty,BindingFlags.GetProperty, null, obj, oParam );
}
private object GetProperty(object obj,string sProperty)
{
    return obj.GetType().InvokeMember
    (sProperty,BindingFlags.GetProperty, null, obj, null );
}
private object InvokeMethod(object obj,string sProperty,object[] oParam)
{
    return obj.GetType().InvokeMember
    (sProperty,BindingFlags.InvokeMethod, null, obj, oParam);
}
private object InvokeMethod(object obj,string sProperty,object oValue)
{
    object[] oParam=new object[1];
    oParam[0]=oValue;
    return obj.GetType().InvokeMember
    (sProperty,BindingFlags.InvokeMethod, null, obj, oParam );
}
#endregion

Now we can do the job with little effort.

We open a Workbook:

//Get the workbooks collection.
oBooks=GetProperty(oApp,"Workbooks");
//Add a new workbook.
Parameters[0]=System.Windows.Forms.Application.StartupPath+@"\EndoTherm.xlt";
oBook=InvokeMethod(oBooks,"Add",Parameters);

As you can see, I opened it with a template. In this way, it is possible to do most of the job (creating a Chart, etc.) already in the template. So we need only import the data values and do some adjustment.

Next, we look for a single Worksheet and do some "Cosmetics":

//Get the worksheets collection.
oSheets = GetProperty(oBook,"Worksheets");
//Get the first worksheet.
oSheet = GetProperty(oSheets,"Item","Temp");
SetProperty(oApp,"Calculation",xlManual);
SetProperty(oApp,"Visible",true);
oCursor=GetProperty(oApp,"Cursor");
SetProperty(oApp,"Cursor",xlWait);
SetProperty(oApp,"UserControl", false);

Due to the fact that the template already has a chart, we can go further and load the references too.

Note: Such properties are only addressable if they are defined in the Excel-sheet. Of course! (I spent a lot of time to learn this...)

//Get the first Diagram.
oCharts=GetProperty(oSheet,"ChartObjects");
oChart=GetProperty(oCharts,"Item",1);
oChart=GetProperty(oChart,"Chart");
//Get Axis
oAxisY=GetProperty(oChart,"Axes");
oAxisX=GetProperty(oAxisY,"Item",1);
oAxisY=GetProperty(oAxisY,"Item",2);

Now we can fill in the data. e.g.: Cell(1,1,"Hello World")

public void Cell(int Row,int Col,object Value)
{
oRange = GetProperty(oSheet,"Range",(char)col+Row.ToString(),Missing.Value);
SetProperty(oRange,"Value",Value);
}

Or set the Minimum and Maximum of the chart:

public ChartMiniMax(double Xmin,double Xmax,double Ymin,double Ymax)
{
SetProperty(oAxisX,"MinimumScale",Xmin);
SetProperty(oAxisX,"MaximumScale",Xmax);
SetProperty(oAxisY,"MinimumScale",Ymin);
SetProperty(oAxisY,"MaximumScale",Ymax);
}

At least we close the whole business. And here is a problem: the number of parameters depends on the version of Excel. V9.0 uses 9 parameters but V10.0 uses 10 for a "SaveAs" so we have to do some preparing:

SetProperty(oApp,"StatusBar","Ready");
//get Excel-version as integer
switch((int)Single.Parse((string)GetProperty(oApp,"Version")))        
{ 
 case 9:
 {
  Parameters = new Object[9];
  break;
 }
 case 10:
 {
  Parameters = new Object[10];
  break;
 }
 default:
 {
  Parameters = new Object[9];
  break;
 }
}
SetProperty(oApp,"Calculation",xlAutomatic);
SetProperty(oApp,"UserControl", true);
SetProperty(oApp,"Cursor",oCursor);
Parameters[0] = sFileName;
for (int i=1;i<Parameters.Length;i++)Parameters[i] = Missing.Value;
InvokeMethod(oSheet,"SaveAs",Parameters );
//Return control of Excel to the user.
SetProperty(oApp,"Visible",true);

In my application, I imported some DataLabels too. But for brevity, I omit this part. Up to here, the code is only meant as an example. The main question is still unanswered.

How to Get the Members, Properties and Methods?

Your VBA-macro gives you an idea, which members, methods and properties you need. Now include a reference to your Excel-application:

  • In Visual Studio .NET, Project-Explorer select References
  • Select Add reference
  • Select COM Tab
  • Select Microsoft Excel Open the Object browser and goto "Excel". There you will find all methods and properties.

For methods (marked with a margenta icon), use the InvokeMethod(...) wrapper.

For properties (white and grey icon), use the GetProperty(...) or SetProperty(...)wrapper.

If you have to get an instance of a collection use the "Item" GetProperty(...,"Item",...). (it seems that you can also use the InvokeMethod(...,"Item",...). In this case, click on the "Item"-item in the object-browser. In the bottom of the window, you see the type of the returned object. Select this object in the object-browser to see its members, properties and methods. In the next step, you have to use the reference to this member.

We saw it already:

oCharts=GetProperty(oSheet,"ChartObjects");
oAxisY=GetProperty(oChart,"Axes");
oAxisX=GetProperty(oAxisY,"Item",1);
SetProperty(oAxisX,"MinimumScale",Xmin);

A member of ChartObjects is the collection Axes. The first Member of this Axes-collection is the X-axis. You can now use this reference to set or get the properties.

I hope this helps you a little to shorten the way to an Excel solution.

History

  • 29th June, 2005: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ariadne
Software Developer (Senior) EndoTherm GmbH
Switzerland Switzerland
My first approach to computers was in 1970 a IBM1160, a real desk computer, this means it was as large as a desk and had a cartreader and 32 kByte Memory! But it runs FORTRAN! Ok 'run' is a little too fast

Later I was very proud on my Comodore CBM8032 32KbRAM+32KbRom (uff!) and 2 (in words 'TWO') floppies. But it was able to run a Basic-Interpreter. (There was a young gay in USA, who invent and developed the garbage collecter for such interpreter, his name was Bill G.)

Unfortunatly this CBM8032 burned away due to some "experiements"...

Ok, later I studied physics and work now since 20 years in the cancer research in Switzerland. My own little company Ariadne-Consulting delivers the login name:

Ariadne


Comments and Discussions

 
QuestionType.Getproperties() for Word COM object showing inconsistent behavior PinmemberMember 856121015-Feb-14 7:13 
QuestionHow to cache the method or a property [modified] PingroupExcellentOrg8-Apr-13 20:52 
AnswerRe: How to cache the method or a property PinmemberAriadne9-Apr-13 4:53 
GeneralRe: How to cache the method or a property PingroupExcellentOrg9-Apr-13 11:03 
GeneralRe: How to cache the method or a property PinmemberAriadne9-Apr-13 11:11 
QuestionHow to add macro PinmemberMad4runing27-Jan-11 9:35 
AnswerRe: How to add macro PinmemberAriadne27-Jan-11 13:04 
GeneralRe: How to add macro PinmemberMad4runing28-Jan-11 5:09 
GeneralRe: How to add macro PinmemberAriadne4-Feb-11 4:18 
GeneralRe: How to add macro PinmemberMad4runing11-Feb-11 4:38 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150327.1 | Last Updated 29 Jun 2005
Article Copyright 2005 by Ariadne
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid