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
