VB Extensibility Library - 97 to 2002

[George J]

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

 

[Bob Phillips]

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.

 

[George J]

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?

 

[David Welch]

Trust access to visual basic project in the macro security settings

 

[Bob Phillips]

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)

 

[George J]

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.

 

[George J]

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

 

[Bob Phillips]

I did miss a bit, but that bit is fine on my system

Sub removeCode()
Const vbext_ct_StdModule As Long = 1
Const vbext_ct_ClassModule As Long = 2
Const vbext_ct_MSForm As Long = 3
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

 

[John Smith]

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.

Hi,

I don't thing there is any way around using the VBProject and not
checking the Trust access to visual basic check box. This is true even
when the project is signed. So signing won't help you (except with the
normal message box).

If you did it outside VBA then you could use send keys to the window, ie

Application.Sendkeys("%tms", true)
Application.Sendkeys("%tv", true)

However, the second sendkeys would fail.

So you could use the handle of the window and send to that

xlhandle = FindWindowEx(0,0,"XLMAIN","")
SendMessage(xlhandle, WM_SYSCHAR, Asc("t"), somenumberhere)
SendMessage(xlhandle, WM_SYSCHAR, Asc("m"), somenumberhere)
SendMessage(xlhandle, WM_SYSCHAR, Asc("s"), somenumberhere)

securityhandle = FindWindowEx(xlhandle, 0, "", "Security")
SendMessage(xlhandle, WM_SYSCHAR, Asc("t"), somenumberhere)
SendMessage(xlhandle, WM_SYSCHAR, Asc("v"), somenumberhere)

but this won't work either! The second FindWindowEx function will fail,
probably because of a security feature. You also can't loop through all
the windows in the system, its as though it doesn't exist.

But what you can do is subclass the window. Then on each WM_ACTIVATE
event, test the caption using GetWindowText. If the caption is
"Security", then thats your window. Then you can use the above
SendMessage code.

Its just too much of a hastle.

 

[George J]

yikes

Thanks for that. If its too much hastle for them - i'll tell them to get
their secretarys to do it for them.

thanks to all who helped - was going demented trying to figure it out.
George