VB Extensibility Library - 97 to 2002

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I had this macro that worked in XL97. Our IT department just upgraded
everyone to XL2002 .


Sub MakeLibrary()
On Error Resume Next 'if it already exits
ThisWorkbook.VBProject.References _
.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 0
'This adds the VBA Extensibility library reference
End Sub


Can anyone tell me how i get the reference to activate via code now?
(Microsoft Visual Basic for Applications Extensibility 5.3)

I've done a bit of reading on this and i think it requires a change in the
security settings. Any way to do this via programming? - Never used anything
but XL97 so i'm a bit lost now. As there will be a number of users who know
nothing of excel using this, really need a coded workaround.

thanks
George
 
Can't see you will get a programming solution. The security settings will
have to be changed by the user, if they can, or by an administrator.
 
If the macro is digitally signed, will this make it a "trusted source" and
allow the changes? Also tried looking at late binding - but can't figure
anything out for that. Trying to get this macro to run at the end which is
why i need the reference.

Sub removeCode()

Dim VBComp As VBIDE.VBComponent
Dim VBComps As VBIDE.VBComponents

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
..DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

Any ideas anyone?
 
That can run late bound like so

Sub removeCode()

Dim VBComp As Object
Dim VBComps As Object

Set VBComps = ActiveWorkbook.VBProject.VBComponents

For Each VBComp In VBComps
Select Case VBComp.Type
Case vbext_ct_StdModule, vbext_ct_MSForm, _
vbext_ct_ClassModule
VBComps.Remove VBComp
Case Else
With VBComp.CodeModule
..DeleteLines 1, .CountOfLines
End With
End Select
Next VBComp

End Sub

but that will do nothing re the security settings. You could get it
digitally signed, but you would still need the clients to accept your
signature as atrusted signature (although they only need to do it once)
 
David, will a digital signature allow me to do this automatically?

As there are alot of users that do not use excel regularly i am not wanting
them to go through this process every time they open the workbook. Will this
only need to be set once on their system, or every time they open the
workbook.

What i really need is a way for them to accept my macro (enable) which will
also let the security settings read my macros as a trusted source.

AAAAaarrgh
i really need help with this.
 
Thanks for that Bob.

Unfortunately when it gets to Set VBComps =
ActiveWorkbook.VBProject.VBComponents an error comes up.
"Method VBProject of object failed"

Probably because the VBE reference is not loaded. :(

With the digital signature, there is no problem with the users accepting
this. But if i am a trusted signature, will this be able to effect the
trusted source?

Does that even make sense???

thanks
George
 
Back
Top