Adding references from within VBA code

[HammerTime]

Hi all,

I have a workbook that has a wee bit of code which makes an Access Table from within excel. It requires a reference to the Microsoft DAO 3.6 Object Library, and to date I have just selected it from the Tools menu in the VBA editor as needed.

I would like to give this workbook to some coworkers who are not familiar with macros. Is there a line or two of code which checks to see if this library is referenced, and if not adds it?

Thanks and have a good ANZAC weekend

[Leith Ross]

Hello HammerTime,

Here is a macro to add the reference.

Please Login or Register  to view this content.

[HammerTime]

Awesome! Thanks Leith

[HammerTime]

oops! Actually it gives me the following error:

"VBE Object library not loaded
Object library no registered"

[Leith Ross]

Hello HammerTime,

Check your reference in the VBE and see if the library is there. If version numbers are different that will cause an error. This can be a potential problem with other computers if those computers are not using the same version of Office as you.

[HammerTime]

It appears to be there!

I just noticed that you have "Visual" in the title. Mine is simply "Microsoft DAO 3.6 Object Library", are these different?

Also, where can I find that long reference number? (in case I want to change the code to add a different ref.)

Also, we have a common excel install in all computers, so the document will be shared with people that (should) have an identical set up as me.

Thanks again for the help :D

[Leith Ross]

Hello HammerTime,

Fooled again by Microsoft. It isn't a standard type library but a DLL. Requires a slightly different loading method. Working on an update.

[Leith Ross]

Hello HammerTime,

I am stumped. I can't load it from VBA even if I register the dll before setting the VBE reference. I have done this successfully with other modules in the past. Perhaps someone else knows the answer.

[royUK]

Once you add a Reference manually & save the workbook the reference remains when the workbook is distributed.The only problem would be if the dll is missing from the other computer

[HammerTime]

Ok thanks Roy,
I'll do some tests on a few computer around the office