Referencing two versions Object models

[Cory]

Good afternoon,

I have developed a rather lengthy project in Excel 2003 that I would like to
allow my coworkers to use. Unfortunately they have Office XP/2002, and when
they open the file, they are greeted with "Cannot Find Library or some such
since the VBE is looking for the Office 2003 reference. Now I could just
have them copy the office 2003 .tlb file to the appropriate directory on
their systems, but that portion of the file structure has permissions in
place that keep lowly users such as us from modyfying it and would require an
admin to copy each one by had.

Is it possible to have the code selectively reference different object
libraries based on the version of the Office program running? And if I have
already specified Office11 (Office 2003) libraries will this keep the "Can't
remove control or Reference; in use" message from stopping the show? Thank
you in advance for any hyelp that you might give me on this vexxing matter.

Cory

 

[Jim Thomlinson]

The best way around this is to use late binding. Right now you are using
early binding which meand that in the VBE you have Tools -> References ->
Microsoft Word 11.0 (or something similar). Using late binding you remove
that reference and use code similar to this...

dim appWord as object

set appword = createobject("word.application")

This creates an instance of Word in whatever version they have. Thus no
problem with references.

The down sides are that the code will be marginally slower (normally not an
issue as the difference is very small). Coding will not have intellisense to
help you (I tend to add the reference for developement purposes and then
change my declarations and remove the references at the end). Finally you
will not be able to use any of the built in constants. You will either have
to use the actual value associated with the constants or create constants of
your own to reference (that is what I do as i have some code to create all of
the constants).

 

[Cory]

So since I am referencing the ActiveWorkbook throughout the code, would I
reference it through the app object? Or could I create an object to represent
the active workbook.

Additionally, would I need to change the references to other items that are
normally in the Object Library i am trying to reference out? Such as
unqhalified Range objects and what not? VBA assumes that those Range objects
fall under a certain portion of the object model unles you specify otherwise.
So would I have to go through and update those lines with a fully qualified
myObject.Workbooks("Thisworkbook.xls").Sheets(1).Range("A1:C5") yada yada...

I am just trying to get a handle on how painful this is going to be...

Cory

 

[Tim Zych]

they open the file, they are greeted with "Cannot Find Library or some
such

since the VBE is looking for the Office 2003 reference.

How do you arrive at that conclusion? Are you sure it's the Office reference
that's MISSING, and not something else?

Excel should be smart enough to refer to the prior versions (Excel, Office)
when they open it. As a test I tried an XL2003 project out on an
XL2000/Office2000 machine and the references automatically adjusted. I don't
have Office 2003 on that machine.

When you open the 2003 project on the 2002 machine, is the Excel/Office
reference MISSING?

 

[Jon Peltier]

Actually, the BEST way is to do the development in the earlier version of
Excel, to ensure you don't use objects and members added to the newer object
model. As Tim points out, you shouldn't get a warning that the Excel or
Office libraries are missing, it's probably some other library. Also,
sending them the later object library files will probably not work, and will
definitely violate your license and theirs.

- Jon