How to identify missing libraries in Excel (Office-XP) using VBA

[Helge V. Larsen]

I am developing an Excel application that is meant to be used by several
(many?) other persons.

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

I am opening an 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.

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 Excel-2000 :

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."
' The next line is not acepted by Office-XP.
For Each aReference In ActiveWorkbook.VBProject.References
If aReference.IsBroken Then
MsgBox aMsg, vbCritical, ActiveWorkbook.Name
End If
Next aReference
End Function

But unfortunately it is not accepted in Office-XP. Not even if security is
set to Low.

The error message is :
Run-time error '1004':
Programmatic access to Visual Basic Project is not trusted.

When I press the debug button, the VBA editor points to the line with
ActiveWorkbook.VBProject.References

What can I do ?
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)

 

[keepITcool]

access to vb project depends on a settings which is independent
of security level.

in xlXP users can change it from the userinterface.
(2nd tab on the macro security dialog).
In the registry it's here:
HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\10.0\Excel\Security
(xl needs a restart is you change it via registry api or wscript.shell)

Unfortunately...from xl2003
the checkbox in the dialog is greyed and the registry key has moved to
HKLM.it must be set via policies or with regedit by someone allowed to
access HKLM registry keys

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\11.0\Excel\Security
DWORD : AccessVBOM =1



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Helge V. Larsen wrote :

 

[quartz]

Helge,

This may or may not help, but make sure that you have "Trust access to
visual basic project" is checked in [Tools] [Macro] [Security]. This check
box allows your code to manipulate modules and references.

I'm using XL 2003 and recently migrated from XP and I have code similar to
yours and it worked for me under both versions.

Also, check into late binding which allows you to run your code in many
instances without requiring ANY references at all. Check out help on
"GetObject" and "CreateObject".

HTH.