Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Extensibility Library - 97 to 2002
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Extensibility Library - 97 to 2002
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? "Bob Phillips" wrote: 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. -- HTH Bob Phillips "George J" <George wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Extensibility Library - 97 to 2002
Trust access to visual basic project in the macro security settings
George J wrote: 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? "Bob Phillips" wrote: 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. -- HTH Bob Phillips "George J" <George wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Extensibility Library - 97 to 2002
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) -- HTH Bob Phillips "George J" wrote in message ... 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? "Bob Phillips" wrote: 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. -- HTH Bob Phillips "George J" <George wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Extensibility Library - 97 to 2002
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Extensibility Library - 97 to 2002
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" wrote: 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) -- HTH Bob Phillips "George J" wrote in message ... 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? "Bob Phillips" wrote: 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. -- HTH Bob Phillips "George J" <George wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Extensibility Library - 97 to 2002
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 -- HTH Bob Phillips "George J" wrote in message ... 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" wrote: 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) -- HTH Bob Phillips "George J" wrote in message ... 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? "Bob Phillips" wrote: 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. -- HTH Bob Phillips "George J" <George wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Extensibility Library - 97 to 2002
Bob Phillips wrote:
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. --- David |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB Extensibility Library - 97 to 2002
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 "John Smith" wrote: Bob Phillips wrote: 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. --- David |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VB Extensibility library and "late binding" | Excel Discussion (Misc queries) | |||
Lost VBA extensibility library reference | Excel Programming | |||
VBA extensibility library for deleting forms, modules, code etc... | Excel Programming | |||
VBA extensibility Library | Excel Programming | |||
VBA extensibility library for deleting forms, modules, code etc... | Excel Programming |