Programmatically Add/Remove Library References

[lickalotapus]

Hi all,

I have a database in Access XP which I share with an Access 2000 user. There are several functions in the database which output things to Excel. Therefore, the Excel object library is loaded under Tools...References.

The problem is that everytime I open the database (in XP), this reference gets updated to Excel 10.0 object model. Then, when the Access 2000 user opens the database, they get a missing library error and have to manually de-select the Excel 10.0 object model and select the Excel 9.0 object model.

I am trying to get around this whole mess by creating a simple auto_exec macro which checks for missing references, deletes them, and adds the correct reference.

So far I've been able to loop through all of the references in the database and check the IsBroken property to tell me what's not working. I am also able to add a reference using the AddFromGUID method of the References collection. However, I cannot seem to remove a broken reference, no matter what I do. Here is a bit of sample code to give an idea what I'm attempting...

Dim ref As Reference
For Each ref In References
    If ref.IsBroken = True Then Application.References.Remove ref
Next

When I attempt to run this in the database containing a missing reference, I get a "Object Library Not Registered" run-time error. Yet I have no problem removing the reference manually. I feel like I'm one step away from conquering this whole problem, so if anyone could shed some light on how to programmatically remove a reference, it would be greatly appreciated.

 

[Dugantrain]

You ever get this figured out? I'd like the same information, particularly with the Excel 9.0/10.0 reference that you just described.

 

[lickalotapus]

Dugantrain,

I did figure it out, although the solution is different from what I was attempting. Apparently there is no way to dynamically remove a "missing" reference. It's a bit of a Catch 22 - you need to remove the missing reference, but you can't tell VBA what to remove because it's missing.

However, you can avoid these missing library errors by using Late Binding instead of Early Binding. Here is a great page which explains the differences:

http://www.mvps.org/word/FAQs/InterDev/EarlyvsLateBinding.htm

In a nutshell, you use generic Object variables and create references on-the-fly using syntax like CreateObject("Excel.Application") instead of clicking the Excel Object Model library and using syntax like Dim xlApp as Excel.Application and so forth. This makes your code run slower (although I didn't notice a difference), but ensures version-independence since you are creating the Excel reference at run-time instead of at compile-time.

Hope that helps...

 

[Dugantrain]

I'll be damned. All of this time I've been Late-Binding my Excel objects AND setting a Reference; turns out the entire time I never even needed the Reference because of the Late Binding.

 

[lickalotapus]

That was exactly my situation too. I think the Microsoft examples must use a combination of late and early binding, because that's probably where I copied my code from to begin with.

But the good news is that you now have to do very little to get rid of that reference library. Just make sure all your Excel variables are Dim'd (is that a word?) as Object and replace any Excel-specific constants (e.g. xlDelimited, xlYesNo, etc.) with their actual values. One casualty of removing the library reference is that you can no longer use those constants.

Here's a page I have been using to convert the constants:
http://techsupt.windowware.com/TS/T000001033005F9.html

Another annoyance is that you don't get the nice drop-down boxes or tool tips as you code, since the VBA editor doesn't have the Excel library for reference. But these things are much less of an inconvenience than having to manually change the library references every time a different user (with a different version of Access) opens the database...