Update Excel 2007 code with code

I need to update the code for worksheet 'evals' in all workbooks named 'MyBook' on the network.  I can get the workbooks and sheets no problem.  I also have a master workbook with a worksheet that has the updated code.

What do I use to update the code from a master worksheet to these worksheets in excel 2007?

thx.
LVL 1
Field Marshall BobAsked:
Who is Participating?
 
dlmilleConnect With a Mentor Commented:
I think so.  If you know the exact code, even at the sheet level, I'd suggest deleting all code, then bringing in the .bas files to update the modules.  

I believe you can even import a sheet codepage with .cls file - would need some experimentation as I've exported codepages, but not imported (it brings them in as class modules) so not sure how that'd work.

Alternatively, delete the codepage data then read in/write out to the codepage directly...

Dave
0
 
dlmilleCommented:
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
0
 
Field Marshall BobAuthor Commented:
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.

0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Field Marshall BobAuthor Commented:
My lowest common denominator on version around here is Office 2007.  I am programming to 2007 and 2010.
0
 
dlmilleCommented:
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
0
 
dlmilleCommented:
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
0
 
Field Marshall BobAuthor Commented:
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!
0
 
dlmilleCommented:
Ok - advise if you need further assistance, as I've had some experience with this in my current project.
0
 
Field Marshall BobAuthor Commented:
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?
0
 
Field Marshall BobAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.