Solved

Update Excel 2007 code with code

Posted on 2011-09-24
10
150 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
  • 5
  • 5
10 Comments
 
LVL 41

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
 
LVL 41

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 41

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 41

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 41

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now