How to identify missing references (libraries) using VBA

[Helge V. Larsen]

I am developing Excel and Access applications that are meant to be used by
several (many?) other persons.

In the development I am using Office-2000 and Office-XP, but end-users will
use Office-97, Office-2000 and Office-XP.

I am opening an Excel object from Access and vice versa an Access object
from Excel. I am also using new functions in ADO (Microsoft ActiveX Data
Objects 2.8 Library). Therefore ADO version 2.7 cannot be used.

Consequently, I have in some way to identify missing libraries, i.e.
libraries that in the VBA editor are found by looking for "MISSING ..." in
"Tools / References...". Missing libraries could for instance be MSACC9.OLB
or MSACC10.OLB in Excel VBA and EXCEL9.OLB or EXCEL10.OLB in Access VBA.

I think that I have to accept just to be able to warn the user that a
library is missing. Probably it is not possible to correct the missing
references through VBA - or is it ??

I have made some VBA that functions in Office-2000 :

In Excel :
++++++++++

Sub HVL_Find_Missing_References_Excel()
Dim aReference As Object
Dim aMsg as String
aMsg = "Missing reference !" & vbCr & vbCr & _
"In the VBA editor select menu Tools/References... " & _
"and identify the missing reference."
For Each aReference In ActiveWorkbook.VBProject.References
If aReference.IsBroken Then
MsgBox aMsg, vbCritical, ActiveWorkbook.Name
End If
Next aReference
End Function

In Access :
+++++++++++

Sub HVL_Find_Missing_References_Access()
Dim aProject As Object
Dim aReference As Object
Dim aMsg as String
aMsg = "Missing reference !" & vbCr & vbCr & _
"In the VBA editor select menu Tools/References... " & _
"and identify the missing reference."
For Each aProject In Application.VBE.VBProjects
For Each aReference In aProject.References
If aReference.IsBroken Then
MsgBox aMsg, vbCritical, aProject.FileName
End If
Next aReference
Next aProject
End Sub

But unfortunately the Excel version does not function in Office-XP. I think
that it perhaps has something to do with security.

What can I do ?
Could the Access VBA be changed to function in Office-XP ?
Can I solve my problem in quite another way ?

Please HELP !

I would be happy (also) to receive answers to :
Helge.V.Larsen(RemoveThis)@Risoe.DK(AndThis)

 

[SA]

Helge:

You are correct that in Excel Xp and later that the security model does not
trust access to the Visual Basic Project by default. You can see this by
going to Tools -> Macro -> Security -> Trusted Sources tab. This has to be
manually checked by the user for your code to work.

That said, it is very unlikely that for the main applications that a
reference to their object libraries (the OLB references) would be missing.
The likely problem is with assuring that the user has ADO 2.8 if you are
relying on functions in that library. Of course, your installer could
include the ADO 2.8 libraries and require their installation to solve that
problem (of course under administrator log on priviledges only.)

HTH