How to convert from ExcelReference to Microsoft.Office.Interop.Excel.Range

[RK]

Hi,

I've searched high and low for an answer to this and perhaps I am missing something obvious, but I cannot work it out. I have an ExcelReference and need to convert it to an object of type Microsoft.Office.Interop.Excel.Range.

I can do the opposite very easily (although this may not be the most efficient way).

using Excel = Microsoft.Office.Interop.Excel;

....

 public static ExcelReference rangeToExcelReference(Excel.Range range)

       {

            return new ExcelReference(range.Row - 1,

                                      range.Row - 1 + range.Rows.Count - 1,

                                      range.Column - 1,

                                      range.Column - 1 + range.Columns.Count - 1,

                                      range.Worksheet.Name);

        }

This is for a COM interface that I am developing using the new COM functionality which works brilliantly.

 

[Govert van Drimmelen]

Hi RK,

I post an example below.
It's all late-bound, but with a reference to the Primary Interop
Assembly or the NetOffice assemblies it would be a bit neater.

<DnaLibrary Language="C#" >
<![CDATA[
using System.Reflection;
using ExcelDna.Integration;
public static class RangeTools
{

[ExcelFunction(IsMacroType=true)]
public static object TestReferenceToRange()
{
ExcelReference caller =
(ExcelReference)XlCall.Excel(XlCall.xlfCaller);
object callerRange = ReferenceToRange(caller);
object callerAddress =
callerRange.GetType().InvokeMember("Address",
BindingFlags.Public | BindingFlags.GetProperty,
null, callerRange, null );
return callerAddress;
}

private static object ReferenceToRange(ExcelReference xlref)
{
object app = ExcelDnaUtil.Application;
object refText = XlCall.Excel(XlCall.xlfReftext, xlref, true);
object range = app.GetType().InvokeMember("Range",
BindingFlags.Public | BindingFlags.GetProperty,
null, app, new object[] {refText});
return range;
}
}
]]>
</DnaLibrary>


In VB.NET it's pretty simple:

Imports ExcelDna.Integration.XlCall
...
Private Function ReferenceToRange(xlref as ExcelReference) as Object
Dim app As Object = ExcelDnaUtil.Application
ReferenceToRange = app.Range(Excel(xlfRefText, xlref, True))
End Function

-Govert

[Qish]

Hi Govert,

Thanks for your prompt response. I will give it a shot, probably on Monday now.

Could you explain to me why it is so much simpler in VB.NET and why the Range method of the app object is not exposed in C#. As you may have guessed, I'm new to this area having come from a Java background.

Thanks.

[Govert van Drimmelen]

Hi,

The late-bound reflection-based way that I posted above is nice
because there are no dependencies on interop assemblies and it runs
on .NET 2.0. Given these constraints, VB.NET would indeed be nicer
since it supports late-binding, which makes calling the COM objects
easier.

If your constraints or environment differs, you might have other
options. C# 4 is as and succinct - I paste the C# 4 dynamic version
below. Also, as I noted, "with a reference to the Primary Interop

Assembly or the NetOffice assemblies it would be a bit neater. "

Some questions that might be relevant to your choices include:
* What version of .NET (or Visual Studio) are you using?
* How will your add-in be used, and what can you install together with
your add-in? Can you assume .NET 4 will be installed?
* What versions of Excel will you be targeting?

The Range property of the Application object _is_ exposed in C#. The
COM object model is exactly the same in all .NET langauges, and is
exactly the COM object model that you might know from VBA.

-Govert

<DnaLibrary RuntimeVersion="v4.0" Language="C#">
<![CDATA[

using ExcelDna.Integration;
public static class RangeTools
{
[ExcelFunction(IsMacroType=true)]

public static object TestReferenceToRangeX()
{
ExcelReference caller =
(ExcelReference)XlCall.Excel(XlCall.xlfCaller);
dynamic callerRange = ReferenceToRange(caller);
return callerRange.Address;
}

private static dynamic ReferenceToRange(ExcelReference xlref)
{
dynamic app = ExcelDnaUtil.Application;
return app.Range[XlCall.Excel(XlCall.xlfReftext, xlref,
true)];
}
}
]]>
</DnaLibrary>

