Add a reference reference programmatically vba-Excel

Ce code vous permet d'ajouter une référence à une bibliothèque spécifique en temps d'exécution.
C'est très utile quand on partage les classeurs entre différentes versions d'Excel.

J’ai vu cette page :
http://www.vbaexpress.com/kb/getarticle.php?kb_id=267

On a toujours les «MAIS ».

Mais, comment peut-on trouver le GUID?

1. Ouvrir le «References – VBA Project » 

On va ajouter le « Microsoft SQL Distribution Control 10.0 »

2. Ouvrir le « Regedit » et chercher la référence par le nom.

3. Copier la clef

HKEY_CLASSES_ROOT\TypeLib\{4AC4D38E-B5C0-4224-B98D-946D58684F39}

Utilisation: Adicioner la reference à “Microsoft ActiveX Data Objects 2.0 Library”

Clet touvée:  00000200-0000-0010-8000-00AA006D2EA4

Sub AddReferenceAdo2()

     'Macro purpose:  To add a reference to the project using the GUID for the

     'reference library

    

    Dim strGUID As String, theRef As Variant, i As Long

    

     'Update the GUID you need below.

    strGUID = "{00000200-0000-0010-8000-00AA006D2EA4}"

    

     'Set to continue in case of error

    On Error Resume Next

    

     'Remove any missing references

    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1

        Set theRef = ThisWorkbook.VBProject.References.Item(i)

        If theRef.isbroken = True Then

            ThisWorkbook.VBProject.References.Remove theRef

        End If

    Next i

    

     'Clear any errors so that error trapping for GUID additions can be evaluated

    Err.Clear

    

     'Add the reference

    ThisWorkbook.VBProject.References.AddFromGuid GUID:=strGUID, Major:=1, Minor:=0

    

     'If an error was encountered, inform the user

    Select Case Err.Number

    Case Is = 32813

         'Reference already in use.  No action necessary

    Case Is = vbNullString

         'Reference added without issue

    Case Else

         'An unknown error was encountered, so alert the user

        MsgBox "A problem was encountered trying to" & vbNewLine _

        & "add or remove a reference in this file" & vbNewLine & "Please check the " _

        & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"

    End Select

    On Error GoTo 0

End Sub