473,320 Members | 1,832 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

CSharp VB Excel COM Differences

Can someone please explain the following for me...

I am trying to link to a .NET DLL from Excel. Excel needs to pass a
reference to itself to the DLL and then the DLL needs to perform some work
on the running instance of Excel via that reference. As an example, a VB
DLL to return the path to the current active workbook contains the following
(in addition to the COM GUIDS automatically added to a new COMClass)...

Public Class Class1
Public Sub New()
MyBase.New()
End Sub
Private gExcel As Object
Public Property Excel() As Object
Set(ByVal value As Object)
gExcel = value
End Set
Get
Excel = gExcel
End Get
End Property
Public Function Path() As String
Return Excel.ThisWorkbook.Path
End Function
End Class

After adding a refernece to the DLL, a macro in Excel could call this as
follows

Sub VBTest()
Dim VBTest As New VBExcelTest.Class1
Set VBTest.Excel = Application
Debug.Print VBTest.Path
End Sub

This all work fine

How can I do the same thing using C#? If I try to create a DLL with
equivalent(?) code eg

public class Class1
{
public Class1(){
}
private object gExcel;
public object Excel{
get{
return gExcel;
}
set{
gExcel = value;
}
}
public string Path(){
return Excel.ThisWorkbook.Path;
}
}

I get a compile error saying that - 'object' does not contain a definition
for 'ThisWorkbook'

How can I get this to work?

Thanks

Alan
Dec 22 '05 #1
7 2649
Alan,

The reason this works in VB is that the VB compiler will translate
property and method calls on variables of type object to be reflection
calls, which cause your code to work.

Unfortunately, C# doesn't support this functionality. You will have to
use reflection to get the member names and then invoke them like that.

OR, you could set a reference to the interop assemblies for Excel, and
cast the object passed into your method into one of those classes in the
interop assembly. This would prevent you from having to do any late
binding.

Also, you should be careful of the operations you are performing. When
you call methods on any object in Excel, you are creating a runtime callable
wrapper which should be disposed of if you aren't using it anymore. In the
case of your Path property, you should assign the result of ThisWorkbook to
a variable, then pass that to the static ReleaseComObject method on the
Marshal class.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
Can someone please explain the following for me...

I am trying to link to a .NET DLL from Excel. Excel needs to pass a
reference to itself to the DLL and then the DLL needs to perform some work
on the running instance of Excel via that reference. As an example, a VB
DLL to return the path to the current active workbook contains the
following (in addition to the COM GUIDS automatically added to a new
COMClass)...

Public Class Class1
Public Sub New()
MyBase.New()
End Sub
Private gExcel As Object
Public Property Excel() As Object
Set(ByVal value As Object)
gExcel = value
End Set
Get
Excel = gExcel
End Get
End Property
Public Function Path() As String
Return Excel.ThisWorkbook.Path
End Function
End Class

After adding a refernece to the DLL, a macro in Excel could call this as
follows

Sub VBTest()
Dim VBTest As New VBExcelTest.Class1
Set VBTest.Excel = Application
Debug.Print VBTest.Path
End Sub

This all work fine

How can I do the same thing using C#? If I try to create a DLL with
equivalent(?) code eg

public class Class1
{
public Class1(){
}
private object gExcel;
public object Excel{
get{
return gExcel;
}
set{
gExcel = value;
}
}
public string Path(){
return Excel.ThisWorkbook.Path;
}
}

I get a compile error saying that - 'object' does not contain a definition
for 'ThisWorkbook'

How can I get this to work?

Thanks

Alan

Dec 22 '05 #2
Hi Nicholas, thanks for the thorough reply! Does this mean that the
following simple VB code to calculate the sum of the values specified in an
Excel Range object

Public Function Sum(ByVal range As String) As Double
Dim i, j As Integer
Dim total As Double
For i = 1 To Excel.Range(range).Rows.Count
For j = 1 To Excel.Range(range).Columns.Count
total = total + Convert.ToDouble(Excel.Range(range).cells(i,
j).value)
Next j
Next i
Return total
End Function
must become something like this in C#???
public double Sum(string range)
{
object oRange, oRows, oCols, oRowCount, oColCount, oCell, oValue;
object[] Parameters;
double total= 0;
Parameters = new Object[1];
Parameters[0] = range;
oRange = Excel.GetType().InvokeMember("Range", BindingFlags.GetProperty,
null, Excel, Parameters);
oRows = oRange.GetType().InvokeMember("Rows", BindingFlags.GetProperty,
null, oRange, null);
oRowCount = oRows.GetType().InvokeMember("Count",
BindingFlags.GetProperty, null, oRows, null);
oCols = oRange.GetType().InvokeMember("Columns",
BindingFlags.GetProperty, null, oRange, null);
oColCount = oCols.GetType().InvokeMember("Count",
BindingFlags.GetProperty, null, oCols, null);
for (int i = 1; i <= (int)oColCount; i++)
{
for (int j = 1; j <= (int)oRowCount; j++)
{
Parameters = new Object[2];
Parameters[0] = j;
Parameters[1] = i;
oValue = new Object();
oCell = new Object();
oCell = oRange.GetType().InvokeMember("Cells",
BindingFlags.GetProperty, null, oRange, Parameters);
oValue = oCell.GetType().InvokeMember("Value",
BindingFlags.GetProperty, null, oCell, null);
total += (double)oValue;
}
}
return total;
}
Or is there a better way?

