Handling library references when distributing worksheets

[relative_virtue]

I've just written a complex VBA-enabled project that gets sent out to
60+ people via a background call to Lotus Notes. It invites them to
fill in information on the spreadsheet, then submit it back to me via
a spreadsheet button and another background call to Notes.

Naturally, however, we have encountered a few problems. While the
code seems to work fine in Excel 2003, it does not work in Excel 97 -
hardly surprising, but a pain in the neck. I'm confident I can
program around this to some extent, however.

The bigger problem is there seem to be missing libraries on a number
of machines. Or, not missing libraries, but ones that are not in the
expected location. I am referencing the following libraries:

Visual Basic for Application
Microsoft Excel 11.0 Object Library
OLE Automation
Microsoft Office 11.0 Object Library
Windows Script Hosting Model
Lotus Domino Objects
Lotus Notes Automation Classes

One one occasion, someone submitted his spreadsheet back to me and it
said *I* was missing a library! It turned out that it was, for some
reason, trying to reference D:\ for the Lotus Domino Objects
(domobj.tlb) instead of C:\, which is what I had set it to. What
could cause this?

More importantly, is there any way I can programatically search for a
missing library if it can't be directly referenced?

Any and all suggestions welcome,

Tristan

 

[Bob Phillips]

The better way is to install the LOWEST versions of each of the applications
that any of your users might have on a machine, and develop and test your
code on this, then distribute that.

Another way is to use late binding, where you don't reference the libraries,
they get looked up at run-time. Here is an article about a development
methodology (using Outlook, but the principle is the same)
http://xldynamic.com/source/xld.EarlyLate.html

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Jon Peltier]

When the file makes a round trip, the library reference might update
properly on the second machine, but then it cannot downdate back on the
first.

A client had a workbook which relied heavily on Solver, and the workbook was
distributed around his company, which had users on Excel 97 through 2003.
The references were killing us, and we tried programmatically updating them,
which was a horror show. We finally had to rely on late binding and using
Application.Run for the Solver calls.

- Jon