I wouldn't use the file name since you cannot be sure that the DLL will
always be in the same place on one machine as it is on another machine.
Instead, use the AddFromGUID method.

On Error Resume Next
ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{0002E157-0000-0000-C000-000000000046}", _
Major:=0, Minor:=0

The problem here is that if there is no reference to VBIDE and VBA decides
to compile the code before the code to add the reference is run, you'll get
compiler errors (that cannot be trapped with an On Error statement).


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"BobR" wrote in message
...
Chip, would it be possible to have the code (on startup) to go to
C:\Program Files\Common Files\microsoft shared\VBA
VBA6\VBE6EXT.OLB and activate the Extensibility reference automatically
or will that work. I too have the problem Otto has in losing the
connection in that workbook each time. I had read about a number but
couldn't find one, only the file and location.

Or is there another way for that to happen????
Thanks
BOB


"Chip Pearson" wrote in message
...
I noticed that setting the Extensibility reference does not stay put
when I shut down Excel and reopen it. Is that normal behavior?

No, that is not normal behavior. The reference will come and go as that
workbook is opened or closed, but that workbook should always have that
reference set. References are part of the workbook, so every workbook
that needs to use a reference must have it checked in the VBProject.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Otto Moehrbach" wrote in message
...
Chip
I noticed that setting the Extensibility reference does not stay put
when I shut down Excel and reopen it. Is that normal behavior? Thanks
for your time. Otto
"Chip Pearson" wrote in message
...
Try something like the following. You'll need a reference to the
Extensibility library.

Sub CopySheetModule()
Dim ThisVBP As VBIDE.VBProject
Dim ThatVBP As VBIDE.VBProject

Dim ThisVBComp As VBIDE.VBComponent
Dim ThatVBComp As VBIDE.VBComponent

Dim S As String

Set ThisVBP = ThisWorkbook.VBProject
Set ThatVBP = Workbooks("Book2").VBProject '<<< CHANGE

Set ThisVBComp = ThisVBP.VBComponents("Sheet1") '<<< CHANGE
Set ThatVBComp = ThatVBP.VBComponents("Sheet1") '<<< CHANGE

With ThisVBComp.CodeModule
S = .Lines(1, .CountOfLines)
End With

With ThatVBComp.CodeModule
.DeleteLines 1, .CountOfLines
.AddFromString S
End With

End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"Otto Moehrbach" wrote in message
...
Excel XP and Win XP
I have used code furnished by Chip Pearson to copy a module from
one file to another. I believe, maybe I'm wrong, that this code deals
with regular modules only. My question now is: Can this be done with
a sheet or workbook module? And how? Thanks for your time. Otto