Copying code behind from a sheet to a sheet in another workbook.

[Nayan]

Hello,

(Environment Excel 2003)

I have a parent workbook (Say WB1.XLS) which contains code behind in its
sheet1.
I made a copy of WB1 say WB2.XLS.

Now the code behind in sheet1 in WB1 changed.

I need a programm that can compare the code between WB1.sheet1 and WB2.sheet1.

if vba code is different then
copy the code from WB1.Sheet1 to WB2.sheet1

At run time I can export/Import the modules (.BAS, .FRM, .CLS) . I tried
exporting sheets but they are stored as .CLS extentions and when I import
these sheets they come back as class modules (.CLS) 'not useful'.

With code I can get the count of lines of code for each sheet (which I can
use to compare ) but how to get the code itself from the sheet and once i get
the code how to insert that code in another sheet to overwrite ?

Any suggestions or ideas.

Thanks in advance

Nayan

 

[JNW]

If you know that the code in WB1 changed and you want to change the code in
WB2 only if WB1 changed, then you don't need to check the code of WB2. I
presume you might have multiple workbooks you are looking to change. You
could set up code that will loop through each individual workbook and make
the change.

Let me know if I'm thinking about this the wrong way.

 

[Nayan]

JNW,
Thanks for the reply.
Yes I have multiple copies of a parent WB.

I do not have any information before hand if the code between WB1.sheet1 and
WB2.sheet1 is diff or same. So I need to check if its same or not before
replicating code fron one sheet to another one.

They come to me from an external source. I just need to sink the code
between these sheets

Please let me know if more information is needed.

Thanks for your time

Nayan

 

[JNW]

To my knowledge there is no way to compare code from two workbooks line by
line, or even command by command.

With what I understand, it seems that there are two options.

1) replace the code in the the workbooks on a scheduled basis. i.e. every
Friday run code that will change the workbooks.

2) have the third party create the code as an add-in. This way, only the
add-in you receive changes and the workbooks don't have to.

 

[Nayan]

Hello JNW,

If I have to follow point 1 as mentioned below, How do I programmatically
copy code from one worksheet to another one?

(1. replace the code in the the workbooks on a scheduled basis. i.e. every
Friday run code that will change the workbooks.)

Thanks

Nayan

 

[JNW]

Chip Pearson explains it way better than I ever could!

Check out this site. It's got everything you need to know to get you
started editing the VBE.
(a
herf:http://www.cpearson.com/excel/vbe.htm>http://www.cpearson.com/excel/vbe.htm</a>

 

[Nayan]

Thanks for reply.

I have figured this out.

FYI- I am exporting the code in a text file and importing using VBproject
object model

Thanks again

Nayan