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
Comments