Accomodating several Excel Versions (Development and Production)

[Goodall]

I am using Microsoft Excel 2003 on my computer in development. I added the COM reference for "Microsoft Excel 11.0 Object Library". Via VB .NET 2003 I create and Excel file without a hitch. My users have Excel 2003 and possibly earlier. I have no problem creating the Excel files but they get the error that says "File or assembly name Microsoft.Office.Interop.Excel, or one of its dependencies, was not found". What seems to be the problem and how might I correct it? Thanks.

 

[Goodall]

A major correction on my previous post. My users have Excel 2000 or earlier. They do NOT have Excel 2003.

 

[RiverGuy]

You can try late-binding, use CreateObject().

 

[chiph]

Because the different versions of Excel have different libraries, this sounds like a good place to apply a pattern.

You can choose between a Factory, a Facade, or a Plugin pattern, depending on how you intend to use/access Excel.

Facade:

http://en.wikipedia.org/wiki/Facade_pattern

Factory:

http://en.wikipedia.org/wiki/Factory_method_pattern

Plugin:

http://www.martinfowler.com/eaaCatalog/plugin.html

The basic idea behind all of them is that you put the version-dependent code off in it's own module, which other code can then access via a generic programming API (typically done via an Interface).

So, if you need a way to get the contents of a cell, you create a GetCellValue method in your Interface, then implement that interface in your Excel2k, Excel 2k3, and Excel95 DLLs. You load the correct DLL by finding out what version they have installed (inspecting the registry, most likely). If you decide to support Excel 4 (or some other version) all you have to do is write a new DLL that implements your interface, then change the code that decides which DLL to load.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[Goodall]

Late-Binding was the solution. Here is what I did.

1) Removed the Excel COM reference via the Solution Explorer

2) Commented out the prior declarations and replaced them as follows:

'Dim xlApp As Microsoft.Office.Interop.Excel.Application
'Dim xlBook As Microsoft.Office.Interop.Excel.Workbook
'Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim xlApp As Object
Dim xlBook As Object
Dim xlsheet As Object

3) Commented out the prior object "creations" and replaced them as follows:

'xlApp = CType(CreateObject("Excel.Application"),Excel.Application)
'xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
'xlsheet = CType(xlBook.Worksheets(1), Excel.Worksheet)
xlApp = CreateObject("Excel.Application")
xlBook = xlApp.workbooks.add()
xlsheet = xlBook.Worksheets(1)

4) To save the workbook I used this code:

xlBook.SaveAs("C:\" & FileName & FileExtension)

Now machines with Excel 2000 and Excel 2003 will both create the Excel document in the appropriate version.

The downside with this approach is that the Excel object model is not "exposed" during the development process so I develop using early-binding and change over to late-binding when I want to promote the code.