Re: Volatile Extensibility Reference?

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" <chip@xxxxxxxxxxxx> wrote in message
news:777977A5-BEC7-4025-BF9B-1014791F9C53@xxxxxxxxxxxxxxxx
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" <ottokmnop@xxxxxxxxxxx> wrote in message
news:OQcXUgGJIHA.4584@xxxxxxxxxxxxxxxxxxxxxxx
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" <chip@xxxxxxxxxxxx> wrote in message
news:88883672-73C8-41E1-9330-F4CA1686173A@xxxxxxxxxxxxxxxx
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" <ottokmnop@xxxxxxxxxxx> wrote in message
news:eJjRDz8IIHA.1324@xxxxxxxxxxxxxxxxxxxxxxx
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
*** or workbook module? And how? Thanks for your time. Otto







.