Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to identify missing libraries in Excel (Office-XP) using VBA
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 : (AndThis) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to identify missing libraries in Excel (Office-XP) using VBA
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 he HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\10.0\E xcel\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 : 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 : (AndThis) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Identify missing numbers in list | Excel Worksheet Functions | |||
Code libraries usable in all of Office? | Excel Programming | |||
Identify missing record numbers | Excel Discussion (Misc queries) | |||
Identify missing criteria | Excel Worksheet Functions | |||
Office Object Libraries | Excel Programming |