VBE Tools>Reference question

[Ludo]

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

 

[Bob Umlas]

AFAIK you can't do that.
Bob Umlas
Excel MVP

 

[Tom Ogilvy]

http://support.microsoft.com/?id=160647
XL97: How to Programmatically Create a Reference

 

[Chip Pearson]

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]

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)










- Show quoted text -

Thanks for this information.
Will try this this evening.

regards,
Ludo

 

[Ludo]

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)










- 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