Foolproof way to add ADO reference

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: [email protected]", _
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

Why not just using late binding?


Dim oConn As Object
Dim oRS As Object

Set oConn = CreateObject("ADODB.Connection")

Set oRS = CreateObject("ADODB.Recordset")


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
> 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: [email protected]",
_
> 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
>
>
>
>
>

What Bob recommends is foolproof but note that there is a slight performance
hit when you do it this way. That is the tradeoff you make for doing it this
way.

The other issue is that you loose intellisence when you are writing the
code. My preference when doing it this way is to reference the ADO object and
write all of my dim statements referencing the actual ADO objects. Once the
code is working then remove the reference and change the dim statements to
use the late binding Bob listed. Maybe it is just me but I find it difficult
to program without intelliesence...
--
HTH...

Jim Thomlinson


"Bob Phillips" wrote:

> Why not just using late binding?
>
>
> Dim oConn As Object
> Dim oRS As Object
>
> Set oConn = CreateObject("ADODB.Connection")
>
> Set oRS = CreateObject("ADODB.Recordset")
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "RB Smissaert" <[email protected]> wrote in message
> news:[email protected]...
> > 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: [email protected]",
> _
> > 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
> >
> >
> >
> >
> >
>
>
>

I don't want to use late binding for 3 reasons:
No intelli-sense
Some performance penalty?
As this is a very large .xla a lot of code re-writing.

RBS

"Bob Phillips" <[email protected]> wrote in message
news:%[email protected]...
> Why not just using late binding?
>
>
> Dim oConn As Object
> Dim oRS As Object
>
> Set oConn = CreateObject("ADODB.Connection")
>
> Set oRS = CreateObject("ADODB.Recordset")
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "RB Smissaert" <[email protected]> wrote in message
> news:[email protected]...
>> 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:
>> [email protected]",
> _
>> 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
>>
>>
>>
>>
>>
>
>

Pick the lowest version of ADO that you have on your system and save it using
that reference. Your code should still run and most people will have version
2.5 or better. Otherwise you are stuck with the re-write... The intellisence
is pretty easy to work around so the only real issue is the performance hit
which is probably not substantial enough to be a show stopper...
--
HTH...

Jim Thomlinson


"RB Smissaert" wrote:

> I don't want to use late binding for 3 reasons:
> No intelli-sense
> Some performance penalty?
> As this is a very large .xla a lot of code re-writing.
>
> RBS
>
> "Bob Phillips" <[email protected]> wrote in message
> news:%[email protected]...
> > Why not just using late binding?
> >
> >
> > Dim oConn As Object
> > Dim oRS As Object
> >
> > Set oConn = CreateObject("ADODB.Connection")
> >
> > Set oRS = CreateObject("ADODB.Recordset")
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "RB Smissaert" <[email protected]> wrote in message
> > news:[email protected]...
> >> 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:
> >> [email protected]",
> > _
> >> 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
> >>
> >>
> >>
> >>
> >>
> >
> >
>
>

One drawback of that is that if I save with say 2.1 and the user has 2.1 and
2.8 on the system it will stick with 2.1.
Not sure if there is much harm in that, but maybe 2.8 has better
performance.
What is wrong with the posted code?

RBS

"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Pick the lowest version of ADO that you have on your system and save it
> using
> that reference. Your code should still run and most people will have
> version
> 2.5 or better. Otherwise you are stuck with the re-write... The
> intellisence
> is pretty easy to work around so the only real issue is the performance
> hit
> which is probably not substantial enough to be a show stopper...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "RB Smissaert" wrote:
>
>> I don't want to use late binding for 3 reasons:
>> No intelli-sense
>> Some performance penalty?
>> As this is a very large .xla a lot of code re-writing.
>>
>> RBS
>>
>> "Bob Phillips" <[email protected]> wrote in message
>> news:%[email protected]...
>> > Why not just using late binding?
>> >
>> >
>> > Dim oConn As Object
>> > Dim oRS As Object
>> >
>> > Set oConn = CreateObject("ADODB.Connection")
>> >
>> > Set oRS = CreateObject("ADODB.Recordset")
>> >
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (replace somewhere in email address with gmail if mailing direct)
>> >
>> > "RB Smissaert" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> 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:
>> >> [email protected]",
>> > _
>> >> 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
>> >>
>> >>
>> >>
>> >>
>> >>
>> >
>> >
>>
>>

Hi Bart,

Your add ado ref routine both worked and failed getting your error message.
Somehow it managed to add the ref to v2.8 though as far as I know this
version is not correctly registered on my system. It doesn't normally appear
in tools> ref's and things have gone wrong in the past using other people's
wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which
for some reason I find more reliable.

As I say it added the 2.8 ref but when I did this

' r = a vba ref to v2.8
Debug.Print r.Name, r.Description, r.Major, r.Minor
ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8

Debug.Print r.FullPath
' this fails !!
debug.print err, err.description
-2147319779 Method 'FullPath' of object 'Reference' failed

If I change the ref to v2.7 both debug lines work
ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
C:\Program Files\Common Files\System\ado\msado27.tlb

Maybe something similar to your user.

I have two versions of msado15.dll on my system, one an old v1.5 and the
newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the
other versions in your list.

FWIW I recall having problems when trying to upgrade to 2.8, it was a while
ago.

Regards,
Peter T

PS I commented out your code to get file name from an ini and did -
strADOPathFromINI = ThisWorkbook.Name

"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
> 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: [email protected]",
_
> 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
>
>
>
>
>

Hi Peter,

Thanks for the reply, but I don't get it quite, other than that you say
avoid 2.8.
You are talking about the reference object, but I haven't used that in
that code.
Correct me if I misunderstood.
Doing this from work, so maybe I didn't look properly.

RBS


Peter T wrote:
> Hi Bart,
>
> Your add ado ref routine both worked and failed getting your error message.
> Somehow it managed to add the ref to v2.8 though as far as I know this
> version is not correctly registered on my system. It doesn't normally appear
> in tools> ref's and things have gone wrong in the past using other people's
> wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which
> for some reason I find more reliable.
>
> As I say it added the 2.8 ref but when I did this
>
> ' r = a vba ref to v2.8
> Debug.Print r.Name, r.Description, r.Major, r.Minor
> ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
>
> Debug.Print r.FullPath
> ' this fails !!
> debug.print err, err.description
> -2147319779 Method 'FullPath' of object 'Reference' failed
>
> If I change the ref to v2.7 both debug lines work
> ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
> C:\Program Files\Common Files\System\ado\msado27.tlb
>
> Maybe something similar to your user.
>
> I have two versions of msado15.dll on my system, one an old v1.5 and the
> newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the
> other versions in your list.
>
> FWIW I recall having problems when trying to upgrade to 2.8, it was a while
> ago.
>
> Regards,
> Peter T
>
> PS I commented out your code to get file name from an ini and did -
> strADOPathFromINI = ThisWorkbook.Name
>
> "RB Smissaert" <[email protected]> wrote in message
> news:[email protected]...
> > 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: [email protected]",
> _
> > 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
> >
> >
> >
> >
> >

Peter,

Just noticed that the function misses this line at the end:

AddReferenceFromFile = True

Before Exit Function

Try again with that added.

RBS


Peter T wrote:
> Hi Bart,
>
> Your add ado ref routine both worked and failed getting your error message.
> Somehow it managed to add the ref to v2.8 though as far as I know this
> version is not correctly registered on my system. It doesn't normally appear
> in tools> ref's and things have gone wrong in the past using other people's
> wb's with this ref. So I always end up changing to 2.7 or rather 2.5 which
> for some reason I find more reliable.
>
> As I say it added the 2.8 ref but when I did this
>
> ' r = a vba ref to v2.8
> Debug.Print r.Name, r.Description, r.Major, r.Minor
> ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
>
> Debug.Print r.FullPath
> ' this fails !!
> debug.print err, err.description
> -2147319779 Method 'FullPath' of object 'Reference' failed
>
> If I change the ref to v2.7 both debug lines work
> ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
> C:\Program Files\Common Files\System\ado\msado27.tlb
>
> Maybe something similar to your user.
>
> I have two versions of msado15.dll on my system, one an old v1.5 and the
> newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all the
> other versions in your list.
>
> FWIW I recall having problems when trying to upgrade to 2.8, it was a while
> ago.
>
> Regards,
> Peter T
>
> PS I commented out your code to get file name from an ini and did -
> strADOPathFromINI = ThisWorkbook.Name
>
> "RB Smissaert" <[email protected]> wrote in message
> news:[email protected]...
> > 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: [email protected]",
> _
> > 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
> >
> >
> >
> >
> >

Hi Bart,

Yes I had noticed (later) the function didn't return True on success.
However that doesn't change what I reported earlier about v2.8 not working
correctly in my system. Trying to debug its Fullpath (after setting the ref)
errors.

In my XL2000 looking at tools > ref's v2.8 is checked and looks correct (but
I know it will cause problems).
I ran same code in XL97, v2.8 is also checked but marked MISSING.

Regards,
Peter


<[email protected]> wrote in message
news:[email protected]...
> Peter,
>
> Just noticed that the function misses this line at the end:
>
> AddReferenceFromFile = True
>
> Before Exit Function
>
> Try again with that added.
>
> RBS
>
>
> Peter T wrote:
> > Hi Bart,
> >
> > Your add ado ref routine both worked and failed getting your error
message.
> > Somehow it managed to add the ref to v2.8 though as far as I know this
> > version is not correctly registered on my system. It doesn't normally
appear
> > in tools> ref's and things have gone wrong in the past using other
people's
> > wb's with this ref. So I always end up changing to 2.7 or rather 2.5
which
> > for some reason I find more reliable.
> >
> > As I say it added the 2.8 ref but when I did this
> >
> > ' r = a vba ref to v2.8
> > Debug.Print r.Name, r.Description, r.Major, r.Minor
> > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
> >
> > Debug.Print r.FullPath
> > ' this fails !!
> > debug.print err, err.description
> > -2147319779 Method 'FullPath' of object 'Reference' failed
> >
> > If I change the ref to v2.7 both debug lines work
> > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
> > C:\Program Files\Common Files\System\ado\msado27.tlb
> >
> > Maybe something similar to your user.
> >
> > I have two versions of msado15.dll on my system, one an old v1.5 and the
> > newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all
the
> > other versions in your list.
> >
> > FWIW I recall having problems when trying to upgrade to 2.8, it was a
while
> > ago.
> >
> > Regards,
> > Peter T
> >
> > PS I commented out your code to get file name from an ini and did -
> > strADOPathFromINI = ThisWorkbook.Name
> >
> > "RB Smissaert" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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:
[email protected]",
> > _
> > > 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
> > >
> > >
> > >
> > >
> > >
>