Thanks

Alan

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:OS**************@tk2msftngp13.phx.gbl...
Alan,

The reason this works in VB is that the VB compiler will translate
property and method calls on variables of type object to be reflection
calls, which cause your code to work.

Unfortunately, C# doesn't support this functionality. You will have to
use reflection to get the member names and then invoke them like that.

OR, you could set a reference to the interop assemblies for Excel, and
cast the object passed into your method into one of those classes in the
interop assembly. This would prevent you from having to do any late
binding.

Also, you should be careful of the operations you are performing. When
you call methods on any object in Excel, you are creating a runtime
callable wrapper which should be disposed of if you aren't using it
anymore. In the case of your Path property, you should assign the result
of ThisWorkbook to a variable, then pass that to the static
ReleaseComObject method on the Marshal class.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
Can someone please explain the following for me...

I am trying to link to a .NET DLL from Excel. Excel needs to pass a
reference to itself to the DLL and then the DLL needs to perform some
work on the running instance of Excel via that reference. As an example,
a VB DLL to return the path to the current active workbook contains the
following (in addition to the COM GUIDS automatically added to a new
COMClass)...

Public Class Class1
Public Sub New()
MyBase.New()
End Sub
Private gExcel As Object
Public Property Excel() As Object
Set(ByVal value As Object)
gExcel = value
End Set
Get
Excel = gExcel
End Get
End Property
Public Function Path() As String
Return Excel.ThisWorkbook.Path
End Function
End Class

After adding a refernece to the DLL, a macro in Excel could call this as
follows

Sub VBTest()
Dim VBTest As New VBExcelTest.Class1
Set VBTest.Excel = Application
Debug.Print VBTest.Path
End Sub

This all work fine

How can I do the same thing using C#? If I try to create a DLL with
equivalent(?) code eg

public class Class1
{
public Class1(){
}
private object gExcel;
public object Excel{
get{
return gExcel;
}
set{
gExcel = value;
}
}
public string Path(){
return Excel.ThisWorkbook.Path;
}
}

I get a compile error saying that - 'object' does not contain a
definition for 'ThisWorkbook'

How can I get this to work?

Thanks

Alan


Dec 23 '05 #3
Alan,

Yes, it's something like that. You could cut down on some of the
reflection calls, but not by much.

Also, you need to call ReleaseComObject for each object you expose. For
example, in your Sum method, you need to release the result returned from:

Excel.Range - Returns a collection of ranges
Excel.Range(range) - Returns the individual range.
Excel.Range(range).Rows - Returns the collection of ranges.
Excel.Range(range).Columns - Returns the collection of columns
Excel.Range(range).Cells - Returns the collection of cells.
Excel.Range(range).Cells(i, j) - Returns the range representing the
individual cell.

All of those need to have their references released properly.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:u5*************@TK2MSFTNGP15.phx.gbl...
Hi Nicholas, thanks for the thorough reply! Does this mean that the
following simple VB code to calculate the sum of the values specified in
an Excel Range object

Public Function Sum(ByVal range As String) As Double
Dim i, j As Integer
Dim total As Double
For i = 1 To Excel.Range(range).Rows.Count
For j = 1 To Excel.Range(range).Columns.Count
total = total + Convert.ToDouble(Excel.Range(range).cells(i,
j).value)
Next j
Next i
Return total
End Function
must become something like this in C#???
public double Sum(string range)
{
object oRange, oRows, oCols, oRowCount, oColCount, oCell, oValue;
object[] Parameters;
double total= 0;
Parameters = new Object[1];
Parameters[0] = range;
oRange = Excel.GetType().InvokeMember("Range",
BindingFlags.GetProperty, null, Excel, Parameters);
oRows = oRange.GetType().InvokeMember("Rows", BindingFlags.GetProperty,
null, oRange, null);
oRowCount = oRows.GetType().InvokeMember("Count",
BindingFlags.GetProperty, null, oRows, null);
oCols = oRange.GetType().InvokeMember("Columns",
BindingFlags.GetProperty, null, oRange, null);
oColCount = oCols.GetType().InvokeMember("Count",
BindingFlags.GetProperty, null, oCols, null);
for (int i = 1; i <= (int)oColCount; i++)
{
for (int j = 1; j <= (int)oRowCount; j++)
{
Parameters = new Object[2];
Parameters[0] = j;
Parameters[1] = i;
oValue = new Object();
oCell = new Object();
oCell = oRange.GetType().InvokeMember("Cells",
BindingFlags.GetProperty, null, oRange, Parameters);
oValue = oCell.GetType().InvokeMember("Value",
BindingFlags.GetProperty, null, oCell, null);
total += (double)oValue;
}
}
return total;
}
Or is there a better way?

Thanks

Alan

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in message news:OS**************@tk2msftngp13.phx.gbl...
Alan,

