Using VBA to "Update" Spreadsheet

[FoRmEd]

Just want to verify my brainy idea before i start researching what sequence I need to do it in. I don't necessarily need the code... I just need to know if it's "doable," and I will take care of the rest. I will also post the finished code here so those that find this thread while searching can use it.

Ok, so let me run this idea past you. I maintiain an extensive excel spreadsheet, and I release a new version every month. The current process... copy and paste. The future process... an update button on the settings tab.

Is it possible to run a macro that pulls the new version from a predetermined folder, renames it to the same as the old one, copies all the users settings from the settings page and closes, leaving the new version sitting infront of the user? Hence calling it an update button, lol. Or is there a more sensible way to go about it?

 

[mikerickson]

One way would be to Import VBComponents, but a lot depends on the situation.

Mostly, when you update, are you changing just formula cells or are you overwriting cells with data?

It sounds like you have many users and getting the update to them is the issue.

 

[FoRmEd]

You're quite right, I do have lots of users, and the current complaint is the process of updating their versions. The newer versions contain anything from new modules to userforms or just changing the color of a cell. Basically whatever changes that are requesting. You know the drill, they dont care about the stuff in the background lol.

 

[mikerickson]

You could sent them a workbook that has an update button. Pressing the button would either:
Change specific cells in their workbook.
Delete a VBComponent from their workbook (e.g. userform), export the replacement component from the Updating workbook and import it to their workbook.
Copy their data from their workbook to the sheet (with new event code) in Update, delete the original sheet and replace it with the one from Update.

Which it would do would depend on the kind of updating requried.

But a seperate workbook that alters their workbook would probably be the easiest way to preserve their data.
(Be wary of the possibility that they rename worksheets and other customization. Make it clear to the power users that adding VB to their workbook is done at their risk.)

ONE CONCERN- if someone skips an update. You could add a custom document property myVersionNumber to their workbook. The Update workbook that you sent them could have all the updates and apply those after their versionnumber.

 

[FoRmEd]

Oh I really like your first idea. It reminds me of a patch, altering what is already there.

I know that the renaming bit is difficult, but I was going to get around that by having the version number in the filename.

Im excited to work on this! Where to start... *grabs wrench*

 

[FoRmEd]

Just a quick update, I'm still working on this. I only get an alotted time per week to work vba, and it's very limited.