Hi Bart,

Yes I had noticed (later) the function didn't return True on success.
However that doesn't change what I reported earlier about v2.8 not working
correctly in my system. Trying to debug its Fullpath (after setting the ref)
errors.

In my XL2000 looking at tools > ref's v2.8 is checked and looks correct (but
I know it will cause problems).
I ran same code in XL97, v2.8 is also checked but marked MISSING.

Regards,
Peter


<[email protected]> wrote in message
news:[email protected]...
> Peter,
>
> Just noticed that the function misses this line at the end:
>
> AddReferenceFromFile = True
>
> Before Exit Function
>
> Try again with that added.
>
> RBS
>
>
> Peter T wrote:
> > Hi Bart,
> >
> > Your add ado ref routine both worked and failed getting your error
message.
> > Somehow it managed to add the ref to v2.8 though as far as I know this
> > version is not correctly registered on my system. It doesn't normally
appear
> > in tools> ref's and things have gone wrong in the past using other
people's
> > wb's with this ref. So I always end up changing to 2.7 or rather 2.5
which
> > for some reason I find more reliable.
> >
> > As I say it added the 2.8 ref but when I did this
> >
> > ' r = a vba ref to v2.8
> > Debug.Print r.Name, r.Description, r.Major, r.Minor
> > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
> >
> > Debug.Print r.FullPath
> > ' this fails !!
> > debug.print err, err.description
> > -2147319779 Method 'FullPath' of object 'Reference' failed
> >
> > If I change the ref to v2.7 both debug lines work
> > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
> > C:\Program Files\Common Files\System\ado\msado27.tlb
> >
> > Maybe something similar to your user.
> >
> > I have two versions of msado15.dll on my system, one an old v1.5 and the
> > newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all
the
> > other versions in your list.
> >
> > FWIW I recall having problems when trying to upgrade to 2.8, it was a
while
> > ago.
> >
> > Regards,
> > Peter T
> >
> > PS I commented out your code to get file name from an ini and did -
> > strADOPathFromINI = ThisWorkbook.Name
> >
> > "RB Smissaert" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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:
[email protected]",
> > _
> > > 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
> > >
> > >
> > >
> > >
> > >
>

Hi Peter,

My customers ( > 100 ) have been using this routine now for a week and
sofar
I haven't heard about any problems. Are you suggesting I should leave
the 2.8
option out?

RBS

Peter T wrote:
> Hi Bart,
>
> Yes I had noticed (later) the function didn't return True on success.
> However that doesn't change what I reported earlier about v2.8 not working
> correctly in my system. Trying to debug its Fullpath (after setting the ref)
> errors.
>
> In my XL2000 looking at tools > ref's v2.8 is checked and looks correct (but
> I know it will cause problems).
> I ran same code in XL97, v2.8 is also checked but marked MISSING.
>
> Regards,
> Peter
>
>
> <[email protected]> wrote in message
> news:[email protected]...
> > Peter,
> >
> > Just noticed that the function misses this line at the end:
> >
> > AddReferenceFromFile = True
> >
> > Before Exit Function
> >
> > Try again with that added.
> >
> > RBS
> >
> >
> > Peter T wrote:
> > > Hi Bart,
> > >
> > > Your add ado ref routine both worked and failed getting your error
> message.
> > > Somehow it managed to add the ref to v2.8 though as far as I know this
> > > version is not correctly registered on my system. It doesn't normally
> appear
> > > in tools> ref's and things have gone wrong in the past using other
> people's
> > > wb's with this ref. So I always end up changing to 2.7 or rather 2.5
> which
> > > for some reason I find more reliable.
> > >
> > > As I say it added the 2.8 ref but when I did this
> > >
> > > ' r = a vba ref to v2.8
> > > Debug.Print r.Name, r.Description, r.Major, r.Minor
> > > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
> > >
> > > Debug.Print r.FullPath
> > > ' this fails !!
> > > debug.print err, err.description
> > > -2147319779 Method 'FullPath' of object 'Reference' failed
> > >
> > > If I change the ref to v2.7 both debug lines work
> > > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
> > > C:\Program Files\Common Files\System\ado\msado27.tlb
> > >
> > > Maybe something similar to your user.
> > >
> > > I have two versions of msado15.dll on my system, one an old v1.5 and the
> > > newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have all
> the
> > > other versions in your list.
> > >
> > > FWIW I recall having problems when trying to upgrade to 2.8, it was a
> while
> > > ago.
> > >
> > > Regards,
> > > Peter T
> > >
> > > PS I commented out your code to get file name from an ini and did -
> > > strADOPathFromINI = ThisWorkbook.Name
> > >
> > > "RB Smissaert" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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:
> [email protected]",
> > > _
> > > > 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
> > > >
> > > >
> > > >
> > > >
> > > >
> >

Hi Bart,

I wouldn't want to suggest you leave out 2.8, not sure what the problem is
with it in my system and probably unusual. No idea if it's relates to the
reason your user fails.

I modified your function and it succeeded it correctly adding v2.7 after
first adding the problematic 2.8.

Function AddReferenceFromFile( blah
Dim oRef As Object ' Reference
' code

On Error Resume Next
Set oRef = VBProj.References.AddFromFile(strFilePath)

' AddReferenceFromFile = True
AddReferenceFromFile = Len(oRef.fullPath) > 0

If Err.Number Then
If Not oRef Is Nothing Then
VBProj.References.Remove oRef ' this removed my 2.8
End If
Else
AddReferenceFromFile = True
End If

Regards,
Peter T

<[email protected]> wrote in message
news:[email protected]...
> Hi Peter,
>
> My customers ( > 100 ) have been using this routine now for a week and
> sofar
> I haven't heard about any problems. Are you suggesting I should leave
> the 2.8
> option out?
>
> RBS
>
> Peter T wrote:
> > Hi Bart,
> >
> > Yes I had noticed (later) the function didn't return True on success.
> > However that doesn't change what I reported earlier about v2.8 not
working
> > correctly in my system. Trying to debug its Fullpath (after setting the
ref)
> > errors.
> >
> > In my XL2000 looking at tools > ref's v2.8 is checked and looks correct
(but
> > I know it will cause problems).
> > I ran same code in XL97, v2.8 is also checked but marked MISSING.
> >
> > Regards,
> > Peter
> >
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> > > Peter,
> > >
> > > Just noticed that the function misses this line at the end:
> > >
> > > AddReferenceFromFile = True
> > >
> > > Before Exit Function
> > >
> > > Try again with that added.
> > >
> > > RBS
> > >
> > >
> > > Peter T wrote:
> > > > Hi Bart,
> > > >
> > > > Your add ado ref routine both worked and failed getting your error
> > message.
> > > > Somehow it managed to add the ref to v2.8 though as far as I know
this
> > > > version is not correctly registered on my system. It doesn't
normally
> > appear
> > > > in tools> ref's and things have gone wrong in the past using other
> > people's
> > > > wb's with this ref. So I always end up changing to 2.7 or rather 2.5
> > which
> > > > for some reason I find more reliable.
> > > >
> > > > As I say it added the 2.8 ref but when I did this
> > > >
> > > > ' r = a vba ref to v2.8
> > > > Debug.Print r.Name, r.Description, r.Major, r.Minor
> > > > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
> > > >
> > > > Debug.Print r.FullPath
> > > > ' this fails !!
> > > > debug.print err, err.description
> > > > -2147319779 Method 'FullPath' of object 'Reference' failed
> > > >
> > > > If I change the ref to v2.7 both debug lines work
> > > > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
> > > > C:\Program Files\Common Files\System\ado\msado27.tlb
> > > >
> > > > Maybe something similar to your user.
> > > >
> > > > I have two versions of msado15.dll on my system, one an old v1.5 and
the
> > > > newer 2.8 (why is it the 2.8 version named msado15.dll ! I also have
all
> > the
> > > > other versions in your list.
> > > >
> > > > FWIW I recall having problems when trying to upgrade to 2.8, it was
a
> > while
> > > > ago.
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > > PS I commented out your code to get file name from an ini and did -
> > > > strADOPathFromINI = ThisWorkbook.Name
> > > >
> > > > "RB Smissaert" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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:
> > [email protected]",
> > > > _
> > > > > 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
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > >
>

Hi Peter,

OK, I got you.
You are trying to test for a reference that was set, but that somehow is not
right.
I think that makes sense, and maybe the way you do it is a good way.
I always have the option though if things don't work out to set the path to
the right file
via my .ini.
Still, it is nice if such a thing could be avoided as it would involve
contacting me etc.
What are you using ADO for?

RBS


"Peter T" <peter_t@discussions> wrote in message
news:%[email protected]...
> Hi Bart,
>
> I wouldn't want to suggest you leave out 2.8, not sure what the problem is
> with it in my system and probably unusual. No idea if it's relates to the
> reason your user fails.
>
> I modified your function and it succeeded it correctly adding v2.7 after
> first adding the problematic 2.8.
>
> Function AddReferenceFromFile( blah
> Dim oRef As Object ' Reference
> ' code
>
> On Error Resume Next
> Set oRef = VBProj.References.AddFromFile(strFilePath)
>
> ' AddReferenceFromFile = True
> AddReferenceFromFile = Len(oRef.fullPath) > 0
>
> If Err.Number Then
> If Not oRef Is Nothing Then
> VBProj.References.Remove oRef ' this removed my 2.8
> End If
> Else
> AddReferenceFromFile = True
> End If
>
> Regards,
> Peter T
>
> <[email protected]> wrote in message
> news:[email protected]...
>> Hi Peter,
>>
>> My customers ( > 100 ) have been using this routine now for a week and
>> sofar
>> I haven't heard about any problems. Are you suggesting I should leave
>> the 2.8
>> option out?
>>
>> RBS
>>
>> Peter T wrote:
>> > Hi Bart,
>> >
>> > Yes I had noticed (later) the function didn't return True on success.
>> > However that doesn't change what I reported earlier about v2.8 not
> working
>> > correctly in my system. Trying to debug its Fullpath (after setting the
> ref)
>> > errors.
>> >
>> > In my XL2000 looking at tools > ref's v2.8 is checked and looks correct
> (but
>> > I know it will cause problems).
>> > I ran same code in XL97, v2.8 is also checked but marked MISSING.
>> >
>> > Regards,
>> > Peter
>> >
>> >
>> > <[email protected]> wrote in message
>> > news:[email protected]...
>> > > Peter,
>> > >
>> > > Just noticed that the function misses this line at the end:
>> > >
>> > > AddReferenceFromFile = True
>> > >
>> > > Before Exit Function
>> > >
>> > > Try again with that added.
>> > >
>> > > RBS
>> > >
>> > >
>> > > Peter T wrote:
>> > > > Hi Bart,
>> > > >
>> > > > Your add ado ref routine both worked and failed getting your error
>> > message.
>> > > > Somehow it managed to add the ref to v2.8 though as far as I know
> this
>> > > > version is not correctly registered on my system. It doesn't
> normally
>> > appear
>> > > > in tools> ref's and things have gone wrong in the past using other
>> > people's
>> > > > wb's with this ref. So I always end up changing to 2.7 or rather
>> > > > 2.5
>> > which
>> > > > for some reason I find more reliable.
>> > > >
>> > > > As I say it added the 2.8 ref but when I did this
>> > > >
>> > > > ' r = a vba ref to v2.8
>> > > > Debug.Print r.Name, r.Description, r.Major, r.Minor
>> > > > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
>> > > >
>> > > > Debug.Print r.FullPath
>> > > > ' this fails !!
>> > > > debug.print err, err.description
>> > > > -2147319779 Method 'FullPath' of object 'Reference' failed
>> > > >
>> > > > If I change the ref to v2.7 both debug lines work
>> > > > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
>> > > > C:\Program Files\Common Files\System\ado\msado27.tlb
>> > > >
>> > > > Maybe something similar to your user.
>> > > >
>> > > > I have two versions of msado15.dll on my system, one an old v1.5
>> > > > and
> the
>> > > > newer 2.8 (why is it the 2.8 version named msado15.dll ! I also
>> > > > have
> all
>> > the
>> > > > other versions in your list.
>> > > >
>> > > > FWIW I recall having problems when trying to upgrade to 2.8, it was
> a
>> > while
>> > > > ago.
>> > > >
>> > > > Regards,
>> > > > Peter T
>> > > >
>> > > > PS I commented out your code to get file name from an ini and did -
>> > > > strADOPathFromINI = ThisWorkbook.Name
>> > > >
>> > > > "RB Smissaert" <[email protected]> wrote in message
>> > > > news:[email protected]...
>> > > > > 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:
>> > [email protected]",
>> > > > _
>> > > > > 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
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > >
>>
>
>

Peter,

Can you add that ADO reference via the GUID and will it be faulty despite
setting
the reference successfully?
I am talking about the problematic 2.8 here.

RBS


"Peter T" <peter_t@discussions> wrote in message
news:%[email protected]...
> Hi Bart,
>
> I wouldn't want to suggest you leave out 2.8, not sure what the problem is
> with it in my system and probably unusual. No idea if it's relates to the
> reason your user fails.
>
> I modified your function and it succeeded it correctly adding v2.7 after
> first adding the problematic 2.8.
>
> Function AddReferenceFromFile( blah
> Dim oRef As Object ' Reference
> ' code
>
> On Error Resume Next
> Set oRef = VBProj.References.AddFromFile(strFilePath)
>
> ' AddReferenceFromFile = True
> AddReferenceFromFile = Len(oRef.fullPath) > 0
>
> If Err.Number Then
> If Not oRef Is Nothing Then
> VBProj.References.Remove oRef ' this removed my 2.8
> End If
> Else
> AddReferenceFromFile = True
> End If
>
> Regards,
> Peter T
>
> <[email protected]> wrote in message
> news:[email protected]...
>> Hi Peter,
>>
>> My customers ( > 100 ) have been using this routine now for a week and
>> sofar
>> I haven't heard about any problems. Are you suggesting I should leave
>> the 2.8
>> option out?
>>
>> RBS
>>
>> Peter T wrote:
>> > Hi Bart,
>> >
>> > Yes I had noticed (later) the function didn't return True on success.
>> > However that doesn't change what I reported earlier about v2.8 not
> working
>> > correctly in my system. Trying to debug its Fullpath (after setting the
> ref)
>> > errors.
>> >
>> > In my XL2000 looking at tools > ref's v2.8 is checked and looks correct
> (but
>> > I know it will cause problems).
>> > I ran same code in XL97, v2.8 is also checked but marked MISSING.
>> >
>> > Regards,
>> > Peter
>> >
>> >
>> > <[email protected]> wrote in message
>> > news:[email protected]...
>> > > Peter,
>> > >
>> > > Just noticed that the function misses this line at the end:
>> > >
>> > > AddReferenceFromFile = True
>> > >
>> > > Before Exit Function
>> > >
>> > > Try again with that added.
>> > >
>> > > RBS
>> > >
>> > >
>> > > Peter T wrote:
>> > > > Hi Bart,
>> > > >
>> > > > Your add ado ref routine both worked and failed getting your error
>> > message.
>> > > > Somehow it managed to add the ref to v2.8 though as far as I know
> this
>> > > > version is not correctly registered on my system. It doesn't
> normally
>> > appear
>> > > > in tools> ref's and things have gone wrong in the past using other
>> > people's
>> > > > wb's with this ref. So I always end up changing to 2.7 or rather
>> > > > 2.5
>> > which
>> > > > for some reason I find more reliable.
>> > > >
>> > > > As I say it added the 2.8 ref but when I did this
>> > > >
>> > > > ' r = a vba ref to v2.8
>> > > > Debug.Print r.Name, r.Description, r.Major, r.Minor
>> > > > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
>> > > >
>> > > > Debug.Print r.FullPath
>> > > > ' this fails !!
>> > > > debug.print err, err.description
>> > > > -2147319779 Method 'FullPath' of object 'Reference' failed
>> > > >
>> > > > If I change the ref to v2.7 both debug lines work
>> > > > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
>> > > > C:\Program Files\Common Files\System\ado\msado27.tlb
>> > > >
>> > > > Maybe something similar to your user.
>> > > >
>> > > > I have two versions of msado15.dll on my system, one an old v1.5
>> > > > and
> the
>> > > > newer 2.8 (why is it the 2.8 version named msado15.dll ! I also
>> > > > have
> all
>> > the
>> > > > other versions in your list.
>> > > >
>> > > > FWIW I recall having problems when trying to upgrade to 2.8, it was
> a
>> > while
>> > > > ago.
>> > > >
>> > > > Regards,
>> > > > Peter T
>> > > >
>> > > > PS I commented out your code to get file name from an ini and did -
>> > > > strADOPathFromINI = ThisWorkbook.Name
>> > > >
>> > > > "RB Smissaert" <[email protected]> wrote in message
>> > > > news:[email protected]...
>> > > > > 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:
>> > [email protected]",
>> > > > _
>> > > > > 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
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > >
>>
>
>

Hi Bart,

Although I already knew my 2.8 is problematic it's only today I've found out
trying to return it's path throws the error. Not sure why I can return other
info' about it, such as Name, Description, Major, Minor etc. So in your
routine need to remove the 'bad' ref before trying lower versions.

Which reminds me, if I (anyone) runs your routine twice, the second time
user will get the error message because trying to add the ref when one
already exists throws the error. I suppose that shouldn't occur because you
set a flag in your ini, but...?

> What are you using ADO for?

Not much !

Regards,
Peter T

"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
> Hi Peter,
>
> OK, I got you.
> You are trying to test for a reference that was set, but that somehow is
not
> right.
> I think that makes sense, and maybe the way you do it is a good way.
> I always have the option though if things don't work out to set the path
to
> the right file
> via my .ini.
> Still, it is nice if such a thing could be avoided as it would involve
> contacting me etc.
> What are you using ADO for?
>
> RBS
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%[email protected]...
> > Hi Bart,
> >
> > I wouldn't want to suggest you leave out 2.8, not sure what the problem
is
> > with it in my system and probably unusual. No idea if it's relates to
the
> > reason your user fails.
> >
> > I modified your function and it succeeded it correctly adding v2.7 after
> > first adding the problematic 2.8.
> >
> > Function AddReferenceFromFile( blah
> > Dim oRef As Object ' Reference
> > ' code
> >
> > On Error Resume Next
> > Set oRef = VBProj.References.AddFromFile(strFilePath)
> >
> > ' AddReferenceFromFile = True
> > AddReferenceFromFile = Len(oRef.fullPath) > 0
> >
> > If Err.Number Then
> > If Not oRef Is Nothing Then
> > VBProj.References.Remove oRef ' this removed my 2.8
> > End If
> > Else
> > AddReferenceFromFile = True
> > End If
> >
> > Regards,
> > Peter T
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi Peter,
> >>
> >> My customers ( > 100 ) have been using this routine now for a week and
> >> sofar
> >> I haven't heard about any problems. Are you suggesting I should leave
> >> the 2.8
> >> option out?
> >>
> >> RBS
> >>
> >> Peter T wrote:
> >> > Hi Bart,
> >> >
> >> > Yes I had noticed (later) the function didn't return True on success.
> >> > However that doesn't change what I reported earlier about v2.8 not
> > working
> >> > correctly in my system. Trying to debug its Fullpath (after setting
the
> > ref)
> >> > errors.
> >> >
> >> > In my XL2000 looking at tools > ref's v2.8 is checked and looks
correct
> > (but
> >> > I know it will cause problems).
> >> > I ran same code in XL97, v2.8 is also checked but marked MISSING.
> >> >
> >> > Regards,
> >> > Peter
> >> >
> >> >
> >> > <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > > Peter,
> >> > >
> >> > > Just noticed that the function misses this line at the end:
> >> > >
> >> > > AddReferenceFromFile = True
> >> > >
> >> > > Before Exit Function
> >> > >
> >> > > Try again with that added.
> >> > >
> >> > > RBS
> >> > >
> >> > >
> >> > > Peter T wrote:
> >> > > > Hi Bart,
> >> > > >
> >> > > > Your add ado ref routine both worked and failed getting your
error
> >> > message.
> >> > > > Somehow it managed to add the ref to v2.8 though as far as I know
> > this
> >> > > > version is not correctly registered on my system. It doesn't
> > normally
> >> > appear
> >> > > > in tools> ref's and things have gone wrong in the past using
other
> >> > people's
> >> > > > wb's with this ref. So I always end up changing to 2.7 or rather
> >> > > > 2.5
> >> > which
> >> > > > for some reason I find more reliable.
> >> > > >
> >> > > > As I say it added the 2.8 ref but when I did this
> >> > > >
> >> > > > ' r = a vba ref to v2.8
> >> > > > Debug.Print r.Name, r.Description, r.Major, r.Minor
> >> > > > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
> >> > > >
> >> > > > Debug.Print r.FullPath
> >> > > > ' this fails !!
> >> > > > debug.print err, err.description
> >> > > > -2147319779 Method 'FullPath' of object 'Reference' failed
> >> > > >
> >> > > > If I change the ref to v2.7 both debug lines work
> >> > > > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
> >> > > > C:\Program Files\Common Files\System\ado\msado27.tlb
> >> > > >
> >> > > > Maybe something similar to your user.
> >> > > >
> >> > > > I have two versions of msado15.dll on my system, one an old v1.5
> >> > > > and
> > the
> >> > > > newer 2.8 (why is it the 2.8 version named msado15.dll ! I also
> >> > > > have
> > all
> >> > the
> >> > > > other versions in your list.
> >> > > >
> >> > > > FWIW I recall having problems when trying to upgrade to 2.8, it
was
> > a
> >> > while
> >> > > > ago.
> >> > > >
> >> > > > Regards,
> >> > > > Peter T
> >> > > >
> >> > > > PS I commented out your code to get file name from an ini and
did -
> >> > > > strADOPathFromINI = ThisWorkbook.Name
> >> > > >
> >> > > > "RB Smissaert" <[email protected]> wrote in message
> >> > > > news:[email protected]...
> >> > > > > 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:
> >> > [email protected]",
> >> > > > _
> >> > > > > 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
> >> > > > >
> >> > > > >
> >> > > > >
> >> > > > >
> >> > > > >
> >> > >
> >>
> >
> >
>

I have added your find (ref successfully added, but faulty as .FullPath
doesn't work) now to my function.
Looks this is a bit more foolproof then, but I am interested if anybody can
still see holes in this.

Function AddProjectReference(Optional strGUID As String, _
Optional lMajor As Long, _
Optional lMinor As Long, _
Optional strRefName As String = "", _
Optional bRemove As Boolean, _
Optional bRemoveAndAdd As Boolean, _
Optional bArray As Boolean, _
Optional vGUIDArray As Variant, _
Optional strObjectString As String, _
Optional strWorkbook As String, _
Optional strFilePath As String, _
Optional bMessage As Boolean = True) As Boolean

Dim oRef As Object
Dim VBProj As VBProject
Dim i As Byte
Dim bSuccess As Boolean

If Len(strWorkbook) = 0 Then
strWorkbook = ThisWorkbook.Name
End If

Set VBProj = Workbooks(strWorkbook).VBProject

'removing references
'-------------------
For Each oRef In VBProj.References
If oRef.Name = strRefName Then
If oRef.IsBroken Then
'so remove any broken references
'-------------------------------
VBProj.References.REMOVE oRef
Else
If bRemove Or bRemoveAndAdd Then
VBProj.References.REMOVE oRef
If bRemove Then
AddProjectReference = True
End If
End If
End If
End If
Next

If bRemove Then
Exit Function
End If

'adding references
'-----------------
If Len(strFilePath) = 0 Then
'not adding directly from file
'-----------------------------
If Len(strObjectString) = 0 Then
If bArray Then 'trying an array of GUID's and version numbers
On Error Resume Next
For i = 1 To UBound(vGUIDArray)
Set oRef = VBProj.References.AddFromGuid(GUID:=vGUIDArray(i,
1), _
Major:=vGUIDArray(i,
2), _
Minor:=vGUIDArray(i,
3))
If Err.Number = 0 Then
bSuccess = Len(oRef.FullPath) > 0
If bSuccess Then
AddProjectReference = True
WriteIniValue strINIPath, _
"Add-in behaviour", _
"ADO reference added", _
vGUIDArray(i, 2) & "." & vGUIDArray(i, 3)
Exit Function
Else
VBProj.References.REMOVE oRef
End If
End If
Next
GoTo ERROROUT 'as we couldn't add any of the references
Else
On Error Resume Next
Set oRef = VBProj.References.AddFromGuid(GUID:=strGUID, _
Major:=lMajor, _
Minor:=lMinor)
If Err.Number = 0 Then
bSuccess = Len(oRef.FullPath) > 0 'just for in case the len
is 0 without error
If bSuccess Then
AddProjectReference = True 'as we got here without an
error
Else
VBProj.References.REMOVE oRef
GoTo ERROROUT
End If
End If
End If
Else
'adding from file via registry reads
'-----------------------------------
On Error GoTo ERROROUT
AddProjectReference = AddRefFromFileWithRegReads(strObjectString,
strWorkbook)
End If
Else
'adding directly from file
'-------------------------
On Error Resume Next
If bFileExistsVBA(strFilePath) Then
Set oRef = VBProj.References.AddFromFile(strFilePath)
If Err.Number = 0 Then
bSuccess = Len(oRef.FullPath) > 0
If bSuccess Then
AddProjectReference = True
Else
VBProj.References.REMOVE oRef
GoTo ERROROUT
End If
End If
Else
If bMessage Then
MsgBox "Couldn't add the " & strRefName & " reference as the
file:" & _
vbCrLf & _
strFilePath & vbCrLf & _
"is missing." & vbCrLf & vbCrLf & _
"Run the installer on this PC", vbExclamation, _
"adding " & strRefName & " reference"
End If
End If
End If

If bRemove = False Then
WriteIniValue strINIPath, _
"Add-in behaviour", _
"Added " & strRefName & " library file path", _
oRef.FullPath
End If

Exit Function
ERROROUT:

If bMessage Then
MsgBox "Couldn't add the " & strRefName & " reference", , _
"adding references to VB Project"
End If
On Error GoTo 0

End Function


It can't work without the other functions, but you will get the idea.
Thanks for that suggestion, it may be useful.


RBS

"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> Hi Bart,
>
> Although I already knew my 2.8 is problematic it's only today I've found
> out
> trying to return it's path throws the error. Not sure why I can return
> other
> info' about it, such as Name, Description, Major, Minor etc. So in your
> routine need to remove the 'bad' ref before trying lower versions.
>
> Which reminds me, if I (anyone) runs your routine twice, the second time
> user will get the error message because trying to add the ref when one
> already exists throws the error. I suppose that shouldn't occur because
> you
> set a flag in your ini, but...?
>
>> What are you using ADO for?
>
> Not much !
>
> Regards,
> Peter T
>
> "RB Smissaert" <[email protected]> wrote in message
> news:[email protected]...
>> Hi Peter,
>>
>> OK, I got you.
>> You are trying to test for a reference that was set, but that somehow is
> not
>> right.
>> I think that makes sense, and maybe the way you do it is a good way.
>> I always have the option though if things don't work out to set the path
> to
>> the right file
>> via my .ini.
>> Still, it is nice if such a thing could be avoided as it would involve
>> contacting me etc.
>> What are you using ADO for?
>>
>> RBS
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:%[email protected]...
>> > Hi Bart,
>> >
>> > I wouldn't want to suggest you leave out 2.8, not sure what the problem
> is
>> > with it in my system and probably unusual. No idea if it's relates to
> the
>> > reason your user fails.
>> >
>> > I modified your function and it succeeded it correctly adding v2.7
>> > after
>> > first adding the problematic 2.8.
>> >
>> > Function AddReferenceFromFile( blah
>> > Dim oRef As Object ' Reference
>> > ' code
>> >
>> > On Error Resume Next
>> > Set oRef = VBProj.References.AddFromFile(strFilePath)
>> >
>> > ' AddReferenceFromFile = True
>> > AddReferenceFromFile = Len(oRef.fullPath) > 0
>> >
>> > If Err.Number Then
>> > If Not oRef Is Nothing Then
>> > VBProj.References.Remove oRef ' this removed my 2.8
>> > End If
>> > Else
>> > AddReferenceFromFile = True
>> > End If
>> >
>> > Regards,
>> > Peter T
>> >
>> > <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi Peter,
>> >>
>> >> My customers ( > 100 ) have been using this routine now for a week and
>> >> sofar
>> >> I haven't heard about any problems. Are you suggesting I should leave
>> >> the 2.8
>> >> option out?
>> >>
>> >> RBS
>> >>
>> >> Peter T wrote:
>> >> > Hi Bart,
>> >> >
>> >> > Yes I had noticed (later) the function didn't return True on
>> >> > success.
>> >> > However that doesn't change what I reported earlier about v2.8 not
>> > working
>> >> > correctly in my system. Trying to debug its Fullpath (after setting
> the
>> > ref)
>> >> > errors.
>> >> >
>> >> > In my XL2000 looking at tools > ref's v2.8 is checked and looks
> correct
>> > (but
>> >> > I know it will cause problems).
>> >> > I ran same code in XL97, v2.8 is also checked but marked MISSING.
>> >> >
>> >> > Regards,
>> >> > Peter
>> >> >
>> >> >
>> >> > <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > > Peter,
>> >> > >
>> >> > > Just noticed that the function misses this line at the end:
>> >> > >
>> >> > > AddReferenceFromFile = True
>> >> > >
>> >> > > Before Exit Function
>> >> > >
>> >> > > Try again with that added.
>> >> > >
>> >> > > RBS
>> >> > >
>> >> > >
>> >> > > Peter T wrote:
>> >> > > > Hi Bart,
>> >> > > >
>> >> > > > Your add ado ref routine both worked and failed getting your
> error
>> >> > message.
>> >> > > > Somehow it managed to add the ref to v2.8 though as far as I
>> >> > > > know
>> > this
>> >> > > > version is not correctly registered on my system. It doesn't
>> > normally
>> >> > appear
>> >> > > > in tools> ref's and things have gone wrong in the past using
> other
>> >> > people's
>> >> > > > wb's with this ref. So I always end up changing to 2.7 or rather
>> >> > > > 2.5
>> >> > which
>> >> > > > for some reason I find more reliable.
>> >> > > >
>> >> > > > As I say it added the 2.8 ref but when I did this
>> >> > > >
>> >> > > > ' r = a vba ref to v2.8
>> >> > > > Debug.Print r.Name, r.Description, r.Major, r.Minor
>> >> > > > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
>> >> > > >
>> >> > > > Debug.Print r.FullPath
>> >> > > > ' this fails !!
>> >> > > > debug.print err, err.description
>> >> > > > -2147319779 Method 'FullPath' of object 'Reference' failed
>> >> > > >
>> >> > > > If I change the ref to v2.7 both debug lines work
>> >> > > > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
>> >> > > > C:\Program Files\Common Files\System\ado\msado27.tlb
>> >> > > >
>> >> > > > Maybe something similar to your user.
>> >> > > >
>> >> > > > I have two versions of msado15.dll on my system, one an old v1.5
>> >> > > > and
>> > the
>> >> > > > newer 2.8 (why is it the 2.8 version named msado15.dll ! I also
>> >> > > > have
>> > all
>> >> > the
>> >> > > > other versions in your list.
>> >> > > >
>> >> > > > FWIW I recall having problems when trying to upgrade to 2.8, it
> was
>> > a
>> >> > while
>> >> > > > ago.
>> >> > > >
>> >> > > > Regards,
>> >> > > > Peter T
>> >> > > >
>> >> > > > PS I commented out your code to get file name from an ini and
> did -
>> >> > > > strADOPathFromINI = ThisWorkbook.Name
>> >> > > >
>> >> > > > "RB Smissaert" <[email protected]> wrote in message
>> >> > > > news:[email protected]...
>> >> > > > > 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:
>> >> > [email protected]",
>> >> > > > _
>> >> > > > > 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
>> >> > > > >
>> >> > > > >
>> >> > > > >
>> >> > > > >
>> >> > > > >
>> >> > >
>> >>
>> >
>> >
>>
>
>

No, I can't set 2.8 via the GUID, I get error "Object library not
registered"

I used your original routine to add all versions, returned GUID each time
before removing the ref
I got these -

2.0, 2.1, 2.5, 2.6 are all same
{00000200-0000-0010-8000-00AA006D2EA4}

2.7
{EF53050B-882E-4776-B643-EDA472E8E3F2}

2.8
{2A75196C-D9EB-4129-B803-931327F72D5C}

s = the above GUID string
..references.AddFromGuid(s, 2, 8)
fails with the 2.8, all others work

It seems strange I can add AddFromFile the 'bad' ref and return its GUID,
yet can't AddFromGuid. But as I've said, there's something wrong with this
one!

Regards,
Peter T


"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
> Peter,
>
> Can you add that ADO reference via the GUID and will it be faulty despite
> setting
> the reference successfully?
> I am talking about the problematic 2.8 here.
>
> RBS
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%[email protected]...
> > Hi Bart,
> >
> > I wouldn't want to suggest you leave out 2.8, not sure what the problem
is
> > with it in my system and probably unusual. No idea if it's relates to
the
> > reason your user fails.
> >
> > I modified your function and it succeeded it correctly adding v2.7 after
> > first adding the problematic 2.8.
> >
> > Function AddReferenceFromFile( blah
> > Dim oRef As Object ' Reference
> > ' code
> >
> > On Error Resume Next
> > Set oRef = VBProj.References.AddFromFile(strFilePath)
> >
> > ' AddReferenceFromFile = True
> > AddReferenceFromFile = Len(oRef.fullPath) > 0
> >
> > If Err.Number Then
> > If Not oRef Is Nothing Then
> > VBProj.References.Remove oRef ' this removed my 2.8
> > End If
> > Else
> > AddReferenceFromFile = True
> > End If
> >
> > Regards,
> > Peter T
> >
> > <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi Peter,
> >>
> >> My customers ( > 100 ) have been using this routine now for a week and
> >> sofar
> >> I haven't heard about any problems. Are you suggesting I should leave
> >> the 2.8
> >> option out?
> >>
> >> RBS
> >>
> >> Peter T wrote:
> >> > Hi Bart,
> >> >
> >> > Yes I had noticed (later) the function didn't return True on success.
> >> > However that doesn't change what I reported earlier about v2.8 not
> > working
> >> > correctly in my system. Trying to debug its Fullpath (after setting
the
> > ref)
> >> > errors.
> >> >
> >> > In my XL2000 looking at tools > ref's v2.8 is checked and looks
correct
> > (but
> >> > I know it will cause problems).
> >> > I ran same code in XL97, v2.8 is also checked but marked MISSING.
> >> >
> >> > Regards,
> >> > Peter
> >> >
> >> >
> >> > <[email protected]> wrote in message
> >> > news:[email protected]...
> >> > > Peter,
> >> > >
> >> > > Just noticed that the function misses this line at the end:
> >> > >
> >> > > AddReferenceFromFile = True
> >> > >
> >> > > Before Exit Function
> >> > >
> >> > > Try again with that added.
> >> > >
> >> > > RBS
> >> > >
> >> > >
> >> > > Peter T wrote:
> >> > > > Hi Bart,
> >> > > >
> >> > > > Your add ado ref routine both worked and failed getting your
error
> >> > message.
> >> > > > Somehow it managed to add the ref to v2.8 though as far as I know
> > this
> >> > > > version is not correctly registered on my system. It doesn't
> > normally
> >> > appear
> >> > > > in tools> ref's and things have gone wrong in the past using
other
> >> > people's
> >> > > > wb's with this ref. So I always end up changing to 2.7 or rather
> >> > > > 2.5
> >> > which
> >> > > > for some reason I find more reliable.
> >> > > >
> >> > > > As I say it added the 2.8 ref but when I did this
> >> > > >
> >> > > > ' r = a vba ref to v2.8
> >> > > > Debug.Print r.Name, r.Description, r.Major, r.Minor
> >> > > > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
> >> > > >
> >> > > > Debug.Print r.FullPath
> >> > > > ' this fails !!
> >> > > > debug.print err, err.description
> >> > > > -2147319779 Method 'FullPath' of object 'Reference' failed
> >> > > >
> >> > > > If I change the ref to v2.7 both debug lines work
> >> > > > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
> >> > > > C:\Program Files\Common Files\System\ado\msado27.tlb
> >> > > >
> >> > > > Maybe something similar to your user.
> >> > > >
> >> > > > I have two versions of msado15.dll on my system, one an old v1.5
> >> > > > and
> > the
> >> > > > newer 2.8 (why is it the 2.8 version named msado15.dll ! I also
> >> > > > have
> > all
> >> > the
> >> > > > other versions in your list.
> >> > > >
> >> > > > FWIW I recall having problems when trying to upgrade to 2.8, it
was
> > a
> >> > while
> >> > > > ago.
> >> > > >
> >> > > > Regards,
> >> > > > Peter T
> >> > > >
> >> > > > PS I commented out your code to get file name from an ini and
did -
> >> > > > strADOPathFromINI = ThisWorkbook.Name
> >> > > >
> >> > > > "RB Smissaert" <[email protected]> wrote in message
> >> > > > news:[email protected]...
> >> > > > > 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:
> >> > [email protected]",
> >> > > > _
> >> > > > > 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
> >> > > > >
> >> > > > >
> >> > > > >
> >> > > > >
> >> > > > >
> >> > >
> >>
> >
> >
>

I've just found out I have the same problem with 2.6 (but 2.7 seems OK).

My understanding in this scenario - "Object library not registered" - means
not registered to Excel (not like say a dll or ocx not registered). So I
tried -

Start > Run "full path to excel.exe" /regserver

No difference !

Regards,
Peter T

"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> No, I can't set 2.8 via the GUID, I get error "Object library not
> registered"
>
> I used your original routine to add all versions, returned GUID each time
> before removing the ref
> I got these -
>
> 2.0, 2.1, 2.5, 2.6 are all same
> {00000200-0000-0010-8000-00AA006D2EA4}
>
> 2.7
> {EF53050B-882E-4776-B643-EDA472E8E3F2}
>
> 2.8
> {2A75196C-D9EB-4129-B803-931327F72D5C}
>
> s = the above GUID string
> .references.AddFromGuid(s, 2, 8)
> fails with the 2.8, all others work
>
> It seems strange I can add AddFromFile the 'bad' ref and return its GUID,
> yet can't AddFromGuid. But as I've said, there's something wrong with this
> one!
>
> Regards,
> Peter T
>
>
> "RB Smissaert" <[email protected]> wrote in message
> news:[email protected]...
> > Peter,
> >
> > Can you add that ADO reference via the GUID and will it be faulty
despite
> > setting
> > the reference successfully?
> > I am talking about the problematic 2.8 here.
> >
> > RBS

Bloody confusing this ADO!
Have you tried the MDAC version checker?
http://www.microsoft.com/downloads/d...displaylang=en

RBS


"Peter T" <peter_t@discussions> wrote in message
news:[email protected]...
> No, I can't set 2.8 via the GUID, I get error "Object library not
> registered"
>
> I used your original routine to add all versions, returned GUID each time
> before removing the ref
> I got these -
>
> 2.0, 2.1, 2.5, 2.6 are all same
> {00000200-0000-0010-8000-00AA006D2EA4}
>
> 2.7
> {EF53050B-882E-4776-B643-EDA472E8E3F2}
>
> 2.8
> {2A75196C-D9EB-4129-B803-931327F72D5C}
>
> s = the above GUID string
> .references.AddFromGuid(s, 2, 8)
> fails with the 2.8, all others work
>
> It seems strange I can add AddFromFile the 'bad' ref and return its GUID,
> yet can't AddFromGuid. But as I've said, there's something wrong with this
> one!
>
> Regards,
> Peter T
>
>
> "RB Smissaert" <[email protected]> wrote in message
> news:[email protected]...
>> Peter,
>>
>> Can you add that ADO reference via the GUID and will it be faulty despite
>> setting
>> the reference successfully?
>> I am talking about the problematic 2.8 here.
>>
>> RBS
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:%[email protected]...
>> > Hi Bart,
>> >
>> > I wouldn't want to suggest you leave out 2.8, not sure what the problem
> is
>> > with it in my system and probably unusual. No idea if it's relates to
> the
>> > reason your user fails.
>> >
>> > I modified your function and it succeeded it correctly adding v2.7
>> > after
>> > first adding the problematic 2.8.
>> >
>> > Function AddReferenceFromFile( blah
>> > Dim oRef As Object ' Reference
>> > ' code
>> >
>> > On Error Resume Next
>> > Set oRef = VBProj.References.AddFromFile(strFilePath)
>> >
>> > ' AddReferenceFromFile = True
>> > AddReferenceFromFile = Len(oRef.fullPath) > 0
>> >
>> > If Err.Number Then
>> > If Not oRef Is Nothing Then
>> > VBProj.References.Remove oRef ' this removed my 2.8
>> > End If
>> > Else
>> > AddReferenceFromFile = True
>> > End If
>> >
>> > Regards,
>> > Peter T
>> >
>> > <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi Peter,
>> >>
>> >> My customers ( > 100 ) have been using this routine now for a week and
>> >> sofar
>> >> I haven't heard about any problems. Are you suggesting I should leave
>> >> the 2.8
>> >> option out?
>> >>
>> >> RBS
>> >>
>> >> Peter T wrote:
>> >> > Hi Bart,
>> >> >
>> >> > Yes I had noticed (later) the function didn't return True on
>> >> > success.
>> >> > However that doesn't change what I reported earlier about v2.8 not
>> > working
>> >> > correctly in my system. Trying to debug its Fullpath (after setting
> the
>> > ref)
>> >> > errors.
>> >> >
>> >> > In my XL2000 looking at tools > ref's v2.8 is checked and looks
> correct
>> > (but
>> >> > I know it will cause problems).
>> >> > I ran same code in XL97, v2.8 is also checked but marked MISSING.
>> >> >
>> >> > Regards,
>> >> > Peter
>> >> >
>> >> >
>> >> > <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> > > Peter,
>> >> > >
>> >> > > Just noticed that the function misses this line at the end:
>> >> > >
>> >> > > AddReferenceFromFile = True
>> >> > >
>> >> > > Before Exit Function
>> >> > >
>> >> > > Try again with that added.
>> >> > >
>> >> > > RBS
>> >> > >
>> >> > >
>> >> > > Peter T wrote:
>> >> > > > Hi Bart,
>> >> > > >
>> >> > > > Your add ado ref routine both worked and failed getting your
> error
>> >> > message.
>> >> > > > Somehow it managed to add the ref to v2.8 though as far as I
>> >> > > > know
>> > this
>> >> > > > version is not correctly registered on my system. It doesn't
>> > normally
>> >> > appear
>> >> > > > in tools> ref's and things have gone wrong in the past using
> other
>> >> > people's
>> >> > > > wb's with this ref. So I always end up changing to 2.7 or rather
>> >> > > > 2.5
>> >> > which
>> >> > > > for some reason I find more reliable.
>> >> > > >
>> >> > > > As I say it added the 2.8 ref but when I did this
>> >> > > >
>> >> > > > ' r = a vba ref to v2.8
>> >> > > > Debug.Print r.Name, r.Description, r.Major, r.Minor
>> >> > > > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
>> >> > > >
>> >> > > > Debug.Print r.FullPath
>> >> > > > ' this fails !!
>> >> > > > debug.print err, err.description
>> >> > > > -2147319779 Method 'FullPath' of object 'Reference' failed
>> >> > > >
>> >> > > > If I change the ref to v2.7 both debug lines work
>> >> > > > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
>> >> > > > C:\Program Files\Common Files\System\ado\msado27.tlb
>> >> > > >
>> >> > > > Maybe something similar to your user.
>> >> > > >
>> >> > > > I have two versions of msado15.dll on my system, one an old v1.5
>> >> > > > and
>> > the
>> >> > > > newer 2.8 (why is it the 2.8 version named msado15.dll ! I also
>> >> > > > have
>> > all
>> >> > the
>> >> > > > other versions in your list.
>> >> > > >
>> >> > > > FWIW I recall having problems when trying to upgrade to 2.8, it
> was
>> > a
>> >> > while
>> >> > > > ago.
>> >> > > >
>> >> > > > Regards,
>> >> > > > Peter T
>> >> > > >
>> >> > > > PS I commented out your code to get file name from an ini and
> did -
>> >> > > > strADOPathFromINI = ThisWorkbook.Name
>> >> > > >
>> >> > > > "RB Smissaert" <[email protected]> wrote in message
>> >> > > > news:[email protected]...
>> >> > > > > 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:
>> >> > [email protected]",
>> >> > > > _
>> >> > > > > 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
>> >> > > > >
>> >> > > > >
>> >> > > > >
>> >> > > > >
>> >> > > > >
>> >> > >
>> >>
>> >
>> >
>>
>
>

Looks interesting, I try and get it -

MS need to verify I've got genuine Windows, continue -

Seems I need to use the "alternative" Genuine Windows Validation method -

Run the validation tool - Continue -
Download and run from location -

msgbox - this version of Windows Advantage is no longer supported, please
download the latest version

Give up !!

Regards,
Peter T


"RB Smissaert" <[email protected]> wrote in message
news:#[email protected]...
> Bloody confusing this ADO!
> Have you tried the MDAC version checker?
>
http://www.microsoft.com/downloads/d...DF6-4A21-4B43-
BF53-14332EF092C9&displaylang=en
>
> RBS
>
>
> "Peter T" <peter_t@discussions> wrote in message
> news:[email protected]...
> > No, I can't set 2.8 via the GUID, I get error "Object library not
> > registered"
> >
> > I used your original routine to add all versions, returned GUID each
time
> > before removing the ref
> > I got these -
> >
> > 2.0, 2.1, 2.5, 2.6 are all same
> > {00000200-0000-0010-8000-00AA006D2EA4}
> >
> > 2.7
> > {EF53050B-882E-4776-B643-EDA472E8E3F2}
> >
> > 2.8
> > {2A75196C-D9EB-4129-B803-931327F72D5C}
> >
> > s = the above GUID string
> > .references.AddFromGuid(s, 2, 8)
> > fails with the 2.8, all others work
> >
> > It seems strange I can add AddFromFile the 'bad' ref and return its
GUID,
> > yet can't AddFromGuid. But as I've said, there's something wrong with
this
> > one!
> >
> > Regards,
> > Peter T
> >
> >
> > "RB Smissaert" <[email protected]> wrote in message
> > news:[email protected]...
> >> Peter,
> >>
> >> Can you add that ADO reference via the GUID and will it be faulty
despite
> >> setting
> >> the reference successfully?
> >> I am talking about the problematic 2.8 here.
> >>
> >> RBS
> >>
> >>
> >> "Peter T" <peter_t@discussions> wrote in message
> >> news:%[email protected]...
> >> > Hi Bart,
> >> >
> >> > I wouldn't want to suggest you leave out 2.8, not sure what the
problem
> > is
> >> > with it in my system and probably unusual. No idea if it's relates to
> > the
> >> > reason your user fails.
> >> >
> >> > I modified your function and it succeeded it correctly adding v2.7
> >> > after
> >> > first adding the problematic 2.8.
> >> >
> >> > Function AddReferenceFromFile( blah
> >> > Dim oRef As Object ' Reference
> >> > ' code
> >> >
> >> > On Error Resume Next
> >> > Set oRef = VBProj.References.AddFromFile(strFilePath)
> >> >
> >> > ' AddReferenceFromFile = True
> >> > AddReferenceFromFile = Len(oRef.fullPath) > 0
> >> >
> >> > If Err.Number Then
> >> > If Not oRef Is Nothing Then
> >> > VBProj.References.Remove oRef ' this removed my 2.8
> >> > End If
> >> > Else
> >> > AddReferenceFromFile = True
> >> > End If
> >> >
> >> > Regards,
> >> > Peter T
> >> >
> >> > <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Hi Peter,
> >> >>
> >> >> My customers ( > 100 ) have been using this routine now for a week
and
> >> >> sofar
> >> >> I haven't heard about any problems. Are you suggesting I should
leave
> >> >> the 2.8
> >> >> option out?
> >> >>
> >> >> RBS
> >> >>
> >> >> Peter T wrote:
> >> >> > Hi Bart,
> >> >> >
> >> >> > Yes I had noticed (later) the function didn't return True on
> >> >> > success.
> >> >> > However that doesn't change what I reported earlier about v2.8 not
> >> > working
> >> >> > correctly in my system. Trying to debug its Fullpath (after
setting
> > the
> >> > ref)
> >> >> > errors.
> >> >> >
> >> >> > In my XL2000 looking at tools > ref's v2.8 is checked and looks
> > correct
> >> > (but
> >> >> > I know it will cause problems).
> >> >> > I ran same code in XL97, v2.8 is also checked but marked MISSING.
> >> >> >
> >> >> > Regards,
> >> >> > Peter
> >> >> >
> >> >> >
> >> >> > <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> > > Peter,
> >> >> > >
> >> >> > > Just noticed that the function misses this line at the end:
> >> >> > >
> >> >> > > AddReferenceFromFile = True
> >> >> > >
> >> >> > > Before Exit Function
> >> >> > >
> >> >> > > Try again with that added.
> >> >> > >
> >> >> > > RBS
> >> >> > >
> >> >> > >
> >> >> > > Peter T wrote:
> >> >> > > > Hi Bart,
> >> >> > > >
> >> >> > > > Your add ado ref routine both worked and failed getting your
> > error
> >> >> > message.
> >> >> > > > Somehow it managed to add the ref to v2.8 though as far as I
> >> >> > > > know
> >> > this
> >> >> > > > version is not correctly registered on my system. It doesn't
> >> > normally
> >> >> > appear
> >> >> > > > in tools> ref's and things have gone wrong in the past using
> > other
> >> >> > people's
> >> >> > > > wb's with this ref. So I always end up changing to 2.7 or
rather
> >> >> > > > 2.5
> >> >> > which
> >> >> > > > for some reason I find more reliable.
> >> >> > > >
> >> >> > > > As I say it added the 2.8 ref but when I did this
> >> >> > > >
> >> >> > > > ' r = a vba ref to v2.8
> >> >> > > > Debug.Print r.Name, r.Description, r.Major, r.Minor
> >> >> > > > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
> >> >> > > >
> >> >> > > > Debug.Print r.FullPath
> >> >> > > > ' this fails !!
> >> >> > > > debug.print err, err.description
> >> >> > > > -2147319779 Method 'FullPath' of object 'Reference' failed
> >> >> > > >
> >> >> > > > If I change the ref to v2.7 both debug lines work
> >> >> > > > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
> >> >> > > > C:\Program Files\Common Files\System\ado\msado27.tlb
> >> >> > > >
> >> >> > > > Maybe something similar to your user.
> >> >> > > >
> >> >> > > > I have two versions of msado15.dll on my system, one an old
v1.5
> >> >> > > > and
> >> > the
> >> >> > > > newer 2.8 (why is it the 2.8 version named msado15.dll ! I
also
> >> >> > > > have
> >> > all
> >> >> > the
> >> >> > > > other versions in your list.
> >> >> > > >
> >> >> > > > FWIW I recall having problems when trying to upgrade to 2.8,
it
> > was
> >> > a
> >> >> > while
> >> >> > > > ago.
> >> >> > > >
> >> >> > > > Regards,
> >> >> > > > Peter T
> >> >> > > >
> >> >> > > > PS I commented out your code to get file name from an ini and
> > did -
> >> >> > > > strADOPathFromINI = ThisWorkbook.Name
> >> >> > > >
> >> >> > > > "RB Smissaert" <[email protected]> wrote in
message
> >> >> > > > news:[email protected]...
> >> >> > > > > 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:
> >> >> > [email protected]",
> >> >> > > > _
> >> >> > > > > 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
> >> >> > > > >
> >> >> > > > >
> >> >> > > > >
> >> >> > > > >
> >> >> > > > >
> >> >> > >
> >> >>
> >> >
> >> >
> >>
> >
> >
>

I can mail you the setup file.

RBS

"Peter T" <peter_t@discussions> wrote in message
news:%[email protected]...
> Looks interesting, I try and get it -
>
> MS need to verify I've got genuine Windows, continue -
>
> Seems I need to use the "alternative" Genuine Windows Validation method -
>
> Run the validation tool - Continue -
> Download and run from location -
>
> msgbox - this version of Windows Advantage is no longer supported, please
> download the latest version
>
> Give up !!
>
> Regards,
> Peter T
>
>
> "RB Smissaert" <[email protected]> wrote in message
> news:#[email protected]...
>> Bloody confusing this ADO!
>> Have you tried the MDAC version checker?
>>
> http://www.microsoft.com/downloads/d...DF6-4A21-4B43-
> BF53-14332EF092C9&displaylang=en
>>
>> RBS
>>
>>
>> "Peter T" <peter_t@discussions> wrote in message
>> news:[email protected]...
>> > No, I can't set 2.8 via the GUID, I get error "Object library not
>> > registered"
>> >
>> > I used your original routine to add all versions, returned GUID each
> time
>> > before removing the ref
>> > I got these -
>> >
>> > 2.0, 2.1, 2.5, 2.6 are all same
>> > {00000200-0000-0010-8000-00AA006D2EA4}
>> >
>> > 2.7
>> > {EF53050B-882E-4776-B643-EDA472E8E3F2}
>> >
>> > 2.8
>> > {2A75196C-D9EB-4129-B803-931327F72D5C}
>> >
>> > s = the above GUID string
>> > .references.AddFromGuid(s, 2, 8)
>> > fails with the 2.8, all others work
>> >
>> > It seems strange I can add AddFromFile the 'bad' ref and return its
> GUID,
>> > yet can't AddFromGuid. But as I've said, there's something wrong with
> this
>> > one!
>> >
>> > Regards,
>> > Peter T
>> >
>> >
>> > "RB Smissaert" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Peter,
>> >>
>> >> Can you add that ADO reference via the GUID and will it be faulty
> despite
>> >> setting
>> >> the reference successfully?
>> >> I am talking about the problematic 2.8 here.
>> >>
>> >> RBS
>> >>
>> >>
>> >> "Peter T" <peter_t@discussions> wrote in message
>> >> news:%[email protected]...
>> >> > Hi Bart,
>> >> >
>> >> > I wouldn't want to suggest you leave out 2.8, not sure what the
> problem
>> > is
>> >> > with it in my system and probably unusual. No idea if it's relates
>> >> > to
>> > the
>> >> > reason your user fails.
>> >> >
>> >> > I modified your function and it succeeded it correctly adding v2.7
>> >> > after
>> >> > first adding the problematic 2.8.
>> >> >
>> >> > Function AddReferenceFromFile( blah
>> >> > Dim oRef As Object ' Reference
>> >> > ' code
>> >> >
>> >> > On Error Resume Next
>> >> > Set oRef = VBProj.References.AddFromFile(strFilePath)
>> >> >
>> >> > ' AddReferenceFromFile = True
>> >> > AddReferenceFromFile = Len(oRef.fullPath) > 0
>> >> >
>> >> > If Err.Number Then
>> >> > If Not oRef Is Nothing Then
>> >> > VBProj.References.Remove oRef ' this removed my 2.8
>> >> > End If
>> >> > Else
>> >> > AddReferenceFromFile = True
>> >> > End If
>> >> >
>> >> > Regards,
>> >> > Peter T
>> >> >
>> >> > <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> Hi Peter,
>> >> >>
>> >> >> My customers ( > 100 ) have been using this routine now for a week
> and
>> >> >> sofar
>> >> >> I haven't heard about any problems. Are you suggesting I should
> leave
>> >> >> the 2.8
>> >> >> option out?
>> >> >>
>> >> >> RBS
>> >> >>
>> >> >> Peter T wrote:
>> >> >> > Hi Bart,
>> >> >> >
>> >> >> > Yes I had noticed (later) the function didn't return True on
>> >> >> > success.
>> >> >> > However that doesn't change what I reported earlier about v2.8
>> >> >> > not
>> >> > working
>> >> >> > correctly in my system. Trying to debug its Fullpath (after
> setting
>> > the
>> >> > ref)
>> >> >> > errors.
>> >> >> >
>> >> >> > In my XL2000 looking at tools > ref's v2.8 is checked and looks
>> > correct
>> >> > (but
>> >> >> > I know it will cause problems).
>> >> >> > I ran same code in XL97, v2.8 is also checked but marked MISSING.
>> >> >> >
>> >> >> > Regards,
>> >> >> > Peter
>> >> >> >
>> >> >> >
>> >> >> > <[email protected]> wrote in message
>> >> >> > news:[email protected]...
>> >> >> > > Peter,
>> >> >> > >
>> >> >> > > Just noticed that the function misses this line at the end:
>> >> >> > >
>> >> >> > > AddReferenceFromFile = True
>> >> >> > >
>> >> >> > > Before Exit Function
>> >> >> > >
>> >> >> > > Try again with that added.
>> >> >> > >
>> >> >> > > RBS
>> >> >> > >
>> >> >> > >
>> >> >> > > Peter T wrote:
>> >> >> > > > Hi Bart,
>> >> >> > > >
>> >> >> > > > Your add ado ref routine both worked and failed getting your
>> > error
>> >> >> > message.
>> >> >> > > > Somehow it managed to add the ref to v2.8 though as far as I
>> >> >> > > > know
>> >> > this
>> >> >> > > > version is not correctly registered on my system. It doesn't
>> >> > normally
>> >> >> > appear
>> >> >> > > > in tools> ref's and things have gone wrong in the past using
>> > other
>> >> >> > people's
>> >> >> > > > wb's with this ref. So I always end up changing to 2.7 or
> rather
>> >> >> > > > 2.5
>> >> >> > which
>> >> >> > > > for some reason I find more reliable.
>> >> >> > > >
>> >> >> > > > As I say it added the 2.8 ref but when I did this
>> >> >> > > >
>> >> >> > > > ' r = a vba ref to v2.8
>> >> >> > > > Debug.Print r.Name, r.Description, r.Major, r.Minor
>> >> >> > > > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
>> >> >> > > >
>> >> >> > > > Debug.Print r.FullPath
>> >> >> > > > ' this fails !!
>> >> >> > > > debug.print err, err.description
>> >> >> > > > -2147319779 Method 'FullPath' of object 'Reference' failed
>> >> >> > > >
>> >> >> > > > If I change the ref to v2.7 both debug lines work
>> >> >> > > > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
>> >> >> > > > C:\Program Files\Common Files\System\ado\msado27.tlb
>> >> >> > > >
>> >> >> > > > Maybe something similar to your user.
>> >> >> > > >
>> >> >> > > > I have two versions of msado15.dll on my system, one an old
> v1.5
>> >> >> > > > and
>> >> > the
>> >> >> > > > newer 2.8 (why is it the 2.8 version named msado15.dll ! I
> also
>> >> >> > > > have
>> >> > all
>> >> >> > the
>> >> >> > > > other versions in your list.
>> >> >> > > >
>> >> >> > > > FWIW I recall having problems when trying to upgrade to 2.8,
> it
>> > was
>> >> > a
>> >> >> > while
>> >> >> > > > ago.
>> >> >> > > >
>> >> >> > > > Regards,
>> >> >> > > > Peter T
>> >> >> > > >
>> >> >> > > > PS I commented out your code to get file name from an ini and
>> > did -
>> >> >> > > > strADOPathFromINI = ThisWorkbook.Name
>> >> >> > > >
>> >> >> > > > "RB Smissaert" <[email protected]> wrote in
> message
>> >> >> > > > news:[email protected]...
>> >> >> > > > > 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:
>> >> >> > [email protected]",
>> >> >> > > > _
>> >> >> > > > > 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
>> >> >> > > > >
>> >> >> > > > >
>> >> >> > > > >
>> >> >> > > > >
>> >> >> > > > >
>> >> >> > >
>> >> >>
>> >> >
>> >> >
>> >>
>> >
>> >
>>
>
>

Thanks, but for some reason I persevered, downloaded the same validation
tool again and this time gave me a code

Got the setup (I already have an older version, maybe that's what the
message was about but what is "Advantage" - very confusing).

I run it, reports many mismatch registry fields, not only in 2.8 but all my
versions, incl 2.5 which works fine.
Think I'll pass on trying to manually edit so many registry entries.

Regards,
Peter T


"RB Smissaert" <[email protected]> wrote in message
news:[email protected]...
> I can mail you the setup file.
>
> RBS
>
> "Peter T" <peter_t@discussions> wrote in message
> news:%[email protected]...
> > Looks interesting, I try and get it -
> >
> > MS need to verify I've got genuine Windows, continue -
> >
> > Seems I need to use the "alternative" Genuine Windows Validation
method -
> >
> > Run the validation tool - Continue -
> > Download and run from location -
> >
> > msgbox - this version of Windows Advantage is no longer supported,
please
> > download the latest version
> >
> > Give up !!
> >
> > Regards,
> > Peter T
> >
> >
> > "RB Smissaert" <[email protected]> wrote in message
> > news:#[email protected]...
> >> Bloody confusing this ADO!
> >> Have you tried the MDAC version checker?
> >>
> >
http://www.microsoft.com/downloads/d...DF6-4A21-4B43-
> > BF53-14332EF092C9&displaylang=en
> >>
> >> RBS
> >>
> >>
> >> "Peter T" <peter_t@discussions> wrote in message
> >> news:[email protected]...
> >> > No, I can't set 2.8 via the GUID, I get error "Object library not
> >> > registered"
> >> >
> >> > I used your original routine to add all versions, returned GUID each
> > time
> >> > before removing the ref
> >> > I got these -
> >> >
> >> > 2.0, 2.1, 2.5, 2.6 are all same
> >> > {00000200-0000-0010-8000-00AA006D2EA4}
> >> >
> >> > 2.7
> >> > {EF53050B-882E-4776-B643-EDA472E8E3F2}
> >> >
> >> > 2.8
> >> > {2A75196C-D9EB-4129-B803-931327F72D5C}
> >> >
> >> > s = the above GUID string
> >> > .references.AddFromGuid(s, 2, 8)
> >> > fails with the 2.8, all others work
> >> >
> >> > It seems strange I can add AddFromFile the 'bad' ref and return its
> > GUID,
> >> > yet can't AddFromGuid. But as I've said, there's something wrong with
> > this
> >> > one!
> >> >
> >> > Regards,
> >> > Peter T
> >> >
> >> >
> >> > "RB Smissaert" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Peter,
> >> >>
> >> >> Can you add that ADO reference via the GUID and will it be faulty
> > despite
> >> >> setting
> >> >> the reference successfully?
> >> >> I am talking about the problematic 2.8 here.
> >> >>
> >> >> RBS
> >> >>
> >> >>
> >> >> "Peter T" <peter_t@discussions> wrote in message
> >> >> news:%[email protected]...
> >> >> > Hi Bart,
> >> >> >
> >> >> > I wouldn't want to suggest you leave out 2.8, not sure what the
> > problem
> >> > is
> >> >> > with it in my system and probably unusual. No idea if it's relates
> >> >> > to
> >> > the
> >> >> > reason your user fails.
> >> >> >
> >> >> > I modified your function and it succeeded it correctly adding v2.7
> >> >> > after
> >> >> > first adding the problematic 2.8.
> >> >> >
> >> >> > Function AddReferenceFromFile( blah
> >> >> > Dim oRef As Object ' Reference
> >> >> > ' code
> >> >> >
> >> >> > On Error Resume Next
> >> >> > Set oRef = VBProj.References.AddFromFile(strFilePath)
> >> >> >
> >> >> > ' AddReferenceFromFile = True
> >> >> > AddReferenceFromFile = Len(oRef.fullPath) > 0
> >> >> >
> >> >> > If Err.Number Then
> >> >> > If Not oRef Is Nothing Then
> >> >> > VBProj.References.Remove oRef ' this removed my 2.8
> >> >> > End If
> >> >> > Else
> >> >> > AddReferenceFromFile = True
> >> >> > End If
> >> >> >
> >> >> > Regards,
> >> >> > Peter T
> >> >> >
> >> >> > <[email protected]> wrote in message
> >> >> > news:[email protected]...
> >> >> >> Hi Peter,
> >> >> >>
> >> >> >> My customers ( > 100 ) have been using this routine now for a
week
> > and
> >> >> >> sofar
> >> >> >> I haven't heard about any problems. Are you suggesting I should
> > leave
> >> >> >> the 2.8
> >> >> >> option out?
> >> >> >>
> >> >> >> RBS
> >> >> >>
> >> >> >> Peter T wrote:
> >> >> >> > Hi Bart,
> >> >> >> >
> >> >> >> > Yes I had noticed (later) the function didn't return True on
> >> >> >> > success.
> >> >> >> > However that doesn't change what I reported earlier about v2.8
> >> >> >> > not
> >> >> > working
> >> >> >> > correctly in my system. Trying to debug its Fullpath (after
> > setting
> >> > the
> >> >> > ref)
> >> >> >> > errors.
> >> >> >> >
> >> >> >> > In my XL2000 looking at tools > ref's v2.8 is checked and looks
> >> > correct
> >> >> > (but
> >> >> >> > I know it will cause problems).
> >> >> >> > I ran same code in XL97, v2.8 is also checked but marked
MISSING.
> >> >> >> >
> >> >> >> > Regards,
> >> >> >> > Peter
> >> >> >> >
> >> >> >> >
> >> >> >> > <[email protected]> wrote in message
> >> >> >> > news:[email protected]...
> >> >> >> > > Peter,
> >> >> >> > >
> >> >> >> > > Just noticed that the function misses this line at the end:
> >> >> >> > >
> >> >> >> > > AddReferenceFromFile = True
> >> >> >> > >
> >> >> >> > > Before Exit Function
> >> >> >> > >
> >> >> >> > > Try again with that added.
> >> >> >> > >
> >> >> >> > > RBS
> >> >> >> > >
> >> >> >> > >
> >> >> >> > > Peter T wrote:
> >> >> >> > > > Hi Bart,
> >> >> >> > > >
> >> >> >> > > > Your add ado ref routine both worked and failed getting
your
> >> > error
> >> >> >> > message.
> >> >> >> > > > Somehow it managed to add the ref to v2.8 though as far as
I
> >> >> >> > > > know
> >> >> > this
> >> >> >> > > > version is not correctly registered on my system. It
doesn't
> >> >> > normally
> >> >> >> > appear
> >> >> >> > > > in tools> ref's and things have gone wrong in the past
using
> >> > other
> >> >> >> > people's
> >> >> >> > > > wb's with this ref. So I always end up changing to 2.7 or
> > rather
> >> >> >> > > > 2.5
> >> >> >> > which
> >> >> >> > > > for some reason I find more reliable.
> >> >> >> > > >
> >> >> >> > > > As I say it added the 2.8 ref but when I did this
> >> >> >> > > >
> >> >> >> > > > ' r = a vba ref to v2.8
> >> >> >> > > > Debug.Print r.Name, r.Description, r.Major, r.Minor
> >> >> >> > > > ADODB Microsoft ActiveX Data Objects 2.8 Library 2 8
> >> >> >> > > >
> >> >> >> > > > Debug.Print r.FullPath
> >> >> >> > > > ' this fails !!
> >> >> >> > > > debug.print err, err.description
> >> >> >> > > > -2147319779 Method 'FullPath' of object 'Reference'
failed
> >> >> >> > > >
> >> >> >> > > > If I change the ref to v2.7 both debug lines work
> >> >> >> > > > ADODB Microsoft ActiveX Data Objects 2.7 Library 2 7
> >> >> >> > > > C:\Program Files\Common Files\System\ado\msado27.tlb
> >> >> >> > > >
> >> >> >> > > > Maybe something similar to your user.
> >> >> >> > > >
> >> >> >> > > > I have two versions of msado15.dll on my system, one an old
> > v1.5
> >> >> >> > > > and
> >> >> > the
> >> >> >> > > > newer 2.8 (why is it the 2.8 version named msado15.dll ! I
> > also
> >> >> >> > > > have
> >> >> > all
> >> >> >> > the
> >> >> >> > > > other versions in your list.
> >> >> >> > > >
> >> >> >> > > > FWIW I recall having problems when trying to upgrade to
2.8,
> > it
> >> > was
> >> >> > a
> >> >> >> > while
> >> >> >> > > > ago.
> >> >> >> > > >
> >> >> >> > > > Regards,
> >> >> >> > > > Peter T
> >> >> >> > > >
> >> >> >> > > > PS I commented out your code to get file name from an ini
and
> >> > did -
> >> >> >> > > > strADOPathFromINI = ThisWorkbook.Name
> >> >> >> > > >
> >> >> >> > > > "RB Smissaert" <[email protected]> wrote in
> > message
> >> >> >> > > > news:[email protected]...
> >> >> >> > > > > 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:
> >> >> >> > [email protected]",
> >> >> >> > > > _
> >> >> >> > > > > 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
> >> >> >> > > > >
> >> >> >> > > > >
> >> >> >> > > > >
> >> >> >> > > > >
> >> >> >> > > > >
> >> >> >> > >
> >> >> >>
> >> >> >
> >> >> >
> >> >>
> >> >
> >> >
> >>
> >
> >
>