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

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" wrote:

> 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
>
>
>

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
"JMB" <(E-Mail Removed)> wrote in message
news:56E54EBA-4B92-4405-BCD3-(E-Mail Removed)...
> 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" wrote:
>
>> 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
>>
>>
>>