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

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

• 29^th June, 2005: Initial post