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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

Hello Chip, is there a way to place into the code the activation of the
reference that you speak about to the Extensibility library.
Thanks
BOB R


"Chip Pearson" <(E-Mail Removed)> wrote in message
news:88883672-73C8-41E1-9330-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>

Chip
I'm learning things here that I've never been into before. And I thank
you for that.
The code you gave me is for a sheet module and I can put it to good use.
How would I modify that code to work with the Workbook module? Thanks for
your time. Otto
"Chip Pearson" <(E-Mail Removed)> wrote in message
news:88883672-73C8-41E1-9330-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>

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" <(E-Mail Removed)> wrote in message
news:88883672-73C8-41E1-9330-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>

> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:88883672-73C8-41E1-9330-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:777977A5-BEC7-4025-BF9B-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:88883672-73C8-41E1-9330-(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>>
>>
>>
>

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" <(E-Mail Removed)> wrote in message
news:AUEZi.1621$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:777977A5-BEC7-4025-BF9B-(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:88883672-73C8-41E1-9330-(E-Mail Removed)...
>>>> 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" <(E-Mail Removed)> wrote in message
>>>> news:(E-Mail Removed)...
>>>>> 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
>>>>>
>>>>
>>>
>>>
>>
>
>

Thanks Chip, that explains it. I thought the setting was global, not
workbook peculiar. Otto
"Chip Pearson" <(E-Mail Removed)> wrote in message
news:777977A5-BEC7-4025-BF9B-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:88883672-73C8-41E1-9330-(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>>
>>
>>
>

Chip
I had another question in this thread regarding altering the code you
gave me for manipulating code in a sheet module, to make it work with a
Workbook module. You may have overlooked that query and I would appreciate
your help with this. Here is the query:

Chip
I'm learning things here that I've never been into before. And I thank
you for that.
The code you gave me is for a sheet module and I can put it to good use.
How would I modify that code to work with the Workbook module? Thanks for
your time. Otto
"Chip Pearson" <(E-Mail Removed)> wrote in message
news:777977A5-BEC7-4025-BF9B-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:88883672-73C8-41E1-9330-(E-Mail Removed)...
>>> 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" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> 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
>>>>
>>>
>>
>>
>