Is it possible to set a reference to a library in another file using VBA in Excel?

[Guest]

Hello, I am using VBA in Excel XP with Windows 2000

Is it possible to set a reference to a library in another file using VBA in Excel

For example, the main program resides in "File A" and it must programmatically set a reference to "Microsoft Outlook 10.0 Object Library" in "File B" for "File B's" use

If this is possible, can someone supply me with the code with which to do it

Thanks much in advance.

 

[Guest]

Hello, while I'm uncertain as to why this reference is a moving target, you can try the following

Sub Tester(
Dim n As Long, z As Boolea
With Workbooks("Book3").VBProject.Reference
For n = 1 To .Coun
If InStr(.Item(n).Description, "Microsoft Outlook") Then GoTo
Next
.AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 9,
z = Not
1: End Wit
Call HitIt(z
End Su

Private Sub HitIt(ByVal z As Boolean
Dim n As Lon
'Do your Stuf

'Remove reference if you set i
If z The
With Workbooks("Book3").VBProject.Reference
For n = 1 To .Coun
If InStr(.Item(n).Description, "Microsoft Outlook") The
.Remove .Item(n
Exit Fo
End I
Next
End Wit
End I
End Su

I currently don't have outlook on me, so this is untested, sorry

Regards
Nate Oliver

 

[Guest]

Thanks Nate. I'm curious, how/where did you get the GUID from? i.e. the line

..AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 9,

Thanks again...

 

[Guest]

Hello Again

You can loop through your references as in my previous post, or have a peak at a specific reference, e.g.

Sub Grab_Reference(
Dim myRef As Objec
'3rd Referenc
Set myRef = ThisWorkbook.VBProject.References(3
If Not myRef Is Nothing The
Debug.Print myRef.Name & vbLf & myRef.Description &
vbLf & """" & myRef.GUID & """" & ", " &
myRef.Major & ", " & myRef.Minor & vbLf &
myRef.fullpat
End I
Set myRef = Nothin
End Su

Where you're in interested in the GUID and Major and Minor references. They can also be found in the registry

Also note, you can set the reference to the lowest Outlook library available using small major & minor versions, e.g.

..AddFromGuid "{00062FFF-0000-0000-C000-000000000046}", 1,

And, further note. Due to Excel XP's default settings, I'm pretty sure you'll need to trust Access to the VB project in your macro security settings. I would not bank on this setting being true for the majority of users. Late Binding may make sense if there's a reason as to why this reference might not be set

Regards
Nate Oliver

 

[Guest]

A little better technique in terms of removing the reference might be to not loop

With ThisWorkbook.VBProject.Reference
.Remove !stdol
End Wit

Where stdole is the name of the library in question

Regards
Nate Oliver

 

[Guest]

Okay. Thanks for the assist and info. Its working perfectly.

 

[Nate Oliver]

You are welcome. Hopefully you noted my non-looping
reference removal post.

Regards,
Nate Oliver

-----Original Message-----
Okay. Thanks for the assist and info. Its working

perfectly.