auto loading vb reference libraries

[lasw10]

Thought I'd chuck this on as it's something I use regularly and something I see a lot of other people mentioning in posts....

If you use code reliant on non-standard vb libraries then you can use the GUID for that library to reference it on the workbook being opened - then use the name of the library to remove it again when closing the file. Saves going in and ticking the references everytime!!

The below is what I use when working with ADO - I can not take credit for this as I found it on another forum but thought i'd post it up for others to use.

You can add these to your "ThisWorkbook" Sheet ... will install ADO (2.5 or above) and remove it on open and close of the workbook.

Private Sub Workbook_Open() 
     
    On Error Resume Next 
     'Reference ADO Object Library using Major / Minor GUID
    Set ID = ThisWorkbook.VBProject.References 
    ID.AddFromGuid "{00000205-0000-0010-8000-00AA006D2EA4}", 2, 5 
     
End Sub 

Private Sub Workbook_BeforeClose(Cancel As Boolean) 
     
     'remove ADO reference
    Dim x As Object 
    n = Application.VBE.ActiveVBProject.References.Count 
     
    Do While Application.VBE.ActiveVBProject.References.Count > 0 And n > 0 
        On Error Resume Next 
        Set x = Application.VBE.ActiveVBProject.References.Item(n) 
        y = x.Name 
        If y = "ADODB" Then 
            Application.VBE.ActiveVBProject.References.Remove x 
        End If 
        n = n - 1 
    Loop 
     
End Sub 

If you don't know the GUID then reference it (by ticking it) and then run this to list all the active libraries and their GUIDs - adapted from NateO post on Mr E

Sub Grab_References() 
     
    Dim n As Integer 
     
    Sheets.Add 
    ActiveSheet.Name = "GUIDS" 
     
    On Error Resume Next 
    For n = 1 To ActiveWorkbook.VBProject.References.Count 
        Cells(n,1) = ActiveWorkbook.VBProject.References.Item(n).Name 
        Cells(n,2) = ActiveWorkbook.VBProject.References.Item(n).Description 
        Cells(n,3) = ActiveWorkbook.VBProject.References.Item(n).GUID 
        Cells(n,4) = ActiveWorkbook.VBProject.References.Item(n).Major 
        Cells(n,5) = ActiveWorkbook.VBProject.References.Item(n).Minor 
        Cells(n,6) = ActiveWorkbook.VBProject.References.Item(n).fullpath 
    Next n 
     
End Sub 

Hope that proves as useful for others as it has for me.

[XL-Dennis]

G8 to publish it

In addition:

Make sure that You refer to the earliest version of the GUID-reference as older versions does not recognise it while later versions of the involved libraries do.

BTW, check out the following article at Excel KB:
http://www.excelkb.com/article.aspx?id=10076 which shows a more convenient way for removing a specific reference.

[mangesh_yadav]

When I try this, I get an error saying "cannot programmatically add reference". How do I solve this error

- Mangesh

[XL-Dennis]

Hi Mangesh and welcome to the board

Could You pls provide the board with Your code?

[Ivan F Moala]

G8 to publish it

In addition:

Make sure that You refer to the earliest version of the GUID-reference as older versions does not recognise it while later versions of the involved libraries do.

BTW, check out the following article at Excel KB:
http://www.excelkb.com/article.aspx?id=10076 which shows a more convenient way for removing a specific reference.

CM Dennis, good link

And yes, a lot of people don't realise this. You don't need / should reference the version numbers. That's because the ProgID has a VersionIndependent Value @

For Excel
HKEY_CLASSES_ROOT\CLSID\{00024500-0000-0000-C000-000000000046}\VersionIndependentProgID

Value = Excel.Application

But then I mentioned this @

http://216.92.17.166/board2/viewtopi...er=asc&start=0

Where Lasw10 got the code from.

and also

http://www.xcelfiles.com/WhatsUpClass.html

Your site is looking good

[XL-Dennis]

Hi CM Ivan,

Thanks for pointing out the VersionIndependent ProgID

But doesn't the version-independent ProgID correspond to the name of the latest version of the object application, i e latest CLSID?

I´ve noticed this behaviour when working with computers that have several versions of Excel as well as ADO installed (where the versions have been installed like 97, 2000 / ADO 2.5/ 2.6 / 2.7 etc and not the opposite), i e it refer to the latest Excel-version only.

Since this have been a major issue for me I decided to use only the GUID-reference.

Anyway, I will check it out via Your great link

Thanks for Your kind comment about my english site

[Ivan F Moala]

But doesn't the version-independent ProgID correspond to the name of the latest version of the object application, i e latest CLSID?

I´ve noticed this behaviour when working with computers that have several versions of Excel as well as ADO installed (where the versions have been installed like 97, 2000 / ADO 2.5/ 2.6 / 2.7 etc and not the opposite), i e it refer to the latest Excel-version only.

Yes, that's right Dennis, it will always take on the latest version, which is why you should install the oldest to latest versions when installing multiple versions.

[XL-Dennis]

it will always take on the latest version, which is why you should install the oldest to latest versions when installing multiple versions.

Yes, I agree and I have been forced to do it on my own computers as well (!) but for ADO it's more tricky and therefore I use the reference to 2.5 as newer versions of the library contain previous version references (at least what I've found out).

Thanks for taking Your time on this subject.

[Jack in the UK]

Ivan / MBTCM Dennis

Yes this is the same regardless what you load care / caution is required, Office needs to be as i have said loads of times 97 / 2000 / 2002 / 2003 and 2005 when out, so stops and kind of clash and lick what you need to each used version - also load carefully not to defaults so that means manual directory naming conventions.

Jack

BTW sorry a bit late :-Geesa

[breakingstein]

Hi all,

Anyone know how to add a reference by name? I want the VBA to create a reference to SOLVER on the Workbook Open event.

Regards,
B