Unanswered: Anyway to manage Excel templates? Like easily update code across the board
I'm intersted in setting up a time keeping system. Right now I have a blank spreadsheet with many macros for hours of employees to be entered for each project.
My blank template will have all the headings but no values. I'm planning to take some values like the project name, date etc from excel and use that to name the files when saved.
My question is though is there anyway to easily manage the code. As from reading an excel book I came upon this little gem.
This reduces the usefulness of Excel templates considerably, for if we create a template that contains lots of code, for instance, then each workbook that is based on that template will contain its own copy of that code. This can be a major waste of space and can also make it very difficult to maintain and upgrade the code. For these reasons, I generally avoid using Excel templates whenever possible.
I was hoping to save each project, and then write some kind of vba code in Access or maybe Excel to produce monthly reports on each employee etc.
But will I have a major headache any time I decide to add any new features? I guess as long as I have bug free code to start with, I should be ok and any revisions can just be for any future projects but it's somewhat frightening.
And if you've read this post this far, any advice on how I would go about opening certain .xls files and then parsing them for relevant information. I'm comfortable with the logic to search through one sheet and get the total hours for any employees, but I don't really have any experience opening multiple files and doing this.
The problem with code attached to templates is that any changes will not go with the previous work; and you would have to update every previous workbook identically.
I have found that if I use code often I will put this into my Personal.xls file (I assume you know how to do that, but if not post back). Others set up a Macro.xls file that acts the same as the Personal.xls but is only loaded when you want it. I prefer the Personal.xls approach - but that's just me. That way it will always be available for any project. And if I have to update the code I only update one time to insure consistency across all work. This works especially well if you are the only one to use the code.
old, slow, and confused
but at least I'm inconsistent!