Update Excel 2007 code with code

Do you want to replace the "evals" sheet from the master workbook in all the worksheets?  A copy paste, or a delete in the "slave" workbooks of the "eval" sheet then a copy of the "evals" tab into the "slave" workbooks?  Is this what you're looking for?

>>in all workbooks named 'MyBook' on the network.  

Are there a bunch of workbooks scattered around on different servers, different folders?  If they are in the same folder they can't all be called 'MyBook'.

Do you need to be prompted for directory/path of "MyBook" - or do you want to search a server - all folders, until it finds "MyBook" - or, are all your files in one folder and they start with "MyBook_somthing.xlsx"

Is this Excel 2003, 2007, 2010?

Please be a bit more descriptive in what you need.  Providing a solution is the easy part - stating the problem to avoid recycles is the challenge, if you're up to it :)

Dave

Thanks for the comment dlmille -
>>Do you want to replace the evals sheet?  No.  I want to keep the sheet and all its data intact.  I only want to replace the underlying code.

>>Are the workbooks scattered on different servers?  No.  All the workbooks are under a folder called evals and each dept. has a subfolder with a workbook named MyBook.  I can already iterate through all the files in the subfolders and open only those that match the name.  I can also iterate through all the sheets and other objects in each workbook.

What I want to do is take all the code in MyBook for the sheet 'Employees' and update it with the code from my development version.  I don't want to have to go to each file every time I make a change, great or small.

I was looking at the VBAProject.code stuff but it looks like I have to iterate through procedure names and once I have the procedure how do I update them?  What if they aren't there, will it create it?   What about functions and private subs?  I would rather just update the code for the worksheet all at once if possible.

My lowest common denominator on version around here is Office 2007.  I am programming to 2007 and 2010.

ok - At first I thought you were talking about VBA code, then I thought you might be talking about code in a worksheet (some people use that term for data).

I do this type of stuff from time to time.  Is your VBA code in the sheet data, or is it in public modules?

Is the code exactly the same in each workbook, or are there variations?  E.g., can you do a wholesale replace of each workbook from your source?

Your answers to these questions will help in solution.  For example, if all the VBA code is in public modules and its consistent, you can open each workbook, remove the public modules, then input from a common .bas file, then save.

Let me know,

Dave

PS - here's my source for intel on programming in the VBA editor, and it has served me well...

http://www.cpearson.com/excel/vbe.aspx

Dave

Hi Dave,

Thanks for the help on this and sorry for the confusion.  

I think that may do it.  I looked at cpearsons stuff a few days ago but could not get it to work.  I just saw the line about giving programmatic access to the VBE is a security feature.  I turned that on and at least things are running.  I will look at this in more detail before closing the question.

Thanks Man!

Ok - advise if you need further assistance, as I've had some experience with this in my current project.

BTW - Here are the answers to your earlier questions:

>>Is your VBA code in the sheet data, or is it in public modules?  Right now it is part of the sheet 'eval'.  I can move it to public modules as long as I fix scope and lifetime for my objects and variables.

>>Is the code exactly the same in each workbook, or are there variations?  E.g., can you do a wholesale replace of each workbook from your source? - The eval book has data in it that is unique but the code is (or rather should be) the same for all.  Keeping them the same is part of the reason I am writing this code.

So it sounds like I can move/remove .bas's around no problem but I have to enumerate through the sub routines of a sheet.  Is that true?

Thanks for the help on this.  It worked spot on!  It is important to reference the extensibility object which I missed the first time I visited his site.