The reason this works in VB is that the VB compiler will translate
property and method calls on variables of type object to be reflection
calls, which cause your code to work.

Unfortunately, C# doesn't support this functionality. You will have
to use reflection to get the member names and then invoke them like that.

OR, you could set a reference to the interop assemblies for Excel, and
cast the object passed into your method into one of those classes in the
interop assembly. This would prevent you from having to do any late
binding.

Also, you should be careful of the operations you are performing.
When you call methods on any object in Excel, you are creating a runtime
callable wrapper which should be disposed of if you aren't using it
anymore. In the case of your Path property, you should assign the result
of ThisWorkbook to a variable, then pass that to the static
ReleaseComObject method on the Marshal class.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
Can someone please explain the following for me...

I am trying to link to a .NET DLL from Excel. Excel needs to pass a
reference to itself to the DLL and then the DLL needs to perform some
work on the running instance of Excel via that reference. As an
example, a VB DLL to return the path to the current active workbook
contains the following (in addition to the COM GUIDS automatically added
to a new COMClass)...

Public Class Class1
Public Sub New()
MyBase.New()
End Sub
Private gExcel As Object
Public Property Excel() As Object
Set(ByVal value As Object)
gExcel = value
End Set
Get
Excel = gExcel
End Get
End Property
Public Function Path() As String
Return Excel.ThisWorkbook.Path
End Function
End Class

After adding a refernece to the DLL, a macro in Excel could call this as
follows

Sub VBTest()
Dim VBTest As New VBExcelTest.Class1
Set VBTest.Excel = Application
Debug.Print VBTest.Path
End Sub

This all work fine

How can I do the same thing using C#? If I try to create a DLL with
equivalent(?) code eg

public class Class1
{
public Class1(){
}
private object gExcel;
public object Excel{
get{
return gExcel;
}
set{
gExcel = value;
}
}
public string Path(){
return Excel.ThisWorkbook.Path;
}
}

I get a compile error saying that - 'object' does not contain a
definition for 'ThisWorkbook'

How can I get this to work?

Thanks

Alan



Dec 24 '05 #4
Blimey! Thanks a lot Nicholas. I guess I will stick with VB for now. On
the whole I find C# more elegant but VB seems better suited to this
particular application.

Thanks

Alan

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:ed*************@TK2MSFTNGP10.phx.gbl...
Alan,

Yes, it's something like that. You could cut down on some of the
reflection calls, but not by much.

Also, you need to call ReleaseComObject for each object you expose.
For example, in your Sum method, you need to release the result returned
from:

Excel.Range - Returns a collection of ranges
Excel.Range(range) - Returns the individual range.
Excel.Range(range).Rows - Returns the collection of ranges.
Excel.Range(range).Columns - Returns the collection of columns
Excel.Range(range).Cells - Returns the collection of cells.
Excel.Range(range).Cells(i, j) - Returns the range representing the
individual cell.

All of those need to have their references released properly.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:u5*************@TK2MSFTNGP15.phx.gbl...
Hi Nicholas, thanks for the thorough reply! Does this mean that the
following simple VB code to calculate the sum of the values specified in
an Excel Range object

Public Function Sum(ByVal range As String) As Double
Dim i, j As Integer
Dim total As Double
For i = 1 To Excel.Range(range).Rows.Count
For j = 1 To Excel.Range(range).Columns.Count
total = total + Convert.ToDouble(Excel.Range(range).cells(i,
j).value)
Next j
Next i
Return total
End Function
must become something like this in C#???
public double Sum(string range)
{
object oRange, oRows, oCols, oRowCount, oColCount, oCell, oValue;
object[] Parameters;
double total= 0;
Parameters = new Object[1];
Parameters[0] = range;
oRange = Excel.GetType().InvokeMember("Range",
BindingFlags.GetProperty, null, Excel, Parameters);
oRows = oRange.GetType().InvokeMember("Rows",
BindingFlags.GetProperty, null, oRange, null);
oRowCount = oRows.GetType().InvokeMember("Count",
BindingFlags.GetProperty, null, oRows, null);
oCols = oRange.GetType().InvokeMember("Columns",
BindingFlags.GetProperty, null, oRange, null);
oColCount = oCols.GetType().InvokeMember("Count",
BindingFlags.GetProperty, null, oCols, null);
for (int i = 1; i <= (int)oColCount; i++)
{
for (int j = 1; j <= (int)oRowCount; j++)
{
Parameters = new Object[2];
Parameters[0] = j;
Parameters[1] = i;
oValue = new Object();
oCell = new Object();
oCell = oRange.GetType().InvokeMember("Cells",
BindingFlags.GetProperty, null, oRange, Parameters);
oValue = oCell.GetType().InvokeMember("Value",
BindingFlags.GetProperty, null, oCell, null);
total += (double)oValue;
}
}
return total;
}
Or is there a better way?

Thanks

Alan

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in message news:OS**************@tk2msftngp13.phx.gbl...
Alan,

The reason this works in VB is that the VB compiler will translate
property and method calls on variables of type object to be reflection
calls, which cause your code to work.

