Solved

Update Excel 2007 code with code

Posted on 2011-09-24
10
155 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:Field Marshall Bob
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 36594061
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
 
LVL 1

Author Comment

by:Field Marshall Bob
ID: 36598631
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
 
LVL 1

Author Comment

by:Field Marshall Bob
ID: 36599605
My lowest common denominator on version around here is Office 2007.  I am programming to 2007 and 2010.
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 42

Expert Comment

by:dlmille
ID: 36600509
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
 
LVL 42

Expert Comment

by:dlmille
ID: 36600517
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
 
LVL 1

Author Comment

by:Field Marshall Bob
ID: 36600656
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
 
LVL 42

Expert Comment

by:dlmille
ID: 36600663
Ok - advise if you need further assistance, as I've had some experience with this in my current project.
0
 
LVL 1

Author Comment

by:Field Marshall Bob
ID: 36600698
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
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 36601314
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
 
LVL 1

Author Closing Comment

by:Field Marshall Bob
ID: 36817246
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

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question