Excel library reference in VBA - How do I set in code?

[ejsmith]

I have lots of code that was written using DAO and also uses the Microsoft Excel 10.0 Object Library. Of course I set these references using the Tools...References menu options in the module.
Problem is that in order for this code to run on someone else's machine I have to manually set the references.
I'd like to set both the DAO and Excel references in code... can anybody show me how or point me in the right direction?
Thanks!

 

[bboffin]

The Application.References object has two methods that allow you to add references:

AddFromFile where you supply the path of the file (dll, ocx, tlb etc) that you wish to add

AddFromGuid where you supply the GUID and its major and minor version ids.

All you have to do is figure out which references you need to add and then call one or other of these methods in your startup code.

 

[ejsmith]

Thanks bboffin! I was able to set the references using the AddFromFile method. The only drawback being that I'll have to write code to find the files if a user doesn't have the same installation as I do. I'm curious - where would I find a particular library's GUID -and would that eliminate the need to locate the dll (or exe) files?
Thanks again for your help!

 

[bboffin]

All the information that links a GUID to a specific file is held in the Registry in the HKEY_CLASSES_ROOT hive (HKCR).

For example if you wish to find the GUID for Excel.Application then open Regedit and look for a key called "Excel.Application" in HKCR. This has a subkey of CLSID which contains as the default value the GUID for this class.

If you now search for that GUID (including the curly brackets) from the top of HKCR you will find an entry in HKCR\CLSID that has a number of subkeys. If you look in LocalServer or LocalServer32 you will find the full path to the code that implements that class. (For some other classes it may be in InProcHandler)

In your case if you're pretty sure that same version of Excel has been installed on each machine you can simply use the GUID from your machine as the argument to AddFromGuid and windows will take care of the rest or give you an error that you can detect and act upon.

 

[ejsmith]

bboffin - I'm not sure what version everyone has so it looks like finding the files is the way to go.
Thanks for all your help!