Unfortunately, C# doesn't support this functionality. You will have
to use reflection to get the member names and then invoke them like
that.

OR, you could set a reference to the interop assemblies for Excel,
and cast the object passed into your method into one of those classes in
the interop assembly. This would prevent you from having to do any late
binding.

Also, you should be careful of the operations you are performing.
When you call methods on any object in Excel, you are creating a runtime
callable wrapper which should be disposed of if you aren't using it
anymore. In the case of your Path property, you should assign the
result of ThisWorkbook to a variable, then pass that to the static
ReleaseComObject method on the Marshal class.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
Can someone please explain the following for me...

I am trying to link to a .NET DLL from Excel. Excel needs to pass a
reference to itself to the DLL and then the DLL needs to perform some
work on the running instance of Excel via that reference. As an
example, a VB DLL to return the path to the current active workbook
contains the following (in addition to the COM GUIDS automatically
added to a new COMClass)...

Public Class Class1
Public Sub New()
MyBase.New()
End Sub
Private gExcel As Object
Public Property Excel() As Object
Set(ByVal value As Object)
gExcel = value
End Set
Get
Excel = gExcel
End Get
End Property
Public Function Path() As String
Return Excel.ThisWorkbook.Path
End Function
End Class

After adding a refernece to the DLL, a macro in Excel could call this
as follows

Sub VBTest()
Dim VBTest As New VBExcelTest.Class1
Set VBTest.Excel = Application
Debug.Print VBTest.Path
End Sub

This all work fine

How can I do the same thing using C#? If I try to create a DLL with
equivalent(?) code eg

public class Class1
{
public Class1(){
}
private object gExcel;
public object Excel{
get{
return gExcel;
}
set{
gExcel = value;
}
}
public string Path(){
return Excel.ThisWorkbook.Path;
}
}

I get a compile error saying that - 'object' does not contain a
definition for 'ThisWorkbook'

How can I get this to work?

Thanks

Alan



Dec 24 '05 #5
Alan,

The problem still exists in VB. In VB, the RCW for all of the objects I
list out still are out there, and you don't release them.

The only thing that VB makes easier is having to actually make the
calls, it will make it easy to perfrom the reflection for you, but not the
actuall calls to ReleaseComObject.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:uQ*************@TK2MSFTNGP15.phx.gbl...
Blimey! Thanks a lot Nicholas. I guess I will stick with VB for now. On
the whole I find C# more elegant but VB seems better suited to this
particular application.

Thanks

Alan

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in message news:ed*************@TK2MSFTNGP10.phx.gbl...
Alan,

Yes, it's something like that. You could cut down on some of the
reflection calls, but not by much.

Also, you need to call ReleaseComObject for each object you expose.
For example, in your Sum method, you need to release the result returned
from:

Excel.Range - Returns a collection of ranges
Excel.Range(range) - Returns the individual range.
Excel.Range(range).Rows - Returns the collection of ranges.
Excel.Range(range).Columns - Returns the collection of columns
Excel.Range(range).Cells - Returns the collection of cells.
Excel.Range(range).Cells(i, j) - Returns the range representing the
individual cell.

All of those need to have their references released properly.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:u5*************@TK2MSFTNGP15.phx.gbl...
Hi Nicholas, thanks for the thorough reply! Does this mean that the
following simple VB code to calculate the sum of the values specified in
an Excel Range object

Public Function Sum(ByVal range As String) As Double
Dim i, j As Integer
Dim total As Double
For i = 1 To Excel.Range(range).Rows.Count
For j = 1 To Excel.Range(range).Columns.Count
total = total + Convert.ToDouble(Excel.Range(range).cells(i,
j).value)
Next j
Next i
Return total
End Function
must become something like this in C#???
public double Sum(string range)
{
object oRange, oRows, oCols, oRowCount, oColCount, oCell, oValue;
object[] Parameters;
double total= 0;
Parameters = new Object[1];
Parameters[0] = range;
oRange = Excel.GetType().InvokeMember("Range",
BindingFlags.GetProperty, null, Excel, Parameters);
oRows = oRange.GetType().InvokeMember("Rows",
BindingFlags.GetProperty, null, oRange, null);
oRowCount = oRows.GetType().InvokeMember("Count",
BindingFlags.GetProperty, null, oRows, null);
oCols = oRange.GetType().InvokeMember("Columns",
BindingFlags.GetProperty, null, oRange, null);
oColCount = oCols.GetType().InvokeMember("Count",
BindingFlags.GetProperty, null, oCols, null);
for (int i = 1; i <= (int)oColCount; i++)
{
for (int j = 1; j <= (int)oRowCount; j++)
{
Parameters = new Object[2];
Parameters[0] = j;
Parameters[1] = i;
oValue = new Object();
oCell = new Object();
oCell = oRange.GetType().InvokeMember("Cells",
BindingFlags.GetProperty, null, oRange, Parameters);
oValue = oCell.GetType().InvokeMember("Value",
BindingFlags.GetProperty, null, oCell, null);
total += (double)oValue;
}
}
return total;
}
Or is there a better way?

Thanks

