Announcement

Collapse
No announcement yet.

auto loading vb reference libraries

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

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

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

  • #2
    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

    Comment


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

      - Mangesh

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment


          • #6
            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?

            Ive 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

            Comment


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

              Ive 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

              Comment


              • #8
                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

                Comment


                • #9
                  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

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      Re: auto loading vb reference libraries

                      Breakingstein
                      Please start a question for this in the Excel/VBA Forum add a reference to this post
                      Hope that Helps

                      Roy

                      New users should read the Forum Rules before posting

                      For free Excel tools & articles visit my web site

                      If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

                      RoyUK's Web Site

                      royUK's Database Form

                      Where to paste code from the Forum

                      About me.

                      Comment

                      Working...
                      X