How to identify missing references (libraries) using VBA 1

[HelgeLarsen]

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 !

 

[cmmrfrds]

Check thread705-903662

 

[HelgeLarsen]

Thanks to Cmmrfrds for pointing to Thread 705-903662 with some good VBA (although an "On Error Resume Next" is missing in Function FixUpRefs after the reference has been added).


________________________
Helge Larsen