Alan

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in message news:OS**************@tk2msftngp13.phx.gbl...
Alan,

The reason this works in VB is that the VB compiler will translate
property and method calls on variables of type object to be reflection
calls, which cause your code to work.

Unfortunately, C# doesn't support this functionality. You will have
to use reflection to get the member names and then invoke them like
that.

OR, you could set a reference to the interop assemblies for Excel,
and cast the object passed into your method into one of those classes
in the interop assembly. This would prevent you from having to do any
late binding.

Also, you should be careful of the operations you are performing.
When you call methods on any object in Excel, you are creating a
runtime callable wrapper which should be disposed of if you aren't
using it anymore. In the case of your Path property, you should assign
the result of ThisWorkbook to a variable, then pass that to the static
ReleaseComObject method on the Marshal class.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
> Can someone please explain the following for me...
>
> I am trying to link to a .NET DLL from Excel. Excel needs to pass a
> reference to itself to the DLL and then the DLL needs to perform some
> work on the running instance of Excel via that reference. As an
> example, a VB DLL to return the path to the current active workbook
> contains the following (in addition to the COM GUIDS automatically
> added to a new COMClass)...
>
> Public Class Class1
> Public Sub New()
> MyBase.New()
> End Sub
> Private gExcel As Object
> Public Property Excel() As Object
> Set(ByVal value As Object)
> gExcel = value
> End Set
> Get
> Excel = gExcel
> End Get
> End Property
> Public Function Path() As String
> Return Excel.ThisWorkbook.Path
> End Function
> End Class
>
> After adding a refernece to the DLL, a macro in Excel could call this
> as follows
>
> Sub VBTest()
> Dim VBTest As New VBExcelTest.Class1
> Set VBTest.Excel = Application
> Debug.Print VBTest.Path
> End Sub
>
> This all work fine
>
> How can I do the same thing using C#? If I try to create a DLL with
> equivalent(?) code eg
>
> public class Class1
> {
> public Class1(){
> }
> private object gExcel;
> public object Excel{
> get{
> return gExcel;
> }
> set{
> gExcel = value;
> }
> }
> public string Path(){
> return Excel.ThisWorkbook.Path;
> }
> }
>
> I get a compile error saying that - 'object' does not contain a
> definition for 'ThisWorkbook'
>
> How can I get this to work?
>
> Thanks
>
> Alan
>



Dec 27 '05 #6
Thanks Nicholas. I guess I am still having dificulties with the Release
idea....
I have come up with the following routine in C#
private static object GetProperty(object obj, string sProperty)
{
string[] Properties = sProperty.Split(new char[] {'.'});
object oProp;
oProp = obj;
for (int i = 0; i <= Properties.GetUpperBound(0); i++)
{
if (Properties[i].Contains("("))
oProp = oProp.GetType().InvokeMember(Properties[i].Substring(0,
Properties[i].IndexOf('(')),
BindingFlags.GetProperty, null, oProp,
Properties[i].Substring(Properties[i].IndexOf('(')
+ 1, Properties[i].IndexOf(')') - (Properties[i].IndexOf('(') +
1)).Split(new char[] { ','
}));
else
oProp = oProp.GetType().InvokeMember(Properties[i],
BindingFlags.GetProperty, null,
oProp, null);
}

return oProp;
}
basically it simplifies my code as I once I have a link to the Excel object
I can access properties using code along the lines of
GetProperty(oExcel, "ActiveSheet.Cells(1,1).Value");
rather than having all of the step by step code I presented previousle.

If I use this function in a routine to calculate the total of a range of
cells eg
double total = 0;
for (int i = 1; i <= 5; i++)
{
total += (double)GetProperty(oRange, "Cells(" + i.ToString() +",
1").Value");
}
What objects do I have to release both in the for loop code above and in the
GetProperty function that it calls?

Thanks for your help with this

Alan

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:ea**************@TK2MSFTNGP14.phx.gbl...
Alan,

The problem still exists in VB. In VB, the RCW for all of the objects
I list out still are out there, and you don't release them.

The only thing that VB makes easier is having to actually make the
calls, it will make it easy to perfrom the reflection for you, but not the
actuall calls to ReleaseComObject.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:uQ*************@TK2MSFTNGP15.phx.gbl...
Blimey! Thanks a lot Nicholas. I guess I will stick with VB for now.
On the whole I find C# more elegant but VB seems better suited to this
particular application.

Thanks

Alan

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote
in message news:ed*************@TK2MSFTNGP10.phx.gbl...
Alan,

Yes, it's something like that. You could cut down on some of the
reflection calls, but not by much.

Also, you need to call ReleaseComObject for each object you expose.
For example, in your Sum method, you need to release the result returned
from:

Excel.Range - Returns a collection of ranges
Excel.Range(range) - Returns the individual range.
Excel.Range(range).Rows - Returns the collection of ranges.
Excel.Range(range).Columns - Returns the collection of columns
Excel.Range(range).Cells - Returns the collection of cells.
Excel.Range(range).Cells(i, j) - Returns the range representing the
individual cell.

