Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts

    Updating Existing Excel Project

    Hi Pro's,

    Question: If an Excel project is emailed to and individual or business, and later the worksheets have changed and need to be updated, what is the best way to accomplish this?

    For example, an Excel project containing numerous customer names, addresses, and etc needs to be updated with additional names and addresses, how should this be done?

    Is it best to send the complete Excel project (this would include forms, VBA modules, workbooks and etc) or can just the workbook or worksheets be updated in the existing Excel project?

    I hope I explained this clearly.

    Thanks

  2. #2
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,407
    Thanks
    447
    Thanked 405 Times in 377 Posts
    In order to keep it all straight, you will need to make sure that multiple copies, each one different in different ways, aren't produced by this effort; because if that happens, it will be a nightmare trying to keep from overwriting one part of the project when you update another part.

    Here are a couple of ways you can prevent this from happening:

    1. You could keep your Excel project in the cloud, and the particular individual or business could then go to your cloud storage and update only the part they are supposed to update. In this way, your master copy will always be current. Doing it this way does introduce the possibility that someone could update an area that they aren't supposed to, so undesired overwrites are possible.

    In order to prevent this sort of error from creeping in, you would probably need to do an Access database rather than an Excel workbook, because you would have a lot better control over every aspect of it with a database system than with an Excel workbook.

    2. You could copy the specific worksheet that someone is going to update, and then send them only that worksheet. Then, after they have made their updates and emailed it back to you, you could copy it back to the master, updating only that one worksheet in the process. In my opinion, this is the best way to do it, because you are always in total control of the update process. A lot less undesired overwrites will occur in this way. In fact, I think that this is about the only way you can control the process and keep it in Excel.

    Be sure to do regular backups of it, and keep several versions back, so that you can recover should an undesired overwrite occur.
    Last edited by mrjimphelps; 2016-03-29 at 12:29.

  3. The Following User Says Thank You to mrjimphelps For This Useful Post:

    Excelnewbie (2016-03-30)

  4. #3
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Thank you. The info you provided is very helpful. Being relatively new to Excel, I hadn't even considered some of things you pointed out. Most helpful.

    Follow up question: when the end user obtains the update how do they enter the update? Do they cut and paste or is there code or a built in function in Excel to complete the update?

  5. #4
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,407
    Thanks
    447
    Thanked 405 Times in 377 Posts
    It depends on how you have set things up. If you just have an Excel workbook with nothing fancy, or a series of Excel spreadsheets with nothing fancy, then they will just enter the data directly into the spreadsheet. On the other hand, if you have set up macros for data entry, then that is how they will do it.

    I mentioned an Access database. If you decide to go that route, you can set up very detailed control over exactly how they enter the data, much more detailed than you can with Excel. But if you've never done any programming, or if you don't have anyone who is familiar with Access, then I wouldn't recommend going that route, because you will have to deal with a whole lot of issues that you aren't even aware of, issues that you wouldn't even think of without some experience in programming.

    Excel is a powerful tool. My favorite thing about it is, you can keep things relatively simple and straightforward compared to a full-blown database system. The drawback is that you don't have total control over how people interact with the data, so you'll have to weigh that against the advantages of a database system. In your case, I recommend Excel, because you yourself can handle every aspect of it with just a little bit of training and knowledge.
    Last edited by mrjimphelps; 2016-03-29 at 14:57.

  6. The Following User Says Thank You to mrjimphelps For This Useful Post:

    Excelnewbie (2016-03-30)

  7. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    E.N.,

    When dealing with this type of thing I like to take an "Accessish" approach similar to what Jim mentioned but going one step further.

    One thing you learn quickly in Access is to keep the Data separate from the Code. In Access this is done by using what are referred to as Front end and Back end Access files. The front end has all the CODE (e.g. the user interface) and the back end has the Data and ONLY the Data!.

    This can also be accomplished in Excel by placing all your macros (e.g. the user interface) in one .xlsm file and all your Data in another .xlsx file. You start the project by opening the .xlsm file which will then open the .xlsx file. It does take some minor changes to the code but they are easily handled. One other advantage to this approach is that you can have the code offer the user a series of files to open, e.g. one for each branch or similar organizational unit or perhaps one file per year or month. This approach offers the data in easier to manage chunks and it is still easily combined by the code when the whole picture is necessary.

    Another advantage is the distribution of Code changes does nothing to disrupt the data you just send out a new copy of the .xlsm file.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Excelnewbie (2016-03-30)

  9. #6
    2 Star Lounger
    Join Date
    Apr 2015
    Posts
    153
    Thanks
    70
    Thanked 0 Times in 0 Posts
    Thanks to Jim and RG,

    This was helpful information. I will take what you've presented and consult with Prof Google. If Prof G. runs dry, I'll follow up with you experts.

    Thanks

  10. #7
    WS Lounge VIP mrjimphelps's Avatar
    Join Date
    Dec 2009
    Location
    USA
    Posts
    3,407
    Thanks
    447
    Thanked 405 Times in 377 Posts
    For what it's worth, here's what I would do if this were my project: I would create a separate spreadsheet for each client, and it would include only that info that they were to update. I would stress to them that they need to be careful and accurate with their data entry. I would then send them the spreadsheet. When they send it back, I would give the data a quick look-over , to catch any glaring errors, and I would then put it back into my project folder.

    The various spreadsheets would be linked to a master spreadsheet by formulas, wherever links are appropriate.

    I like to keep it real simple, because then it's real easy to manage.

    If we had had this conversation during a previous time in my life, I would have told you that I would set up a complete Access database system, with data entry controls, etc. But over the years, I've come to love simplicity.

    RG's approach is probably a good approach for you, if you can take some time and learn how to do macros, because his approach has many of the advantages and benefits of a full-blown database system, but it's not nearly as complex.

  11. The Following User Says Thank You to mrjimphelps For This Useful Post:

    Excelnewbie (2016-03-30)

Posting Permissions

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