Excel with Early and LateBinding

MumbleB

Hi Guys. I have a slight problem. My application usses EarlyBinding.where I reference Excel 12. Problem I'm having is that most of the machines targeted have Excel 10 or Excel 11 installed so when installing the App or running it I get a GAC error. This I know is that the versions are not backward compatable. My question is, how do I go from earlybinding to LateBinding with as little changes possible. Below I instanciate my Excel Workbook etc. My code is doing loads of stuff and I wouldn't want to rewrite all of it.

        Microsoft.Office.Interop.Excel.Application objExcel = 
            new Microsoft.Office.Interop.Excel.Application();
        objExcel.Visible = false;
        Microsoft.Office.Interop.Excel.Workbook objBook = 
            objExcel.Workbooks.Add(System.Reflection.Missing.Value);
        Microsoft.Office.Interop.Excel.Worksheet objSheet = 
            (Microsoft.Office.Interop.Excel.Worksheet)
            objBook.Worksheets.get\_Item(1);
        Microsoft.Office.Interop.Excel.Range objRange;

Any advice would be appreciated.

Excellence is doing ordinary things extraordinarily well.

Pete OHanlon

You can do this using something like this:

// Instantiate Excel using the prog id.
object xl = Activator.CreateInstance(Type.GetTypeFromProgID("Excel.Application"));
// Get the workbooks collection
object books = xl.GetType().InvokeMember(
"Workbooks", BindingFlags.GetProperty, null, xl, null);
// Add a workbook
object book = books.GetType().InvokeMember( "Add",
BindingFlags.InvokeMethod, null, books, null );
// Get a reference to the worksheets
object sheets = book.GetType().InvokeMember( "Worksheets",
BindingFlags.GetProperty,null, book, null );
// and select the first sheet.
object[] parameters;
parameters = new object[1];
parameters[0] = 1;
object sheet = sheets.GetType().InvokeMember( "Item",
BindingFlags.GetProperty, null, sheets, parameters );
// get the "range" A1.
parameters = new object[2];
parameters[0] = "A1";
parameters[1] = Missing.Value;
object range = sheet.GetType().InvokeMember( "Range",
BindingFlags.GetProperty, null, sheet, parameters );
// And set the value of it to 1.
parameters = new object[1];
parameters[0] = "1";
range.GetType().InvokeMember( "Value",
BindingFlags.SetProperty, null, range, parameters );

// Now, set Excel to visible.
parameters = new object[1];
parameters[0] = true;
xl.GetType().InvokeMember( "Visible",
BindingFlags.SetProperty, null, xl, parameters );

I've just knocked this together in Notepad, so I apologise if it's not 100% perfect.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx

MumbleB

Cool, looks good though. My concern is when I start writing the data, for example see below portion of code.

objSheet.Cells[iR, iC] = CLIENTSURNAME.Trim();
objSheet.Cells[iR, iC + 1] = CLIENTFORENAMES.Trim();
objSheet.Cells[iR, iC + 2] = TITLE.Trim();
objSheet.Cells[iR, iC + 3] = "'" + ADDRESSLINE1.Trim();
objSheet.Cells[iR, iC + 4] = "'" + ADDRESSLINE2.Trim();
objSheet.Cells[iR, iC + 5] = "'" + ADDRESSLINE3.Trim();
objSheet.Cells[iR, iC + 6] = "'" + ADDRESSLINE4.Trim();
objSheet.Cells[iR, iC + 7] = "'" + POSTALCODE.Trim();
objSheet.Cells[iR, iC + 8] = LEVEL;

would I need to do the same as your example above

Pete O'Hanlon wrote:

// get the "range" A1.parameters = new object[2];parameters[0] = "A1";parameters[1] = Missing.Value;object range = sheet.GetType().InvokeMember( "Range", BindingFlags.GetProperty, null, sheet, parameters );

Excellence is doing ordinary things extraordinarily well.

Pete OHanlon

Yup. It's not pretty. I'd write some helper methods to do this if I were you.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys | Onyx

MumbleB

Yeah, this could end up being a right mess then..... :(( .....I have read somewhere that if I use the earliest version and develop on that then I could use earlybinding. I guess though that it would depend if they use the same methods hey? :(( :(( :(( The Nearest Bridge is looking pretty good from my office window right now!!!!

Excellence is doing ordinary things extraordinarily well.