All of those need to have their references released properly.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:u5*************@TK2MSFTNGP15.phx.gbl...
Hi Nicholas, thanks for the thorough reply! Does this mean that the
following simple VB code to calculate the sum of the values specified
in an Excel Range object

Public Function Sum(ByVal range As String) As Double
Dim i, j As Integer
Dim total As Double
For i = 1 To Excel.Range(range).Rows.Count
For j = 1 To Excel.Range(range).Columns.Count
total = total + Convert.ToDouble(Excel.Range(range).cells(i,
j).value)
Next j
Next i
Return total
End Function
must become something like this in C#???
public double Sum(string range)
{
object oRange, oRows, oCols, oRowCount, oColCount, oCell, oValue;
object[] Parameters;
double total= 0;
Parameters = new Object[1];
Parameters[0] = range;
oRange = Excel.GetType().InvokeMember("Range",
BindingFlags.GetProperty, null, Excel, Parameters);
oRows = oRange.GetType().InvokeMember("Rows",
BindingFlags.GetProperty, null, oRange, null);
oRowCount = oRows.GetType().InvokeMember("Count",
BindingFlags.GetProperty, null, oRows, null);
oCols = oRange.GetType().InvokeMember("Columns",
BindingFlags.GetProperty, null, oRange, null);
oColCount = oCols.GetType().InvokeMember("Count",
BindingFlags.GetProperty, null, oCols, null);
for (int i = 1; i <= (int)oColCount; i++)
{
for (int j = 1; j <= (int)oRowCount; j++)
{
Parameters = new Object[2];
Parameters[0] = j;
Parameters[1] = i;
oValue = new Object();
oCell = new Object();
oCell = oRange.GetType().InvokeMember("Cells",
BindingFlags.GetProperty, null, oRange, Parameters);
oValue = oCell.GetType().InvokeMember("Value",
BindingFlags.GetProperty, null, oCell, null);
total += (double)oValue;
}
}
return total;
}
Or is there a better way?

Thanks

