Add a line to Chip's code and give it another try:
''''''''''''''''''''''''''''''''''''''''''''''
' Document modules (SheetX and ThisWorkbook)
' cannot be removed. So, if we are working with
' a document object, delete all code in that
' component and add the lines of FName
' back in to the module.
''''''''''''''''''''''''''''''''''''''''''''''
Set VBComp = Nothing '<--- added
Set VBComp = ToVBProject.VBComponents(CompName)
There was a slight bug in Chip's routine.
Janie wrote:
Did you you suggested. Nothing happens. On the plus side I don't get the
error message. But ... I don't get the module in the other file, either.
I am overdosing on chocolate ... a sure sign I am getting v-e-r-y
frustrated. What the heck am I doing wrong???
"Dave Peterson" wrote:
Try not running it from the immediate window.
Create a small subroutine:
Option Explicit
Sub testme()
dim ProjectA as VBIDE.vbproject
dim Projectb as VBIDE.vbproject
dim ret as boolean
Set ProjectA = Application.Workbooks("OldBook.xls").VBProject
Set ProjectB = Application.Workbooks("NewBook.xls").VBProject
ret = CopyModule("basMyRoutine",ProjectA,ProjectB,True)
end sub
Janie wrote:
I am STILL getting the same ByRef argument type mismatch error. Using your
recommendation, here's how the function starts:
Function CopyModule(ModuleName As String, _
FromVBProject As VBIDE.VBProject, _
ToVBProject As VBIDE.VBProject, _
OverwriteExisting As Boolean) As Boolean
Dim VBComp As VBIDE.VBComponent
Dim FName As String
Dim CompName As String
Dim S As String
Dim SlashPos As Long
Dim ExtPos As Long
Dim TempVBComp As VBIDE.VBComponent
Set ProjectA = Application.Workbooks("OldBook.xls").VBProject
Set ProjectB = Application.Workbooks("NewBook.xls").VBProject
in the Immediate Window I have:
?CopyModule("basMyRoutine",ProjectA,ProjectB,True)
the sound you hear is my head banging against the wall
"Dave Peterson" wrote:
You're going to need to set those ProjectA and ProjectB variables somehow.
Look at Chip's site again and you'll see where I copied that line from.
Janie wrote:
well, that step wasn't in Chip's complete code which is what I copied and
pasted into a module. But what the heck --- gave it a try as you suggested,
nope, still get the same error message when I try to run from my Immediate
Window.
Dave, Joel or anybody else who has an idea -- Go to Chip's site
(http://cpearson.com/excel/vbe.htm) and copy the code from the section "Copy
A Module From One Project To Another" ... it begins after the the line:
The complete code is shown below:
and then tell me if it works for you.
Either something is missing (doubtful) or I am overlooking something
(probable)
"Dave Peterson" wrote:
You didn't share how you set the FromVBProject or the ToVBProject variables.
Did you use something like this (modified from Chip's page):
Set FromVBProject = Application.Workbooks("Book1.xls").VBProject
Janie wrote:
I'm using Chip Pearson's CopyModule function found at
http://cpearson.com/excel/vbe.htm. I must say, I am thrilled to find this
bit. However, I am having a problem.
I have installed the VBA Extensibility library. Then I copied the sample
code for CopyModule and pasted it into my Excel module. But when I try to
use the function, I get the error "ByRef argument type mismatch". Any hints?
here is the command line of the function:
CopyModule(ModuleName As String, _
FromVBProject As VBIDE.VBProject, _
ToVBProject As VBIDE.VBProject, _
OverwriteExisting As Boolean) As Boolean
Here's what I used in my Immediate Window
?CopyModule("basMyRoutine", ProjectA, ProjectB, True)
If I place the ProjectA and ProjectB inside quotes, I get a Type Mismatch
error, so I don't think that is the cause of my problem.
I welcome any recommendations. And thanks in advance.
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson