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

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.


"Helge V. Larsen" wrote:

> 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)
>
>
>
.