Object Library not supported

[Guest]

I have created a database in Office XP in a VB Module. I have a reference to
Excel 10.0. When my co-worker - who is working in Access 2003 - opens the DB
- I created and runs the module - he receives an error that states 'Object
Library not supported' and refers back to a newly added reference to Excel
11.0.

I need this to be shared by end users who have both XP and 2003 versions.

Any thoughts?

 

[Terry Kreft]

There are a couple of ways to deal with this.

a) Programatically fix references
b) Use late binding

Personally, unless your use of Excel is intensive I would go with late
binding.

 

[Guest]

How would I go about fixing it programitically and I have never heard of Late
Binding? What is it?

 

[Terry Kreft]

To fix it programaticallyyou would need to do the following
Before any other code runs
Check the version of Excel available.
If the version available isn't the one your using then drop the
reference then recreate the reference.

All the code used in this section has to be disambiguated also this method
can't be used if you're distributing and MDE file.

Late Binding
--------------
This involves dropping the reference to Excel changing your declarations and
changing how you instantiate some objects.


e.g. Say you have code like this
Function TestXL()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

Set xlApp = New Excel.Application

Set xlWB = xlApp.Workbooks.Add
Set xlWS = xlWB.Worksheets.Add

xlWS.Name = "NewSheet"

xlApp.Visible = True

Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing

End Function

You would change this to
Function TestXL()
Dim xlApp As Object
Dim xlWB As Object
Dim xlWS As Object

Set xlApp = CreateObject("Excel.Application")

Set xlWB = xlApp.Workbooks.Add
Set xlWS = xlWB.Worksheets.Add

xlWS.Name = "NewSheet"

xlApp.Visible = True

Set xlWS = Nothing
Set xlWB = Nothing
Set xlApp = Nothing

End Function

The only other thing you would hav to look out or is if you've used named
constants but then you would just declare them after removing the reference.

 

[Guest]

Terry,

Thank you for your time. You have led us in a good direction. It's proving
a little more difficult and therefore needing more time to work on it.

Again, Thanks!