Reference Excel 9 object library

[TJ Dowling]

I am developing an Access/Excel application for a client who uses Office
2000. I am writing the code in Access 2003 but saving as version 2000.
Whenever I send the client an mdb file for testing, they get an error since I
reference the Excel 11 object library while they have the Excel 9 object
library.

I have a copy of Office 2000 but don't want to install it on the same
machine as Office 2003. I just want the reference to the Excel 9 object
library which is found in the file named "Excel9.olb". I copied the olb
file to the folder "C:\Program Files\Microsoft Office\Office" and tried to
reference it from the VBA code window using Tools/References and then the
browse button. I can find the olb file but it never shows up in the
references dialog.

Do I need to register the olb file or is there an easier way to reference
the Excel 9 object library without installing Office 2000?

Thanks,
TJD

 

[Ken Snell MVP]

You cannot "register" the ACCESS 2000 library in your ACCESS 2003 database
when you use ACCESS 2003 to open the file.

In this case, it's better that you use late binding for the EXCEL code that
you run in the ACCESS database instead of early binding -- early binding
requires the library to be registered, late binding does not.

 

[Tony Toews [MVP]]

Do I need to register the olb file or is there an easier way to reference
the Excel 9 object library without installing Office 2000?

Late binding means you can safely remove the reference and only have
an error when the app executes lines of code in question. Rather than
erroring out while starting up the app and not allowing the users in
the app at all. Or when hitting a mid, left or trim function call.

This also is very useful when you don't know version of the external
application will reside on the target system. Or if your organization
is in the middle of moving from one version to another.

For more information including additional text and some detailed links
see the "Late Binding in Microsoft Access" page at
http://www.granite.ab.ca/access/latebinding.htm

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[TJ Dowling]

Thanks for the tip but I would rather not use late binding. I'm maintaining
about 10 mdb applications that use early binding on Excel 9 object library
and I would rather not have to rewrite them all. I'm trying to maintain them
remotely and be able to send them updates that work on their machines without
error.

In the meantime, I've installed Excel and Access 2000 on the same machine
that has Office 2003. When I go into the code window for this app using
Access 2003, I do not see a reference to the Excel 9 object library though I
can find this file:

C:\Program Files\Microsoft Office\Office\Excel9.olb

How can I make this object library appear in my references?

Thanks a bunch,
TJD

 

[Ken Snell MVP]

As I said in my first reply, you cannot have EXCEL 9.0 library reference in
an ACCESS 2003 database.

Having both versions of the software allows ACCESS the ability to replace
EXCEL 11.0 library reference with EXCEL 9.0 library reference when you open
the database using ACCESS 2000. However, you may find that the references
still get messed up when switching from one version to another (in either
direction).

Early binding is susceptible to this problem. Late binding is not.

 

[Tony Toews [MVP]]

Thanks for the tip but I would rather not use late binding. I'm maintaining
about 10 mdb applications that use early binding on Excel 9 object library
and I would rather not have to rewrite them all. I'm trying to maintain them
remotely and be able to send them updates that work on their machines without
error.

<shrug> Generally speaking all you need to do is replace three lines
of code and create any constants that you've used in the code. I
generally only have one or two routines that create Excel spreadsheets
so it's not a big deal.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/