[RK]

Hi Govert.

I tried to tidy things up with the Excel Interop libraries but couldn't find what I needed. Like I said, I'm new to this area!

But I got it working with your first example.

I'm coding for .NET versions 3.5 and 4 with Excel 2007 (12) so I have the MS Ofiice Interop libraies referenced.

Thanks for all your help.

[Govert van Drimmelen]

Hi,

If you have the interop assemblies, you can use them to early-bind.
Whether you use C# 3 or C# 4 makes a big difference to how easy the
COM libraries are to call from C#.

For .NET 2.0 (and 3.5) I paste an example that uses the Primary
Interop Assemblies below. Note we need to explicitly deal with all the
Missing arguments, and call properties that have parameters with the
get_XXX helpers.

For .NET 4 it should be much nicer - you can just change the type
declarations in the Dynamic .NET 4 sample to Application and Range,
and it should work.

Regards,
Govert

<DnaLibrary Language="C#" >
<Reference Name="Microsoft.Office.Interop.Excel" />
<![CDATA[

using System.Reflection;
using Microsoft.Office.Interop.Excel;

using ExcelDna.Integration;

public static class RangeTools
{

// Set '_' as alias for the 'Missing' value.
public static Missing _ = Missing.Value;

[ExcelFunction(IsMacroType=true)]
public static object TestReferenceToRange()
{
ExcelReference caller =
(ExcelReference)XlCall.Excel(XlCall.xlfCaller);

Range callerRange = ReferenceToRange(caller);
string callerAddress =
callerRange.get_Address(_,_,XlReferenceStyle.xlA1,_,_);
return callerAddress;
}

private static Range ReferenceToRange(ExcelReference xlref)
{
Application app = (Application)ExcelDnaUtil.Application;
string refText = (string)XlCall.Excel(XlCall.xlfReftext,
xlref, true);
Range range = app.get_Range(refText, _);
return range;
}
}
]]>
</DnaLibrary>

[Mark]

Govert,
         how do you go about extracting a range from an ExcelReference if the whole process wasn't started from a cell function call?
I have a Wpf control hosted in a WinForm that is displayed via a ribbon button click. In this form I am trying to get the user to select cells by invoking the Excel Inputbox with Type: 8. This works fine and I can get back an ExcelReference. In another part of the code I wish to take this ExcelReference and get the CurrentRegion for the range it represents. Any attempt to call the necessary XlCall functions results in

An unhandled exception of type 'System.Runtime.Serialization.SerializationException' occurred in Unknown Module.
Additional information: Type 'ExcelDna.Integration.XlCallException' in Assembly 'ExcelDna.Integration, Version=0.32.5236.31783, Culture=neutral, PublicKeyToken=null' is not marked as serializable.

I figure this is because the process did not initiate in any way through a cell, but is it just this or is it a threading issue? I can't fathom why the xlApp.InputBox works but the xlCall.Excel(XlCall.xlfReftext... doesn't

[Govert van Drimmelen]

Hi Mark,

Anything related to the Excel C API (including any call on ExcelRefence) will only work if your were initially called from Excel as a worksheet function or a macro.

But if you're calling Application.Inputbox, you're already getting a Range COM object back. 

I'm not sure exactly how you're getting the ExcelReference and in what context the code is running (maybe a "RunTagMacro" in the ribbon?)

Is it possible that the "other part of the code" is running is a funny context, like a separate thread or another ribbon click callback that's being processed directly?

This code should similarly run in a "macro" context to use the C API and the ExcelReference.

-Govert

[Michel Jadoul]

Hello,

This thread was very helpful to me. Thanks!

I just discovered that using the  "dynamic"  type, the code can become just as simple in C# than in  VB.net .

Michel