Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: auto loading vb reference libraries

  1. #1
    Join Date
    25th May 2004
    Location
    London
    Posts
    447

    auto loading vb reference libraries

    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.

    VB:
    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 
    
    
    VB:
    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

    VB:
    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th January 2003
    Location
    Ístersund, Sweden
    Posts
    2,451
    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.
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  3. #3
    Join Date
    8th September 2004
    Location
    India
    Posts
    12
    When I try this, I get an error saying "cannot programmatically add reference". How do I solve this error

    - Mangesh

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    25th January 2003
    Location
    Ístersund, Sweden
    Posts
    2,451
    Hi Mangesh and welcome to the board

    Could You pls provide the board with Your code?
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  5. #5
    Join Date
    27th January 2003
    Location
    Auckland, New Zealand
    Posts
    396
    Quote Originally Posted by 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.
    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
    Kind Regards,
    Ivan F Moala From the City of Sails

    http://www.xcelfiles.com

  6. #6
    Join Date
    25th January 2003
    Location
    Ístersund, Sweden
    Posts
    2,451
    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
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  7. #7
    Join Date
    27th January 2003
    Location
    Auckland, New Zealand
    Posts
    396
    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.
    Kind Regards,
    Ivan F Moala From the City of Sails

    http://www.xcelfiles.com

  8. #8
    Join Date
    25th January 2003
    Location
    Ístersund, Sweden
    Posts
    2,451
    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.
    Kind regards,
    Dennis

    .NET & Excel | 2nd edition PED | MVP

  9. #9
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,684

    Re: auto loading vb reference libraries

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    9th February 2005
    Location
    London
    Posts
    83

    Re: auto loading vb reference libraries

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 2 users browsing this thread. (1 members and 1 guests)

  1. jbmcdan

Possible Answers

  1. Missing Reference Libraries
    By Andrew5 in forum Technical Issues Help
    Replies: 5
    Last Post: March 16th, 2006, 10:21
  2. Reference Libraries causing Errors
    By iwrk4dedpr in forum EXCEL HELP
    Replies: 1
    Last Post: November 11th, 2005, 17:57
  3. Office 2003 Reference Libraries
    By socha in forum EXCEL HELP
    Replies: 3
    Last Post: September 14th, 2005, 20:07
  4. Reference libraries
    By Snakey2005 in forum EXCEL HELP
    Replies: 1
    Last Post: March 8th, 2005, 17:28
  5. Iasw10 - auto loading vb reference libraries
    By Ranger in forum EXCEL HELP
    Replies: 2
    Last Post: August 14th, 2004, 17:19

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno