Hi,

I am working on a VBA project that will systematically update VBA code
in multiple workbooks based on the code in another workbook. Chip
Pearson's "Programming in the VBA Editor" page at http://www.cpearson.com/excel/vbe.aspx
has been very helpful with this.

But I have come across a problem: the CopyModule function gives
unexpected results when the VBComponent in question is a Sheet module
or the ThisWorkbook module. In VBA lingo, it is of the type
"vbext_ct_Document". Such a module cannot be removed via the Remove
method. Hence, the Remove method in the CopyModule function fails
(without any indication - there is no error check there), and the
source module is imported as a class module with a "1" appended to the
module name. This is most undesirable.

So what is the fix for this situation?
I tried the following simple code in a code module:

Sub foo()
Dim proj As VBIDE.VBProject
Dim comp As VBIDE.VBComponent
Set proj = ThisWorkbook.VBProject
Set comp = proj.VBComponents("Sheet2")
With comp.CodeModule
.DeleteLines 1, .CountOfLines
.AddFromFile "D:\mypath\Sheet2.cls"
End With
End Sub

....but the problem here is that the AddFromFile method blindly brings
in the full contents of the .cls file. So, you get the undesirable
meta-fluff like:

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
End

....which is always saved in an exported module file, but is illegal
inside the code pane.

The only other way I can see around this issue is to find, delete and
insert Procedures one by one. It seems feasible, though rather
hackish.

Any other ideas?

Cheers,
-Basilisk96