How set ADO reference in a VBA protected Excel file?

[LowDef]

I am sending a VBAProject password protected Excel file to multiple users. If the users do not have a reference to the ADO Library - how can they set it without knowing the VBA password? I would prefer to keep the code protected.

I have a reference set to the ADO Ext 6.0 Library in the file but don't know if other users have 6.0 or other version on their PCs.

 

[Trevor G]

Try out this code, you may have to adapt it slightly.

Sub AddReference()
'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 = "{00020905-0000-0000-C000-000000000046}"

'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

Trevor

 

[dk]

If you want a robust solution that is going to work whatever version of ADO is installed on the target machine then you should look at late binding - that is getting rid of any references in your Excel project and making some changes to your code. Here is an example of early bound (what you're currently using):

Sub Early()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String

Set cn = New ADODB.Connection
cn.Open "some_connection_string"

Set rs = New ADODB.Recordset
rs.Open "select * from blah", cn, adOpenForwardOnly, adLockOptimistic

Range("A1").CopyFromRecordset rs

rs.Close
cn.Close

End Sub

Here is the same code as late bound - you basically declare the variables as object, change any New statements to Createobject and either use the literal values (such as 3 rather than adLockOptimistic) or preferably define the constants yourself.

Public Const adOpenForwardOnly As Long = 0
Public Const adLockOptimistic = 3

Sub Late()
Dim cn As Object
Dim rs As Object
Dim sSQL As String

Set cn = CreateObject("ADODB.Connection")
cn.Open "some_connection_string"

Set rs = CreateObject("ADODB.Recordset")
rs.Open "select * from blah", cn, adOpenForwardOnly, adLockOptimistic

Range("A1").CopyFromRecordset rs

rs.Close
cn.Close


End Sub

Hope that helps - let me know if you have any questions.

DK

 

[xenou]

Thanks DK - amazingly, I do this all the time with other objects and never thought of applying late binding to ADO objects.