autoload references

[Hungoverhippie]

Is there a way in Excel VBA to add the references I need loaded on the fly. I have done a few excel macros that talk to my sql databases but everytime I give it to someone I have to go load all the references by hand that the macro needs before it will run for them.. Sorry may be a simple question kinda new to VBA..

Thanks
Hungoverhippie

 

[acron]

You can use the following :

Thisworkbook.VBProject.References.AddFromFile _
"FilePath/FileName"

where you need to know the file name and path of each file you want to include. As at least the file paths can be different on different machines, there is another method available, but it requires that each reference has a GUID, i.e. Globally Unique Identifier. However to dao that you need to know the GUID. The best way to find if the GUIDs exist and if so what value they have is to set a reference to the files you are interested in as well as a reference to the VBA Extensibility Libray and run the following code :

Dim refVBE As VBIDE.Reference
For Each refVBE In ThisWorkbook.VBProject.References
Debug.Print refVBE.GUID, refVBE.FullPath
Next refVBE
End Sub

Actually you can set the reference to the VBA Extensibility by using the following example of using AddFromGUID !!

Sub AddVBEEXT_Ref()
Application.VBE.ActiveVBProject.References. _
AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 0, 0
End Sub

As you an see the GUID is a long a strange looking number and includes the opening "{" and closing "}". Not too easy to remember.



Hope something of the above is of help and that all your references have a GUID.

AC