R
RB Smissaert
Trying to come up with a foolproof way to add the current ADO library to the
project's references.
I used to do this by just saving the .xla with a reference to a low version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.
Then I had a method that got the ADO library file path from registry reads
using code from KeepITCool:
Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _
"winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function
Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not exactly
sure why.
Currently I use this method:
Save the .xla with the lowest ADO version I have on my development machine,
2.1
In the Workbook_Open event remove this reference and add the current library
like this,
slightly simplified:
Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As Boolean
Dim VBProj As VBProject
On Error GoTo ERROROUT
If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If
Set VBProj = Workbooks(strWorkbook).VBProject
VBProj.References.AddFromFile strFilePath
Exit Function
ERROROUT:
End Function
Sub SetADOReference()
Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles
Const strINIPath As String = "C:\test.ini"
strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")
If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) > 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If
strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"
Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing
Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select
If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If
arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")
For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then
Exit Sub
End If
Next
MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert: (e-mail address removed)", _
vbExclamation, "adding ADO reference"
End Sub
Sofar this seems to work fine. I know it is overkill, but as this is so
tricky I can't be
careful enough. The .ini file read should always make it possible for the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there always
is the brute force
method with the array.
Have read a lot of postings about this problem, but there doesn't seem to be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?
RBS
project's references.
I used to do this by just saving the .xla with a reference to a low version,
2.5 and that worked fine for
a long time, but then came across a user where this failed.
Then I had a method that got the ADO library file path from registry reads
using code from KeepITCool:
Function GetLibrary(sProgID$) As String
Dim oReg As Object, sDat$
Const HKCR = &H80000000
Set oReg = GetObject( _
"winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
oReg.getstringvalue _
HKCR, sProgID & "\CLSID", vbNullString, sDat
oReg.getstringvalue _
HKCR, "CLSID\" & sDat & "\Inprocserver32", vbNullString, sDat
GetLibrary = sDat
End Function
Except I re-wrote this by using the Windows API.
This solved this one user's problem, but it failed with others, not exactly
sure why.
Currently I use this method:
Save the .xla with the lowest ADO version I have on my development machine,
2.1
In the Workbook_Open event remove this reference and add the current library
like this,
slightly simplified:
Function AddReferenceFromFile(strFilePath As String, _
Optional strWorkbook As String) As Boolean
Dim VBProj As VBProject
On Error GoTo ERROROUT
If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If
Set VBProj = Workbooks(strWorkbook).VBProject
VBProj.References.AddFromFile strFilePath
Exit Function
ERROROUT:
End Function
Sub SetADOReference()
Dim i As Byte
Dim ADOConn As Object
Dim strADOVersion As String
Dim strADOFolder As String
Dim strADOFile As String
Dim strADOPathFromINI As String
Dim arrADOFiles
Const strINIPath As String = "C:\test.ini"
strADOPathFromINI = ReadINIValue(strINIPath, _
"Add-in behaviour", _
"Full path to ADO library")
If InStr(1, strADOPathFromINI, ":\", vbBinaryCompare) > 0 Then
If AddReferenceFromFile(strADOPathFromINI) = True Then
Exit Sub
End If
End If
strADOFolder = Left$(Application.Path, 1) & _
":\Program Files\Common Files\System\ADO\"
Set ADOConn = CreateObject("ADODB.Connection")
strADOVersion = Left$(ADOConn.Version, 3)
Set ADOConn = Nothing
Select Case strADOVersion
Case "2.8"
strADOFile = "msado15.dll"
Case "2.7"
strADOFile = "msado27.tlb"
Case "2.6"
strADOFile = "msado26.tlb"
Case "2.5"
strADOFile = "msado25.tlb"
Case "2.1"
strADOFile = "msado21.tlb"
Case "2.0"
strADOFile = "msado20.tlb"
End Select
If AddReferenceFromFile(strADOFolder & strADOFile) = True Then
Exit Sub
End If
arrADOFiles = Array("msado15.dll", "msado27.tlb", "msado26.tlb", _
"msado25.tlb", "msado21.tlb", "msado20.tlb")
For i = 0 To 5
If AddReferenceFromFile(strADOFolder & arrADOFiles(i)) = True Then
Exit Sub
End If
Next
MsgBox "Failed to add the ADO reference" & vbCrLf & vbCrLf & _
"Please contact Bart Smissaert: (e-mail address removed)", _
vbExclamation, "adding ADO reference"
End Sub
Sofar this seems to work fine. I know it is overkill, but as this is so
tricky I can't be
careful enough. The .ini file read should always make it possible for the
user to
set the right path, but this can be skipped for starters.
I am not 100% sure the Select Case sequence is right, but then there always
is the brute force
method with the array.
Have read a lot of postings about this problem, but there doesn't seem to be
any definite, single best
way how to tackle this.
Any pitfalls here or any suggestions for improvement?
RBS