Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBE ToolsReference question
Hi,
I do have a question abouth the Tools - Reference in the VB editor (Excel 2000). Is it possible to add programmaticaly references in the Workbook_open event in run time? If so, how to do so? Question comes from some problems i encounter in distributing my utility on 2 other PC's, located at around 70 miles from my home. Normally, in design time, you select the nessesarry library's to work with (example MSMAPI.OCX, FM20.DLL). but the other PC's does'nt have those librarys selected. So, the idea rise to write some code in the Workbook_open event to find the location of the nessesarry librarys (OCX, DLL, ...) and select them. This would be more convienient then hanging on the phone with someone who knows nothing abouth the VBE and selecting those library's. There's also a problem if those librarys doesn't appear in the list and have to be found manually. Any help welcome. Regards, Ludo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBE ToolsReference question
AFAIK you can't do that.
Bob Umlas Excel MVP "Ludo" wrote in message oups.com... Hi, I do have a question abouth the Tools - Reference in the VB editor (Excel 2000). Is it possible to add programmaticaly references in the Workbook_open event in run time? If so, how to do so? Question comes from some problems i encounter in distributing my utility on 2 other PC's, located at around 70 miles from my home. Normally, in design time, you select the nessesarry library's to work with (example MSMAPI.OCX, FM20.DLL). but the other PC's does'nt have those librarys selected. So, the idea rise to write some code in the Workbook_open event to find the location of the nessesarry librarys (OCX, DLL, ...) and select them. This would be more convienient then hanging on the phone with someone who knows nothing abouth the VBE and selecting those library's. There's also a problem if those librarys doesn't appear in the list and have to be found manually. Any help welcome. Regards, Ludo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBE ToolsReference question
http://support.microsoft.com/?id=160647 XL97: How to Programmatically Create a Reference -- Regards, Tom Ogilvy "Ludo" wrote: Hi, I do have a question abouth the Tools - Reference in the VB editor (Excel 2000). Is it possible to add programmaticaly references in the Workbook_open event in run time? If so, how to do so? Question comes from some problems i encounter in distributing my utility on 2 other PC's, located at around 70 miles from my home. Normally, in design time, you select the nessesarry library's to work with (example MSMAPI.OCX, FM20.DLL). but the other PC's does'nt have those librarys selected. So, the idea rise to write some code in the Workbook_open event to find the location of the nessesarry librarys (OCX, DLL, ...) and select them. This would be more convienient then hanging on the phone with someone who knows nothing abouth the VBE and selecting those library's. There's also a problem if those librarys doesn't appear in the list and have to be found manually. Any help welcome. Regards, Ludo |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBE ToolsReference question
Assuming that the files exist on the computer and have been registered with Windows, you don't need to try to find the actual files. Just use the GUID. ' MSFORMS reference ThisWorkbook.VBProject.References.AddFromGuid _ GUID:="{0D452EE1-E08F-101A-852E-02608C4D0BB4}", _ Major:=0, Minor:=0 You can get the GUIDs for the various libraries with code like in your master workbook. Debug.Print ThisWorkbook.VBProject.References("MSFORMS").GUID If you really do want to go down the file route, use ThisWorkbook.VBProject.References.AddFromFile _ Filename:="C:\Whatever\FileName.dll" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Ludo" wrote in message oups.com... Hi, I do have a question abouth the Tools - Reference in the VB editor (Excel 2000). Is it possible to add programmaticaly references in the Workbook_open event in run time? If so, how to do so? Question comes from some problems i encounter in distributing my utility on 2 other PC's, located at around 70 miles from my home. Normally, in design time, you select the nessesarry library's to work with (example MSMAPI.OCX, FM20.DLL). but the other PC's does'nt have those librarys selected. So, the idea rise to write some code in the Workbook_open event to find the location of the nessesarry librarys (OCX, DLL, ...) and select them. This would be more convienient then hanging on the phone with someone who knows nothing abouth the VBE and selecting those library's. There's also a problem if those librarys doesn't appear in the list and have to be found manually. Any help welcome. Regards, Ludo |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBE ToolsReference question
On Oct 1, 7:32 pm, "Chip Pearson" wrote:
Assuming that the files exist on the computer and have been registered with Windows, you don't need to try to find the actual files. Just use the GUID. ' MSFORMS reference ThisWorkbook.VBProject.References.AddFromGuid _ GUID:="{0D452EE1-E08F-101A-852E-02608C4D0BB4}", _ Major:=0, Minor:=0 You can get the GUIDs for the various libraries with code like in your master workbook. Debug.Print ThisWorkbook.VBProject.References("MSFORMS").GUID If you really do want to go down the file route, use ThisWorkbook.VBProject.References.AddFromFile _ Filename:="C:\Whatever\FileName.dll" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consultingwww.cpearson.com (email on the web site) "Ludo" wrote in message oups.com... Hi, I do have a question abouth the Tools - Reference in the VB editor (Excel 2000). Is it possible to add programmaticaly references in the Workbook_open event in run time? If so, how to do so? Question comes from some problems i encounter in distributing my utility on 2 other PC's, located at around 70 miles from my home. Normally, in design time, you select the nessesarry library's to work with (example MSMAPI.OCX, FM20.DLL). but the other PC's does'nt have those librarys selected. So, the idea rise to write some code in the Workbook_open event to find the location of the nessesarry librarys (OCX, DLL, ...) and select them. This would be more convienient then hanging on the phone with someone who knows nothing abouth the VBE and selecting those library's. There's also a problem if those librarys doesn't appear in the list and have to be found manually. Any help welcome. Regards, Ludo- Hide quoted text - - Show quoted text - Thanks for this information. Will try this this evening. regards, Ludo |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBE ToolsReference question
On 1 okt, 19:32, "Chip Pearson" wrote:
Assuming that the files exist on the computer and have been registered with Windows, you don't need to try to find the actual files. Just use the GUID. ' MSFORMS reference ThisWorkbook.VBProject.References.AddFromGuid _ GUID:="{0D452EE1-E08F-101A-852E-02608C4D0BB4}", _ Major:=0, Minor:=0 You can get the GUIDs for the various libraries with code like in your master workbook. Debug.Print ThisWorkbook.VBProject.References("MSFORMS").GUID If you really do want to go down the file route, use ThisWorkbook.VBProject.References.AddFromFile _ Filename:="C:\Whatever\FileName.dll" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consultingwww.cpearson.com (email on the web site) "Ludo" wrote in message oups.com... Hi, I do have a question abouth the Tools - Reference in the VB editor (Excel 2000). Is it possible to add programmaticaly references in the Workbook_open event in run time? If so, how to do so? Question comes from some problems i encounter in distributing my utility on 2 other PC's, located at around 70 miles from my home. Normally, in design time, you select the nessesarry library's to work with (example MSMAPI.OCX, FM20.DLL). but the other PC's does'nt have those librarys selected. So, the idea rise to write some code in the Workbook_open event to find the location of the nessesarry librarys (OCX, DLL, ...) and select them. This would be more convienient then hanging on the phone with someone who knows nothing abouth the VBE and selecting those library's. There's also a problem if those librarys doesn't appear in the list and have to be found manually. Any help welcome. Regards, Ludo- Tekst uit oorspronkelijk bericht niet weergeven - - Tekst uit oorspronkelijk bericht weergeven - Hi, Using the file route is verry tricky. My MSMAPI32.OCX is located in a different folder than the one we'll test the utility! So, I tried 2 other routines, one does work, the otherone doesn't On the first one i could return the GUID with the - Debug.Print ThisWorkbook.VBProject.References("MSFORMS").guid - aproach mentioned in your answer. But then i was wondering if the returned GUID would be the same on an other OFFICE version (i use Office 2000) like Office 2003 / Office 2007, so i tried to change the code as in the second example. To prevent working with a hardcoded GUID, i would like to get it in runtime, and this won't work. I get a -subscript out of range error -. Any idea what's wrong? Or isn't it possible to get the GUID in runtime mode? Just want to write a flexible code that works in Excel 2000 as well as a higher version. Any help welcome. Regards, Ludo here follows the code for the working routine (Excel 2000): Sub FindLibraryFiles() Dim ReturnGUID As String Dim Cntr As Integer Const Libraries = 3 Dim LibArray As Variant ' library GUID: ' deze kan je vinden op volgende manier: ' selecteer de bibliotheek(en) via "EXTRA |Verwijzingen" ' de naam van de bibliotheek vind je door op "Objectenoverzicht" te klikken ' in de lijst van bibliotheken vind je de bibliotheek naam die hieronder gebruikt wordt om de GUID te bekomen ' MSFORMS = {0D452EE1-E08F-101A-852E-02608C4D0BB4} ' MSMAPI = {20C62CAE-15DA-101B-B9A8-444553540000} ' Outlook = {00062FFF-0000-0000-C000-000000000046} ' Excel = {00020813-0000-0000-C000-000000000046} ' ' Debug.Print ThisWorkbook.VBProject.References("MSFORMS").guid ' Debug.Print ThisWorkbook.VBProject.References("MSMAPI").guid ' Debug.Print ThisWorkbook.VBProject.References("Outlook").guid ' Debug.Print ThisWorkbook.VBProject.References("Excel").guid LibArray = Array("{0D452EE1-E08F-101A-852E-02608C4D0BB4}", "{20C62CAE-15DA-101B-B9A8-444553540000}", "{00062FFF-0000-0000- C000-000000000046}", "{00020813-0000-0000-C000-000000000046}") For Cntr = 0 To Libraries - 1 ReturnGUID = LibArray(Cntr) On Error GoTo ErrorHandler ThisWorkbook.VBProject.References.AddFromGuid guid:=ReturnGUID, major:=0, minor:=0 Next Cntr Exit Sub ErrorHandler: Select Case Err.Number Case 32813 ' library alreddy activated Resume Next Case 9 MsgBox "DLL or OCX not found!", vbCritical End Select End Sub ------------------------------------------ this one doesn't work (Excel 2000): Sub FindLibraryFiles2() Dim ReturnGUID As String Dim Library As String Dim Cntr As Integer Const Libraries = 3 Dim LibArray As Variant LibArray = Array("MSFORMS", "MSMAPI", "Outlook") For Cntr = 0 To Libraries - 1 Library = LibArray(Cntr) ReturnGUID = ThisWorkbook.VBProject.References(Library).guid '<<<<< subscript out of range error !!!!! On Error GoTo ErrorHandler ThisWorkbook.VBProject.References.AddFromGuid guid:=ReturnGUID, major:=0, minor:=0 Next Cntr Exit Sub ErrorHandler: Select Case Err.Number Case 32813 ' library alreddy activated Resume Next Case 9 'subscript out of range error !!! MsgBox Library & vbCrLf & "DLL or OCX not found!", vbCritical Resume Next End Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Same Office 2003 but different VBA ToolsReference (object ref.) | Excel Programming | |||
Same Office 2003 but different VBA ToolsReference (object ref | Excel Programming | |||
Microsoft.Office.Tools reference | Excel Programming | |||
VBA: Tools - Reference | Excel Programming | |||
Shareware Edwin's Power Tools decompiled and became Excel PowerPlus Tools | Excel Programming |