Excel Late Binding with C#

[Walter]

Hi,
I've been having a real hard time converting some VB.NET Excel Automation
code (that works) to C#. I get the app running and can open an existing
spreadsheet but then when trying to get the Workbook reference (and then
hopefully the sheet) I get this error:
Unknown name. (Exception from HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME))

I have tried may methods to get the Workbook reference such as Item,
get_Item, and Open. They all fail. VB uses the Item method (Book =
Books.Item(1) ) to do this so I thought C# would too, but it fails.

Below I'll show the my code then I'll follow it with my C# attempt so far:

VB:
Dim Excel As Object
Dim Books As Object 'Excel.Workbooks
Dim Book As Object 'Excel.Workbook
Dim Sheets As Object 'Excel.Sheets
Dim Sheet As Object 'Excel.Worksheet


Excel = CreateObject("Excel.Application")
Excel.Visible = False

Books = Excel.Workbooks 'get Books reference
Books.Open("c:\myfile.xls")
Book = Books.Item(1) 'get Book reference
Sheets = Book.Worksheets 'get Sheets reference
Sheet = Sheets.item("Monthly Cover") 'get Sheet reference

Excel.Application.Goto(Reference:="MCBillingAccount")
Excel.ActiveCell.FormulaR1C1 = "some data"

Here is C#:
Type Excel;
object excelObject;
object Books;
object Book;
object Sheets;
object Sheet;
object[] Parameters;

Excel = Type.GetTypeFromProgID("Excel.Application");
excelObject = Activator.CreateInstance(Excel);

//get Books reference
Books = excelObject.GetType().InvokeMember("Workbooks",
BindingFlags.GetProperty, null, excelObject, null);

//Open existing spreadsheet
Parameters = new Object[1];
Parameters[0] = pathAndFileName;
Books = excelObject.GetType().InvokeMember("Open",
BindingFlags.InvokeMethod, null, Books, Parameters);

//get the workbook.
Parameters = new Object[1];
Parameters[0] = 1;
//THIS NEXT COMMAND BELOW FAILS
Book = Books.GetType().InvokeMember("Item",
BindingFlags.GetProperty, null, Books, Parameters);

//The code below here has not been reached yet, so I am not sure if it will
work either.
//Get the worksheets collection.
Sheets = Book.GetType().InvokeMember("Worksheets",
BindingFlags.GetProperty, null, Book, null);


//Get the first worksheet.
Parameters = new Object[1];
Parameters[0] = "Monthly Cover";
Sheet = Sheets.GetType().InvokeMember("Item",
BindingFlags.GetProperty, null, Sheets, Parameters);

//Move to Named Reference Cell
Parameters = new Object[1];
Parameters[0] = "MCBillingAccount";
excelObject.GetType().InvokeMember("Goto",
BindingFlags.InvokeMethod, null, Books, Parameters);



Basically, what my app requires is simple:
1. Open existing spreadsheet that has multiple sheets within.
2. Switch to different sheets and insert data into cells, sometimes going to
cell via Named Reference.

Any ideas? Also on how to get to a cell with a Named Reference?

Thanks
Walter

 

[Cindy M.]

Hi =?Utf-8?B?V2FsdGVy?=,

I've been having a real hard time converting some VB.NET Excel Automation
code (that works) to C#. I get the app running and can open an existing
spreadsheet but then when trying to get the Workbook reference (and then
hopefully the sheet) I get this error:
Unknown name. (Exception from HRESULT: 0x80020006 (DISP_E_UNKNOWNNAME))

I have tried may methods to get the Workbook reference such as Item,
get_Item, and Open. They all fail. VB uses the Item method (Book =
Books.Item(1) ) to do this so I thought C# would too, but it fails.

Basically, what my app requires is simple:
1. Open existing spreadsheet that has multiple sheets within.
2. Switch to different sheets and insert data into cells, sometimes going to
cell via Named Reference.

Once I cleaned everything up it worked just fine, here. My sample code is below
my signature line. The Excel.Application with early binding I put in there in
order to get better information on the errors. The culture Info stuff is in
there because my Windows Regional settings are a different language than what I
use in the Excel interface.

