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

 1
 2Sub AddReferenceAdo2()
 3
 4     'Macro purpose:  To add a reference to the project using the GUID for the
 5
 6     'reference library
 7
 8    Dim strGUID As String, theRef As Variant, i As Long
 9
10     'Update the GUID you need below.
11
12    strGUID = "{00000200-0000-0010-8000-00AA006D2EA4}"
13
14     'Set to continue in case of error
15
16    On Error Resume Next
17
18     'Remove any missing references
19
20    For i = ThisWorkbook.VBProject.References.Count To 1 Step -1
21
22        Set theRef = ThisWorkbook.VBProject.References.Item(i)
23
24        If theRef.isbroken = True Then
25
26            ThisWorkbook.VBProject.References.Remove theRef
27
28        End If
29
30    Next i
31
32     'Clear any errors so that error trapping for GUID additions can be evaluated
33
34    Err.Clear
35
36     'Add the reference
37
38    ThisWorkbook.VBProject.References.AddFromGuid GUID:=strGUID, Major:=1, Minor:=0
39
40     'If an error was encountered, inform the user
41
42    Select Case Err.Number
43
44    Case Is = 32813
45
46         'Reference already in use.  No action necessary
47
48    Case Is = vbNullString
49
50         'Reference added without issue
51
52    Case Else
53
54         'An unknown error was encountered, so alert the user
55
56        MsgBox "A problem was encountered trying to" & vbNewLine _
57
58        & "add or remove a reference in this file" & vbNewLine & "Please check the " _
59
60        & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
61
62    End Select
63
64    On Error GoTo 0
65
66End Sub