Alan

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com>
wrote in message news:OS**************@tk2msftngp13.phx.gbl...
> Alan,
>
> The reason this works in VB is that the VB compiler will translate
> property and method calls on variables of type object to be reflection
> calls, which cause your code to work.
>
> Unfortunately, C# doesn't support this functionality. You will
> have to use reflection to get the member names and then invoke them
> like that.
>
> OR, you could set a reference to the interop assemblies for Excel,
> and cast the object passed into your method into one of those classes
> in the interop assembly. This would prevent you from having to do any
> late binding.
>
> Also, you should be careful of the operations you are performing.
> When you call methods on any object in Excel, you are creating a
> runtime callable wrapper which should be disposed of if you aren't
> using it anymore. In the case of your Path property, you should
> assign the result of ThisWorkbook to a variable, then pass that to the
> static ReleaseComObject method on the Marshal class.
>
> Hope this helps.
>
>
> --
> - Nicholas Paldino [.NET/C# MVP]
> - mv*@spam.guard.caspershouse.com
>
> "Alan Roberts" <al**@statistixl.co.uk> wrote in message
> news:%2****************@TK2MSFTNGP15.phx.gbl...
>> Can someone please explain the following for me...
>>
>> I am trying to link to a .NET DLL from Excel. Excel needs to pass a
>> reference to itself to the DLL and then the DLL needs to perform some
>> work on the running instance of Excel via that reference. As an
>> example, a VB DLL to return the path to the current active workbook
>> contains the following (in addition to the COM GUIDS automatically
>> added to a new COMClass)...
>>
>> Public Class Class1
>> Public Sub New()
>> MyBase.New()
>> End Sub
>> Private gExcel As Object
>> Public Property Excel() As Object
>> Set(ByVal value As Object)
>> gExcel = value
>> End Set
>> Get
>> Excel = gExcel
>> End Get
>> End Property
>> Public Function Path() As String
>> Return Excel.ThisWorkbook.Path
>> End Function
>> End Class
>>
>> After adding a refernece to the DLL, a macro in Excel could call this
>> as follows
>>
>> Sub VBTest()
>> Dim VBTest As New VBExcelTest.Class1
>> Set VBTest.Excel = Application
>> Debug.Print VBTest.Path
>> End Sub
>>
>> This all work fine
>>
>> How can I do the same thing using C#? If I try to create a DLL with
>> equivalent(?) code eg
>>
>> public class Class1
>> {
>> public Class1(){
>> }
>> private object gExcel;
>> public object Excel{
>> get{
>> return gExcel;
>> }
>> set{
>> gExcel = value;
>> }
>> }
>> public string Path(){
>> return Excel.ThisWorkbook.Path;
>> }
>> }
>>
>> I get a compile error saying that - 'object' does not contain a
>> definition for 'ThisWorkbook'
>>
>> How can I get this to work?
>>
>> Thanks
>>
>> Alan
>>
>
>



Jan 6 '06 #7
On a side note, you can optimize your loop heavy code to take advantage of
the excel internally supported methods.
double total = 0;
for (int i = 1; i <= 5; i++)
{
total += (double)GetProperty(oRange, "Cells(" + i.ToString() +",
1").Value");
}
can be replaced with
Range r = rangeInQuestion.Formula = "=Sum(1,5)"

FYI

--
Regards,
Alvin Bruney [MVP ASP.NET]

[Shameless Author plug]
The Microsoft Office Web Components Black Book with .NET
Now Available @ www.lulu.com/owc
Forth-coming VSTO.NET - Wrox/Wiley 2006
-------------------------------------------------------

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:Ow*************@TK2MSFTNGP12.phx.gbl... Thanks Nicholas. I guess I am still having dificulties with the Release
idea....
I have come up with the following routine in C#
private static object GetProperty(object obj, string sProperty)
{
string[] Properties = sProperty.Split(new char[] {'.'});
object oProp;
oProp = obj;
for (int i = 0; i <= Properties.GetUpperBound(0); i++)
{
if (Properties[i].Contains("("))
oProp = oProp.GetType().InvokeMember(Properties[i].Substring(0,
Properties[i].IndexOf('(')),
BindingFlags.GetProperty, null, oProp,
Properties[i].Substring(Properties[i].IndexOf('(')
+ 1, Properties[i].IndexOf(')') - (Properties[i].IndexOf('(') +
1)).Split(new char[] { ','
}));
else
oProp = oProp.GetType().InvokeMember(Properties[i],
BindingFlags.GetProperty, null,
oProp, null);
}

return oProp;
}
basically it simplifies my code as I once I have a link to the Excel object I can access properties using code along the lines of
GetProperty(oExcel, "ActiveSheet.Cells(1,1).Value");
rather than having all of the step by step code I presented previousle.

If I use this function in a routine to calculate the total of a range of
cells eg
double total = 0;
for (int i = 1; i <= 5; i++)
{
total += (double)GetProperty(oRange, "Cells(" + i.ToString() +",
1").Value");
}
What objects do I have to release both in the for loop code above and in the GetProperty function that it calls?

Thanks for your help with this

Alan

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in message news:ea**************@TK2MSFTNGP14.phx.gbl...
Alan,

The problem still exists in VB. In VB, the RCW for all of the objects I list out still are out there, and you don't release them.

The only thing that VB makes easier is having to actually make the
calls, it will make it easy to perfrom the reflection for you, but not the actuall calls to ReleaseComObject.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:uQ*************@TK2MSFTNGP15.phx.gbl...
Blimey! Thanks a lot Nicholas. I guess I will stick with VB for now.
On the whole I find C# more elegant but VB seems better suited to this
particular application.

Thanks

Alan

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in message news:ed*************@TK2MSFTNGP10.phx.gbl...
Alan,

Yes, it's something like that. You could cut down on some of the
reflection calls, but not by much.

Also, you need to call ReleaseComObject for each object you expose.
For example, in your Sum method, you need to release the result returned from:

Excel.Range - Returns a collection of ranges
Excel.Range(range) - Returns the individual range.
Excel.Range(range).Rows - Returns the collection of ranges.
Excel.Range(range).Columns - Returns the collection of columns
Excel.Range(range).Cells - Returns the collection of cells.
Excel.Range(range).Cells(i, j) - Returns the range representing the
individual cell.

All of those need to have their references released properly.

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Alan Roberts" <al**@statistixl.co.uk> wrote in message
news:u5*************@TK2MSFTNGP15.phx.gbl...
> Hi Nicholas, thanks for the thorough reply! Does this mean that the
> following simple VB code to calculate the sum of the values specified
> in an Excel Range object
>
> Public Function Sum(ByVal range As String) As Double
> Dim i, j As Integer
> Dim total As Double
> For i = 1 To Excel.Range(range).Rows.Count
> For j = 1 To Excel.Range(range).Columns.Count
> total = total + Convert.ToDouble(Excel.Range(range).cells(i,> j).value)
> Next j
> Next i
> Return total
> End Function
>
>
> must become something like this in C#???
>
>
> public double Sum(string range)
> {
> object oRange, oRows, oCols, oRowCount, oColCount, oCell, oValue;
> object[] Parameters;
> double total= 0;
> Parameters = new Object[1];
> Parameters[0] = range;
> oRange = Excel.GetType().InvokeMember("Range",
> BindingFlags.GetProperty, null, Excel, Parameters);
> oRows = oRange.GetType().InvokeMember("Rows",
> BindingFlags.GetProperty, null, oRange, null);
> oRowCount = oRows.GetType().InvokeMember("Count",
> BindingFlags.GetProperty, null, oRows, null);
> oCols = oRange.GetType().InvokeMember("Columns",
> BindingFlags.GetProperty, null, oRange, null);
> oColCount = oCols.GetType().InvokeMember("Count",
> BindingFlags.GetProperty, null, oCols, null);
> for (int i = 1; i <= (int)oColCount; i++)
> {
> for (int j = 1; j <= (int)oRowCount; j++)
> {
> Parameters = new Object[2];
> Parameters[0] = j;
> Parameters[1] = i;
> oValue = new Object();
> oCell = new Object();
> oCell = oRange.GetType().InvokeMember("Cells",
> BindingFlags.GetProperty, null, oRange, Parameters);
> oValue = oCell.GetType().InvokeMember("Value",
> BindingFlags.GetProperty, null, oCell, null);
> total += (double)oValue;
> }
> }
> return total;
> }
>
>
> Or is there a better way?
>
> Thanks
>
> Alan
>
>
>
> "Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com>
> wrote in message news:OS**************@tk2msftngp13.phx.gbl...
>> Alan,
>>
>> The reason this works in VB is that the VB compiler will translate>> property and method calls on variables of type object to be reflection>> calls, which cause your code to work.
>>
>> Unfortunately, C# doesn't support this functionality. You will
>> have to use reflection to get the member names and then invoke them
>> like that.
>>
>> OR, you could set a reference to the interop assemblies for Excel,>> and cast the object passed into your method into one of those classes>> in the interop assembly. This would prevent you from having to do any>> late binding.
>>
>> Also, you should be careful of the operations you are performing.
>> When you call methods on any object in Excel, you are creating a
>> runtime callable wrapper which should be disposed of if you aren't
>> using it anymore. In the case of your Path property, you should
>> assign the result of ThisWorkbook to a variable, then pass that to the>> static ReleaseComObject method on the Marshal class.
>>
>> Hope this helps.
>>
>>
>> --
>> - Nicholas Paldino [.NET/C# MVP]
>> - mv*@spam.guard.caspershouse.com
>>
>> "Alan Roberts" <al**@statistixl.co.uk> wrote in message
>> news:%2****************@TK2MSFTNGP15.phx.gbl...
>>> Can someone please explain the following for me...
>>>
>>> I am trying to link to a .NET DLL from Excel. Excel needs to pass a>>> reference to itself to the DLL and then the DLL needs to perform some>>> work on the running instance of Excel via that reference. As an
>>> example, a VB DLL to return the path to the current active workbook
>>> contains the following (in addition to the COM GUIDS automatically
>>> added to a new COMClass)...
>>>
>>> Public Class Class1
>>> Public Sub New()
>>> MyBase.New()
>>> End Sub
>>> Private gExcel As Object
>>> Public Property Excel() As Object
>>> Set(ByVal value As Object)
>>> gExcel = value
>>> End Set
>>> Get
>>> Excel = gExcel
>>> End Get
>>> End Property
>>> Public Function Path() As String
>>> Return Excel.ThisWorkbook.Path
>>> End Function
>>> End Class
>>>
>>> After adding a refernece to the DLL, a macro in Excel could call this>>> as follows
>>>
>>> Sub VBTest()
>>> Dim VBTest As New VBExcelTest.Class1
>>> Set VBTest.Excel = Application
>>> Debug.Print VBTest.Path
>>> End Sub
>>>
>>> This all work fine
>>>
>>> How can I do the same thing using C#? If I try to create a DLL with>>> equivalent(?) code eg
>>>
>>> public class Class1
>>> {
>>> public Class1(){
>>> }
>>> private object gExcel;
>>> public object Excel{
>>> get{
>>> return gExcel;
>>> }
>>> set{
>>> gExcel = value;
>>> }
>>> }
>>> public string Path(){
>>> return Excel.ThisWorkbook.Path;
>>> }
>>> }
>>>
>>> I get a compile error saying that - 'object' does not contain a
>>> definition for 'ThisWorkbook'
>>>
>>> How can I get this to work?
>>>
>>> Thanks
>>>
>>> Alan
>>>
>>
>>
>
>



Jan 6 '06 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Xiao-Qin Xia | last post by:
I don't like VBA, and want to use python to work with Excel. Does anybody recommend some good documents to begin with? Many thanks, Xiao-Qin
6
by: Matthew Wieder | last post by:
I have the following requirements: Build a stand-alone C# application that asks the user to click in a cell in an Excel spreadsheet, and then displays the address of that cell in the C#...
1
by: | last post by:
The following code: Private Sub ClearControls(ByVal ctrl As Control) Dim i As Int32 For i = ctrl.Controls.Count - 1 To 0 Step -1 ClearControls(ctrl.Controls(i))
3
by: | last post by:
I wrote a class in VB.NET to export the contents of a datagrid to Excel. It works perfectly on my machine, but it fails on my customers' PCs that have identical versions of Win XP (SP1) and Excel...
1
by: Keith Clark | last post by:
Is there somwhere a tutorial which explains the differences between programming CSharp and Java? Thus it should not start from scratch (e.g. what is a "while" loop) but guide a Java programmer to...
0
by: akantrowitz | last post by:
Can you replicate the Excel solver functionality from within Csharp? thks, ak
5
by: psycho | last post by:
can someone what are the differences between csharp 1.1 and 2.0 and I am new to csharp but i hav worked in vb.net so i am starting with CSHARP UNLEASHED. if any one knows a book or has an ebook...
6
by: gumbystation | last post by:
I am looking to take data from an excel spreadsheet into a csharp application to manipulate the data in various ways. Currently, I am using VS2005 (self-taught C#) and Excel 2000. I have...
1
atksamy
by: atksamy | last post by:
Hi, I am trying to write some data into excel sheets using c#. so far i have written the following code. But i am not able to proceed further ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.