What I found, while working this out, is that you have to be very careful about
the text you pass in as a parameter If the name isn't available, you get an
error.

I also noticed that you declare the arguments as a new Object, rather than a
new object (note the small "o"). The two are not the same in C# and you don't
get any compile-time errors if you use Object instead of object, but I don't
think Excel can digest the large "O". (That's why I have the MessageBox in
there, to make sure I actually have a workbook open.)

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

private void btnExcelLateBind_Click(object sender, EventArgs e)
{
Type oExcel;
object excelObject;
object Books;
object Book;
object Sheets;
object Sheet;
object rng;
oExcel = Type.GetTypeFromProgID("Excel.Application");
excelObject = Activator.CreateInstance(oExcel);
System.Globalization.CultureInfo ci =
System.Threading.Thread.CurrentThread.CurrentCulture;
try
{
object[] oArgs = new object[1];
oArgs[0] = -1;
excelObject.GetType().InvokeMember("Visible",
System.Reflection.BindingFlags.SetProperty, null, excelObject, oArgs);

System.Threading.Thread.CurrentThread.CurrentCulture = new
System.Globalization.CultureInfo("en-US");

//get Books reference
Books = excelObject.GetType().InvokeMember("Workbooks",
System.Reflection.BindingFlags.GetProperty, null, excelObject, null);
//Open existing spreadsheet
object[] oArgs2 = new object[1];
oArgs2[0] = @"C:\Test\Test.xlsx";
//Excel.Application appXL = (Excel.Application)
System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
//appXL.Workbooks.Open(oArgs2[0].ToString(), missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing);

Books.GetType().InvokeMember("Open",
System.Reflection.BindingFlags.InvokeMethod,
null, Books, oArgs2);

//object booksCount = Books.GetType().InvokeMember("Count",
System.Reflection.BindingFlags.GetProperty, null, Books, null);
//MessageBox.Show(booksCount.ToString());

oArgs[0] = 1;
Book = Books.GetType().InvokeMember("Item",
System.Reflection.BindingFlags.GetProperty, null, Books, oArgs);

//Get the worksheets collection.
Sheets = Book.GetType().InvokeMember("Worksheets",
System.Reflection.BindingFlags.GetProperty, null, Book, null);

// //Get the first worksheet.
oArgs2[0] = "Sheet1";
Sheet = Sheets.GetType().InvokeMember("Item",
System.Reflection.BindingFlags.GetProperty, null, Sheets, oArgs2);

////Move to Named Reference Cell
oArgs[0] = "Test";
rng = Sheet.GetType().InvokeMember("Range",
System.Reflection.BindingFlags.GetProperty, null, Sheet, oArgs);
rng.GetType().InvokeMember("Select",
System.Reflection.BindingFlags.InvokeMethod, null, rng, null);
//excelObject.GetType().InvokeMember("Goto",
System.Reflection.BindingFlags.InvokeMethod, null, Books, oArgs);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message);
}

finally
{
//object[] oArgs = new object[2];
excelObject.GetType().InvokeMember("Quit",
System.Reflection.BindingFlags.InvokeMethod, null, excelObject, null);
Book = null;
Books = null;
excelObject = null;
System.Threading.Thread.CurrentThread.CurrentCulture = ci;
GC.Collect();
}

}

 

[Giant CooCoo]

Hello, I face the same problem with Worksheets[0] in c#, it was because the index range start at 1 in excel.

----------------------------------------------

string filename = @"Toto.xlsx";

Excel.Application xL = new Excel.Application();
//xL.DisplayAlerts = false;

Excel.Workbook workbook = xL.Workbooks.Add(Type.Missing);

Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
worksheet.Name = "Result";
worksheet.Cells[1,1] = 50;

workbook.SaveAs(filename, Excel.XlFileFormat.xlOpenXMLWorkbook, Type.Missing, Type.Missing, false, false, Excel.XlSaveAsAccessMode.xlShared, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

xL.Quit();