Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Location
    Canada
    Posts
    25

    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.

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    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!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •