justin404
02-06-2012, 05:29 PM
Hello all,
I've got a bit of a problem that I'm trying to deal with and I'm not sure of the best way to go about it.
Back in 2007, I had somewhat of a summer internship for an HVAC contractor company and created a few spreadsheets for them that helped manage their extra work quotes. The spreadsheets have since stopped working (due to network changes, and some bad assumptions on my part), and they have contacted me to fix them.
Essentially, there are 2 main files in question - XW Template.xls and XW Quote Log.xls
XW Template.xls contains a sheet named "Template" which basically has some fields that the user will input, then when the user goes to Save/Save As, a sub routine kicks off that copies the data from the respective cells into the XW Quote Log.xls. The user can then assign a custom name to the XW Template.xls file through the normal SaveAsUI dialog.
The XW Quote Log.xls contains a sheet named "Extra Work Quotation Summaries" which essentially contains all of the data from the template (IE one row in the XW Quote Log.xls file corresponds to one XW Template.xls workbook).
The initial problem was that the XW Template.xls book stopped copying the data over to the XW Quote Log.xls file. I was able to fix that relatively quickly, but this is only a "going forward" change. They would essentially have to use the new version of the XW Template.xls file, with the updated macros, for all quotes entered from here on out.
Unfortunately, this is not an ideal solution. They are looking for the ability to go back to older Templates that they have saved, edit some of the information, and then re-save the old template to have it import into the XW Quote Log.xls file.
The issue that I am faced with now is how can I programmatically update (potentially) thousands of workbooks with my updated macro code? I'm not sure if this is even possible with just VBA.
I'm currently a java developer, so I've been thinking I could leverage some java toolkits to get this task done. My current thoughts are as follows:
1) Using the Apache Commons POI library (can't link to it since this is my first post -- but google Apache POI for more info) I could write a script to iterate over each spreadsheet, copy the data from the old sheet, into a new sheet that already has the updated macro, then save the file.
2) I could try to use VBA to iterate over each workbook, copying the pertinent data into a new workbook with the updated macro.
3) If this is even possible, using VBA iterate over all of the workbooks, and programmatically replacing the BeforeSave macro with my new version.
Aside from this, I'm not sure what other options there may be. Unfortunately just telling them to use my new version is not going to cut it. The people that use these spreadsheets are not very tech savvy, so whatever solution that I come up with needs to involve the user as minimally as possible. Any and all suggestions would be greatly appreciated. Thank you in advance for your help.
I've got a bit of a problem that I'm trying to deal with and I'm not sure of the best way to go about it.
Back in 2007, I had somewhat of a summer internship for an HVAC contractor company and created a few spreadsheets for them that helped manage their extra work quotes. The spreadsheets have since stopped working (due to network changes, and some bad assumptions on my part), and they have contacted me to fix them.
Essentially, there are 2 main files in question - XW Template.xls and XW Quote Log.xls
XW Template.xls contains a sheet named "Template" which basically has some fields that the user will input, then when the user goes to Save/Save As, a sub routine kicks off that copies the data from the respective cells into the XW Quote Log.xls. The user can then assign a custom name to the XW Template.xls file through the normal SaveAsUI dialog.
The XW Quote Log.xls contains a sheet named "Extra Work Quotation Summaries" which essentially contains all of the data from the template (IE one row in the XW Quote Log.xls file corresponds to one XW Template.xls workbook).
The initial problem was that the XW Template.xls book stopped copying the data over to the XW Quote Log.xls file. I was able to fix that relatively quickly, but this is only a "going forward" change. They would essentially have to use the new version of the XW Template.xls file, with the updated macros, for all quotes entered from here on out.
Unfortunately, this is not an ideal solution. They are looking for the ability to go back to older Templates that they have saved, edit some of the information, and then re-save the old template to have it import into the XW Quote Log.xls file.
The issue that I am faced with now is how can I programmatically update (potentially) thousands of workbooks with my updated macro code? I'm not sure if this is even possible with just VBA.
I'm currently a java developer, so I've been thinking I could leverage some java toolkits to get this task done. My current thoughts are as follows:
1) Using the Apache Commons POI library (can't link to it since this is my first post -- but google Apache POI for more info) I could write a script to iterate over each spreadsheet, copy the data from the old sheet, into a new sheet that already has the updated macro, then save the file.
2) I could try to use VBA to iterate over each workbook, copying the pertinent data into a new workbook with the updated macro.
3) If this is even possible, using VBA iterate over all of the workbooks, and programmatically replacing the BeforeSave macro with my new version.
Aside from this, I'm not sure what other options there may be. Unfortunately just telling them to use my new version is not going to cut it. The people that use these spreadsheets are not very tech savvy, so whatever solution that I come up with needs to involve the user as minimally as possible. Any and all suggestions would be greatly appreciated. Thank you in advance for your help.