Adding Reference

[AlexanderBB]

Hi,
I need to add a reference programicably to Microsoft ActiveX Data Objects 6.1 Library to about 75 workbooks.
Have spent ages trying stuff in Google but nothing seems to work, not even code to display the GUID.

Can someone give me that (or how to get it) , and I propose to try the following (unless there's something better)

Sub AddReference()
'Macro purpose: To add a reference to the project using the GUID for the
'reference library

Dim strGUID As String

'Update the GUID you need below.
strGUID = "{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}"
'Set to continue in case of error
On Error Resume Next

'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

Any help much appreciated. Also, any idea what Major and Minor mean in the above ??

 

[Yongle]

Your code works if the correct reference is used

I added the reference to Microsoft ActiveX Data Objects 6.1 Library manually, and ran this code:

Sub ListRefPaths()
Dim ref, r As Long
With Sheets.Add.Range("A1:D1")
    .Parent.Cells.Clear
    .Value = Array("Reference", "Version", "GUID", "Path")
    For Each ref In ThisWorkbook.VBProject.References
        r = r + 1
        .Offset(r) = Array(ref.Description, "v." & ref.Major & "." & ref.Minor, ref.GUID, ref.FullPath)
    Next ref
    .Columns.EntireColumn.AutoFit
End With
End Sub

Which returned this list:

Book1
	A	B	C	D
1	Reference	Version	GUID	Path
2	Visual Basic For Applications	v.4.2	{000204EF-0000-0000-C000-000000000046}	C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL
3	Microsoft Excel 16.0 Object Library	v.1.9	{00020813-0000-0000-C000-000000000046}	C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE
4	OLE Automation	v.2.0	{00020430-0000-0000-C000-000000000046}	C:\Windows\SysWOW64\stdole2.tlb
5	Microsoft Visual Basic for Applications Extensibility 5.3	v.5.3	{0002E157-0000-0000-C000-000000000046}	C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
6	Microsoft ActiveX Data Objects 6.1 Library	v.6.1	{B691E011-1797-432E-907A-4D8C69339129}	C:\Program Files (x86)\Common Files\System\ado\msado15.dll
7	Microsoft Office 16.0 Object Library	v.2.8	{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}	C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16\MSO.DLL
Sheet2

I cannot help you with your other questions

 

[AlexanderBB]

Thanks Yongle, that's all I need. Great.