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.ThisWorkb ook.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.Cla ss1
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.ThisWorkb ook.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 7 2663
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 ReleaseComObjec t method on the
Marshal class.
Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message
news:%2******** ********@TK2MSF TNGP15.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.ThisWorkb ook.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.Cla ss1 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.ThisWorkb ook.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
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(ran ge).Rows.Count
For j = 1 To Excel.Range(ran ge).Columns.Cou nt
total = total + Convert.ToDoubl e(Excel.Range(r ange).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.Ge tProperty,
null, Excel, Parameters);
oRows = oRange.GetType( ).InvokeMember( "Rows", BindingFlags.Ge tProperty,
null, oRange, null);
oRowCount = oRows.GetType() .InvokeMember(" Count",
BindingFlags.Ge tProperty, null, oRows, null);
oCols = oRange.GetType( ).InvokeMember( "Columns",
BindingFlags.Ge tProperty, null, oRange, null);
oColCount = oCols.GetType() .InvokeMember(" Count",
BindingFlags.Ge tProperty, 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.Ge tProperty, null, oRange, Parameters);
oValue = oCell.GetType() .InvokeMember(" Value",
BindingFlags.Ge tProperty, 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.c om> wrote in
message news:OS******** ******@tk2msftn gp13.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 ReleaseComObjec t method on the Marshal class.
Hope this helps.
-- - Nicholas Paldino [.NET/C# MVP] - mv*@spam.guard. caspershouse.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message news:%2******** ********@TK2MSF TNGP15.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.ThisWorkb ook.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.Cla ss1 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.ThisWorkb ook.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
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 ReleaseComObjec t 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(ran ge) - Returns the individual range.
Excel.Range(ran ge).Rows - Returns the collection of ranges.
Excel.Range(ran ge).Columns - Returns the collection of columns
Excel.Range(ran ge).Cells - Returns the collection of cells.
Excel.Range(ran ge).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.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message
news:u5******** *****@TK2MSFTNG P15.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(ran ge).Rows.Count For j = 1 To Excel.Range(ran ge).Columns.Cou nt total = total + Convert.ToDoubl e(Excel.Range(r ange).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.Ge tProperty, null, Excel, Parameters); oRows = oRange.GetType( ).InvokeMember( "Rows", BindingFlags.Ge tProperty, null, oRange, null); oRowCount = oRows.GetType() .InvokeMember(" Count", BindingFlags.Ge tProperty, null, oRows, null); oCols = oRange.GetType( ).InvokeMember( "Columns", BindingFlags.Ge tProperty, null, oRange, null); oColCount = oCols.GetType() .InvokeMember(" Count", BindingFlags.Ge tProperty, 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.Ge tProperty, null, oRange, Parameters); oValue = oCell.GetType() .InvokeMember(" Value", BindingFlags.Ge tProperty, 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.c om> wrote in message news:OS******** ******@tk2msftn gp13.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 ReleaseComObjec t method on the Marshal class.
Hope this helps.
-- - Nicholas Paldino [.NET/C# MVP] - mv*@spam.guard. caspershouse.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message news:%2******** ********@TK2MSF TNGP15.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.ThisWorkb ook.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.Cla ss1 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.ThisWorkb ook.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
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.c om> wrote in
message news:ed******** *****@TK2MSFTNG P10.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 ReleaseComObjec t 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(ran ge) - Returns the individual range. Excel.Range(ran ge).Rows - Returns the collection of ranges. Excel.Range(ran ge).Columns - Returns the collection of columns Excel.Range(ran ge).Cells - Returns the collection of cells. Excel.Range(ran ge).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.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message news:u5******** *****@TK2MSFTNG P15.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(ran ge).Rows.Count For j = 1 To Excel.Range(ran ge).Columns.Cou nt total = total + Convert.ToDoubl e(Excel.Range(r ange).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.Ge tProperty, null, Excel, Parameters); oRows = oRange.GetType( ).InvokeMember( "Rows", BindingFlags.Ge tProperty, null, oRange, null); oRowCount = oRows.GetType() .InvokeMember(" Count", BindingFlags.Ge tProperty, null, oRows, null); oCols = oRange.GetType( ).InvokeMember( "Columns", BindingFlags.Ge tProperty, null, oRange, null); oColCount = oCols.GetType() .InvokeMember(" Count", BindingFlags.Ge tProperty, 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.Ge tProperty, null, oRange, Parameters); oValue = oCell.GetType() .InvokeMember(" Value", BindingFlags.Ge tProperty, 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.c om> wrote in message news:OS******** ******@tk2msftn gp13.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 ReleaseComObjec t method on the Marshal class.
Hope this helps.
-- - Nicholas Paldino [.NET/C# MVP] - mv*@spam.guard. caspershouse.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message news:%2******** ********@TK2MSF TNGP15.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.ThisWorkb ook.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.Cla ss1 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.ThisWorkb ook.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
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 ReleaseComObjec t.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard. caspershouse.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message
news:uQ******** *****@TK2MSFTNG P15.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.c om> wrote in message news:ed******** *****@TK2MSFTNG P10.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 ReleaseComObjec t 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(ran ge) - Returns the individual range. Excel.Range(ran ge).Rows - Returns the collection of ranges. Excel.Range(ran ge).Columns - Returns the collection of columns Excel.Range(ran ge).Cells - Returns the collection of cells. Excel.Range(ran ge).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.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message news:u5******** *****@TK2MSFTNG P15.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(ran ge).Rows.Count For j = 1 To Excel.Range(ran ge).Columns.Cou nt total = total + Convert.ToDoubl e(Excel.Range(r ange).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.Ge tProperty, null, Excel, Parameters); oRows = oRange.GetType( ).InvokeMember( "Rows", BindingFlags.Ge tProperty, null, oRange, null); oRowCount = oRows.GetType() .InvokeMember(" Count", BindingFlags.Ge tProperty, null, oRows, null); oCols = oRange.GetType( ).InvokeMember( "Columns", BindingFlags.Ge tProperty, null, oRange, null); oColCount = oCols.GetType() .InvokeMember(" Count", BindingFlags.Ge tProperty, 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.Ge tProperty, null, oRange, Parameters); oValue = oCell.GetType() .InvokeMember(" Value", BindingFlags.Ge tProperty, 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.c om> wrote in message news:OS******** ******@tk2msftn gp13.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 ReleaseComObjec t method on the Marshal class.
Hope this helps.
-- - Nicholas Paldino [.NET/C# MVP] - mv*@spam.guard. caspershouse.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message news:%2******** ********@TK2MSF TNGP15.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.ThisWorkb ook.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.Cla ss1 > 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.ThisWorkb ook.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 >
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(obj ect obj, string sProperty)
{
string[] Properties = sProperty.Split (new char[] {'.'});
object oProp;
oProp = obj;
for (int i = 0; i <= Properties.GetU pperBound(0); i++)
{
if (Properties[i].Contains("("))
oProp = oProp.GetType() .InvokeMember(P roperties[i].Substring(0,
Properties[i].IndexOf('(')),
BindingFlags.Ge tProperty, null, oProp,
Properties[i].Substring(Prop erties[i].IndexOf('(')
+ 1, Properties[i].IndexOf(')') - (Properties[i].IndexOf('(') +
1)).Split(new char[] { ','
}));
else
oProp = oProp.GetType() .InvokeMember(P roperties[i],
BindingFlags.Ge tProperty, 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(oEx cel, "ActiveSheet.Ce lls(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)GetProp erty(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.c om> wrote in
message news:ea******** ******@TK2MSFTN GP14.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 ReleaseComObjec t.
-- - Nicholas Paldino [.NET/C# MVP] - mv*@spam.guard. caspershouse.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message news:uQ******** *****@TK2MSFTNG P15.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.c om> wrote in message news:ed******** *****@TK2MSFTNG P10.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 ReleaseComObjec t 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(ran ge) - Returns the individual range. Excel.Range(ran ge).Rows - Returns the collection of ranges. Excel.Range(ran ge).Columns - Returns the collection of columns Excel.Range(ran ge).Cells - Returns the collection of cells. Excel.Range(ran ge).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.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message news:u5******** *****@TK2MSFTNG P15.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(ran ge).Rows.Count For j = 1 To Excel.Range(ran ge).Columns.Cou nt total = total + Convert.ToDoubl e(Excel.Range(r ange).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.Ge tProperty, null, Excel, Parameters); oRows = oRange.GetType( ).InvokeMember( "Rows", BindingFlags.Ge tProperty, null, oRange, null); oRowCount = oRows.GetType() .InvokeMember(" Count", BindingFlags.Ge tProperty, null, oRows, null); oCols = oRange.GetType( ).InvokeMember( "Columns", BindingFlags.Ge tProperty, null, oRange, null); oColCount = oCols.GetType() .InvokeMember(" Count", BindingFlags.Ge tProperty, 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.Ge tProperty, null, oRange, Parameters); oValue = oCell.GetType() .InvokeMember(" Value", BindingFlags.Ge tProperty, 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.c om> wrote in message news:OS******** ******@tk2msftn gp13.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 ReleaseComObjec t method on the Marshal class. > > Hope this helps. > > > -- > - Nicholas Paldino [.NET/C# MVP] > - mv*@spam.guard. caspershouse.co m > > "Alan Roberts" <al**@statistix l.co.uk> wrote in message > news:%2******** ********@TK2MSF TNGP15.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.ThisWorkb ook.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.Cla ss1 >> 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.ThisWorkb ook.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 >> > >
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)GetProp erty(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**@statistix l.co.uk> wrote in message
news:Ow******** *****@TK2MSFTNG P12.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(obj ect obj, string sProperty) { string[] Properties = sProperty.Split (new char[] {'.'}); object oProp; oProp = obj; for (int i = 0; i <= Properties.GetU pperBound(0); i++) { if (Properties[i].Contains("(")) oProp = oProp.GetType() .InvokeMember(P roperties[i].Substring(0, Properties[i].IndexOf('(')), BindingFlags.Ge tProperty, null, oProp, Properties[i].Substring(Prop erties[i].IndexOf('(') + 1, Properties[i].IndexOf(')') - (Properties[i].IndexOf('(') + 1)).Split(new char[] { ',' })); else oProp = oProp.GetType() .InvokeMember(P roperties[i], BindingFlags.Ge tProperty, 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(oEx cel, "ActiveSheet.Ce lls(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)GetProp erty(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.c om> wrote
in message news:ea******** ******@TK2MSFTN GP14.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 ReleaseComObjec t.
-- - Nicholas Paldino [.NET/C# MVP] - mv*@spam.guard. caspershouse.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message news:uQ******** *****@TK2MSFTNG P15.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.c om>
wrote in message news:ed******** *****@TK2MSFTNG P10.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 ReleaseComObjec t 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(ran ge) - Returns the individual range. Excel.Range(ran ge).Rows - Returns the collection of ranges. Excel.Range(ran ge).Columns - Returns the collection of columns Excel.Range(ran ge).Cells - Returns the collection of cells. Excel.Range(ran ge).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.co m
"Alan Roberts" <al**@statistix l.co.uk> wrote in message news:u5******** *****@TK2MSFTNG P15.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(ran ge).Rows.Count > For j = 1 To Excel.Range(ran ge).Columns.Cou nt > total = total +
Convert.ToDoubl e(Excel.Range(r ange).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.Ge tProperty, null, Excel, Parameters); > oRows = oRange.GetType( ).InvokeMember( "Rows", > BindingFlags.Ge tProperty, null, oRange, null); > oRowCount = oRows.GetType() .InvokeMember(" Count", > BindingFlags.Ge tProperty, null, oRows, null); > oCols = oRange.GetType( ).InvokeMember( "Columns", > BindingFlags.Ge tProperty, null, oRange, null); > oColCount = oCols.GetType() .InvokeMember(" Count", > BindingFlags.Ge tProperty, 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.Ge tProperty, null, oRange, Parameters); > oValue = oCell.GetType() .InvokeMember(" Value", > BindingFlags.Ge tProperty, 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.c om> > wrote in message news:OS******** ******@tk2msftn gp13.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 ReleaseComObjec t method on the Marshal class. >> >> Hope this helps. >> >> >> -- >> - Nicholas Paldino [.NET/C# MVP] >> - mv*@spam.guard. caspershouse.co m >> >> "Alan Roberts" <al**@statistix l.co.uk> wrote in message >> news:%2******** ********@TK2MSF TNGP15.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.ThisWorkb ook.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.Cla ss1 >>> 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.ThisWorkb ook.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 >>> >> >> > >
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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
|
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# application. It seems simple enough, but the problem I'm
encountering is as follows:
In order for the user to select the cell from Excel, they must first...
|
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))
|
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 (SP1) installed.
The error is:
System.Runtime.InteropServices.COMException(0x800A03EC): Exception from
HRESULT: 0x800A03EC.
at...
|
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 use CSharp in terms of Java architecture.
Keith
| |
by: akantrowitz |
last post by:
Can you replicate the Excel solver functionality from within Csharp?
thks,
ak
|
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 better than UNLEASHED than
please help me.
|
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 researched into
using the Excel.Application object and have successfully openned a
worksheet in Excel (though hidden from the user).
The problem now...
|
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
Microsoft.Office.Interop.Excel.ApplicationClass appExcel =
new Microsoft.Office.Interop.Excel.ApplicationClass();
try
{
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |