Help with talking to Excel from C#

[james]

Hi all, I am trying to convert some old VB6 code to .NET.

Take this first section (there is more, but hey-ho...)
Dim XLApp As Object
Dim XLSheet
Dim XLBook
Dim HeaderItem As String
Dim ColCount As Integer
ColCount = 1
Set XLApp = CreateObject("EXCEL.Application")
XLApp.Visible = False
XLApp.Workbooks.Open (ExcelFile)

Now, after googling, I have found I can either add a reference to Excel, or
I can use the "GetTypeFromProgID" method instead. The latter is preferable,
as there is no guarantee of the end user having a specific version of Excel,
and the "createobject" method didn't seem to care.

So far, I have this:

//Create Excel object
Object XLApp;
Object XLWorkBook;
Object XLSheet;
Object XLBook;
String HeaderItem;
Int16 ColCount = 1;
Type ExcelType = Type.GetTypeFromProgID("EXCEL.Application");
lstExcelProgress.Items.Add("Creating Excel instance...");
try
{
XLApp = Activator.CreateInstance(ExcelType);
ExcelType.InvokeMember("Visible",
System.Reflection.BindingFlags.SetProperty, null, XLApp, new object[] {
false });
lstExcelProgress.Items.Add("Trying to open the workbook...");
try
{
XLWorkBook = ExcelType.InvokeMember("Workbooks.Open",
System.Reflection.BindingFlags.InvokeMethod, null, XLApp, new object[]
{ExcelFile});
}
catch (Exception wer)
{
//handle error }
}
catch (Exception er)
{
//handle error
}

Now, the first bit works OK where I create my Excel instance, but the second
bit (opening the workbook) is failing, the error returned being Exception
from HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME).

I assume it doesn't like Workbooks.Open as a method to run, but I'm not sure
how else to do it? Any ideas?

James.

 

[Bruce Wood]

Hi all, I am trying to convert some old VB6 code to .NET.

Take this first section (there is more, but hey-ho...)
Dim XLApp As Object
Dim XLSheet
Dim XLBook
Dim HeaderItem As String
Dim ColCount As Integer
ColCount = 1
Set XLApp = CreateObject("EXCEL.Application")
XLApp.Visible = False
XLApp.Workbooks.Open (ExcelFile)

Now, after googling, I have found I can either add a reference to Excel, or
I can use the "GetTypeFromProgID" method instead. The latter is preferable,
as there is no guarantee of the end user having a specific version of Excel,
and the "createobject" method didn't seem to care.

So far, I have this:

//Create Excel object
Object XLApp;
Object XLWorkBook;
Object XLSheet;
Object XLBook;
String HeaderItem;
Int16 ColCount = 1;
Type ExcelType = Type.GetTypeFromProgID("EXCEL.Application");
lstExcelProgress.Items.Add("Creating Excel instance...");
try
{
XLApp = Activator.CreateInstance(ExcelType);
ExcelType.InvokeMember("Visible",
System.Reflection.BindingFlags.SetProperty, null, XLApp, new object[] {
false });
lstExcelProgress.Items.Add("Trying to open the workbook...");
try
{
XLWorkBook = ExcelType.InvokeMember("Workbooks.Open",
System.Reflection.BindingFlags.InvokeMethod, null, XLApp, new object[]
{ExcelFile});
}
catch (Exception wer)
{
//handle error }
}
catch (Exception er)
{
//handle error
}

Now, the first bit works OK where I create my Excel instance, but the second
bit (opening the workbook) is failing, the error returned being Exception
from HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME).

I assume it doesn't like Workbooks.Open as a method to run, but I'm not sure
how else to do it? Any ideas?

Just off the cuff, remember that while VB6 has the concept of "optional
parameters", which it uses with gay abandon, C# has no such concept.
When you call Office apps from C#, you must pass an argument for every
method parameter. If want to omit an argument then you have to pass
Type.Missing as the argument value.

See the following for more information.

http://msdn2.microsoft.com/en-gb/library/aa192488(office.11).aspx#offcsharp_link3

Again, I don't know whether this is your case: perhaps Workbooks.Open
takes just one argument and you supplied it.

The other thing I notice is that you're passing "Workbooks.Open" as the
method name. I'm not sure that this will work... at least I've never
used Reflection that way. I would think that you would first have to
get a Type for Workbooks, and then Invoke "Open" on _that_ type, rather
than trying to do it all in one go.

 

[james]

Just off the cuff, remember that while VB6 has the concept of "optional
parameters", which it uses with gay abandon, C# has no such concept.
When you call Office apps from C#, you must pass an argument for every
method parameter. If want to omit an argument then you have to pass
Type.Missing as the argument value.

OK - there are actually 14 "optional" parameters for that that I can see in
the version of Excel I am using...

See the following for more information.

http://msdn2.microsoft.com/en-gb/library/aa192488(office.11).aspx#offcsharp_link3

Again, I don't know whether this is your case: perhaps Workbooks.Open
takes just one argument and you supplied it.

The other thing I notice is that you're passing "Workbooks.Open" as the
method name. I'm not sure that this will work... at least I've never
used Reflection that way. I would think that you would first have to
get a Type for Workbooks, and then Invoke "Open" on _that_ type, rather
than trying to do it all in one go.

I think that's more likely - I tried adding 14 Type.Missing's to my command
and I get the same error... the "not found" bit suggests its perhaps not
even finding the method name, leading me to think your latter suggestion is
the one to try.
Thanks,
James