Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Sheet code using VBA - question for Chip Pearson
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Question on a Chip Pearson macro | Excel Programming | |||
Question to Chip Pearson | Excel Programming | |||
Chip Pearson: A Question about RegServ32 and DLLs :) | Excel Programming | |||
CHIP PEARSON - THANX | Excel Programming | |||
Chip Pearson or someone | Excel Programming |