Chip Pearson's code to modify code

[Otto Moehrbach]

Excel XP & Win XP
I need to copy a module from one file to another. I went to Chip's site and
copied the code to do this. In that code,
"FromVBProject" is defined as "The VBProject that contains the module to be
copied." and
"ToVBProject" is the destination VBProject.
I took "VBProject" to be the name of the file, as in "Toolbar.xls".
Apparently that is not so because I am getting a "Type Mismatch" error.
Chip's code is a Function and my little macro simply calls that function, as
in:
Sub CopyTheModule()
MsgBox CopyModule("CreateToolbar", "Toolbar.xls", "RB Full
Database.xls", "False")
End Sub

Function CopyModule(ModuleName As String, _
FromVBProject As VBIDE.VBProject, _
ToVBProject As VBIDE.VBProject, _
OverwriteExisting As Boolean) As Boolean

The error is in "Toolbar.xls" in the sub and I imagine the error would move
on to the next file name as well.
Question: What am I doing wrong? Thanks for your time. Otto

 

[Guest]

You need to pass the VBProjects to the function (or an object reference to
the vbprojects), not just the workbook or workbook names.

I've not worked extensively with the VBProject through code, but I think
something like this should work:

MsgBox CopyModule("CreateToolbar", Workbooks("Toolbar.xls").VBProject,
Workbooks("RB Full Database.xls").VBProject, "False")

and I believe you will need to set up a reference to the Microsoft Visual
Basic Extensibility Library (Tools/References).

 

[Otto Moehrbach]

JMB
Thanks for responding. I'll try that in the morning. You're right about
the reference and I have already done that. Thanks again. Otto