programmatically add a COM add-in to VBA for Excel 1

[mikej28621]

Hi,
I have written a program in VBA for Excel 2000/xp. It requires the MS ADO 2.7 library COM add-in to be checked in VBA (not excel itself). I need some code to check to see if it is installed and check it if neccessary.

 

[JPMontreal]

Hi,

Your question is very interesting. Here is what I found at Microsoft MSDN.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnado/html/msdn_adorosest.asp

Defining and Instantiating ADO Objects with Visual Basic for Applications
To create an Automation variable and instantiate an object for that variable, you can use two forms of syntax: the CreateObject method or the Dim statement. With the CreateObject method, each statement is a discrete action. However, you can both define a variable and assign it to an instantiated object in one step, as shown in Listing 2.
Listing 2: Instantiating an ADO Connection object within VBA
' Technique #1: Use CreateObject() to create Connection object.
' Declare variable.
Dim conn1
' Implicit instantiation.
Set conn1 = CreateObject("ADODB.Connection.2.0&quot

' Technique #2A: Use Dim ... as ... to create Connection object
' for a pre-defined variable.
' Declare variable.
Dim conn2 As ADODB.Connection
' Implicit instantiation.
Set conn2 = New ADODB.Connection

...

The Dim statement is only successful if you have already added the ADO type library to your project's references.

. . .


Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[Bowers74]

The following bit of code will check if the ADO 2.7 Library is installed for the current VBA Project:

Sub InstalledReferences()
Dim i As Integer
Dim msg As String
On Error Resume Next
For i = 1 To 40
   msg = msg & Application.VBE.ActiveVBProject. _
         References.Item(i).Description & vbCrLf
Next
' MsgBox msg, vbOKOnly, "Installed References"
If Not msg Like "*ActiveX Data Objects 2.7*" Then
    MsgBox "Microsoft ADO 2.7 Library" & _
        " is not installed in this VBA Project!", , "Reference Missing"
End If
End Sub

I hope this helps!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

It's a bit crude (only a slight change from the above), but it will install the ADO 2.7 Library for you if it is missing:

Sub Install_ADO27Lib_IfMissing()
Dim i As Integer
Dim msg As String
Dim ADO27LibPath As String
' You will need to change this to match the Path on your system
ADO27LibPath = "C:\Program Files\Common Files\System\ado\msado15.dll"
On Error Resume Next
For i = 1 To 40
   List = List & Application.VBE.ActiveVBProject. _
          References.Item(i).Description & vbCrLf
Next
If Not List Like "*ActiveX Data Objects 2.7*" Then
    ThisWorkbook.VBProject.References.AddFromFile ADO27LibPath
End If
End Sub

I hope this helps!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JPMontreal]

Hi,

That’s very helpful Bowers74. Get a star.


Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[Bowers74]

Thanks for the Star JP,

Here is a more optimized code (containing all known ADO Libraries just in case). You will have to change the path to match the path on your computer though:

Sub Activate_ADORef_IfMissing()
Dim vbpRef As Object
Dim i As Integer, r As Integer
Dim Ref As String, refFile As String
Set vbpRef = ThisWorkbook.VBProject.References
Ref = "*ActiveX Data Objects 2.*"

' refFile = "C:\Program Files\Common Files\System\ado\msado20.dll" ' ADO 2.0

' refFile = "C:\Program Files\Common Files\System\ado\msado21.dll" ' ADO 2.1

' refFile = "C:\Program Files\Common Files\System\ado\msado25.dll" ' ADO 2.5

' refFile = "C:\Program Files\Common Files\System\ado\msado26.dll" ' ADO 2.6

refFile = "C:\Program Files\Common Files\System\ado\msado15.dll" ' ADO 2.7
On Error Resume Next
For i = 1 To vbpRef.Count
   If vbpRef.Item(i).Description Like Ref Then Exit Sub
Next
vbpRef.AddFromFile refFile
End Sub

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[Bowers74]

mikej28621,

I was just wondering if any of these solutions worked for you?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[mikej28621]

Thanks Guys that looks exactly like what I was looking for. I will work on it and let you know how it goes.

Many Thanks

MJ

 

[mikej28621]

I have tried several variations of Bower74's code to list refernces and they all come back blank. It runs with no errors just empty. I even added "debug.print .VBE.ActiveVBProject. _
References.Item(i).Description"
to the loop and it show blanks.

I also read a good chapter in the VBA Developers Handbook by Getz and Gilbert that recommended the following code:

Dim ref As reference

For Each ref In Application.VBE.ActiveVBProject.References

'use each ref and pring
'name and version
'description
'built in or custom
'project or type lib
'broken or intact
'full path
'guid


With ref
Debug.Print .Name & " " & .major & "." & .minor
If Not .isbroken Then
Debug.Print " " & .Description
End If
Debug.Print " "; IIf(.BuiltIn, "built-in/", "custom/"
Debug.Print " "; IIf(.Type = vbext_rk_project, "project/", "thpelib/"
Debug.Print " "; IIf(.isbroken, "broken", "intact&quot
Debug.Print " "; .fullpath
Debug.Print " "; IIf(.Type = vbext_rk_typelib, .GUID, "&quot
End With
Next



to list all refereces to the debug window but it dies on the "Dim ref As reference" line. Error: user defined type not defined. Any thoughts helpful.

Thanks in advance

MJ

 

[Bowers74]

Before you can deal with anything in the VBE, you need to make sure that you have a reference to the "Microsoft Visual Basic for Applications Extensibility 5.3" or else it won't work.

Sorry that I didn't mention that before, but I figured that since you were already planning on referring to VBAProjects, that you already knew that.

The reason that you weren't receiving an error is because of the line "On Error Resume Next".

Sorry for the confusion!

Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[lienyi]

Also, goto Tools->Macro->Security. Click "Trusted Sources" tab and make sure the "Trust access to Visual Baisc Project" option is checked.

-Jerry

 

[Bowers74]

But only in Office XP! ;-)



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[mikej28621]

Thanks Mike and Jerry... That got it working.

Does anyone know if the "Microsoft Visual Basic for Applications Extensibility 5.3" should exist on any Excel 2k/xp version. I am trying to make sure that users don't have to try to check/interact with any settings.

Thanks again

MJ

 

[Bowers74]

A reference will have to be made to it, but if you are distributing this "app" to the users, then the references should already be referenced in the file that you are sending, so you should only have to see if the references are broken (missing).



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!