Adding Reference

AlexanderBB

Well-known Member
Joined
Jul 1, 2009
Messages
1,713
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)

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

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Your code works if the correct reference is used :oops:

I added the reference to Microsoft ActiveX Data Objects 6.1 Library manually, and ran this code:
VBA 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
ABCD
1ReferenceVersionGUIDPath
2Visual Basic For Applicationsv.4.2{000204EF-0000-0000-C000-000000000046}C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL
3Microsoft Excel 16.0 Object Libraryv.1.9{00020813-0000-0000-C000-000000000046}C:\Program Files (x86)\Microsoft Office\root\Office16\EXCEL.EXE
4OLE Automationv.2.0{00020430-0000-0000-C000-000000000046}C:\Windows\SysWOW64\stdole2.tlb
5Microsoft Visual Basic for Applications Extensibility 5.3v.5.3{0002E157-0000-0000-C000-000000000046}C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
6Microsoft ActiveX Data Objects 6.1 Libraryv.6.1{B691E011-1797-432E-907A-4D8C69339129}C:\Program Files (x86)\Common Files\System\ado\msado15.dll
7Microsoft Office 16.0 Object Libraryv.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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,231
Messages
5,600,427
Members
414,384
Latest member
joehalks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top