Modifying code remotely (Excel 2000)

Topic

New Reply

Good day all
I have a workbook which will be distributed to a number of organizations. This workbook will then be used to feed another workbook in a different organization that contains the same VBA code. This scenario will be duplicated many times, with several hundred organizations reporting to their immediate sponsor, until a single comprehensive report is issued. This process will happen many times with each report that is issued by a given organization being built on the last which they submitted to their sponsor.
The approval to proceed to the next reporting period will be provided by an approval file sent by the ultimate authority to each of the contributors to the report. This approval file will contain code to archive the approved report, and set up the system for the next reporting period.
Now I’m just too long in the tooth to believe that changes to the VBA code will never be required. I have searched for insight into modifying VBA code with VBA code and the instances I have found appear to not modify the code as saved but create an object and then run that object

Now my questions are:

1. Is it possible to modify the code in Workbook ‘A’ from code in Workbook ‘B’, such that the new code will be saved in and run in future occasions of Workbook ‘A’ being opened?
2. Is it possible to do this if Workbook ‘A’ has the VBA Project locked for viewing, and the appropriate password is available to the code in Workbook ‘B’?
3. And can someone point me in the right direction to gain a deeper understanding of the details?

Reply | Quote

Replies

I used to do something like this in DOS Lotus in 1989 for a client with about 750 divisions all across Canada and the USA. We released the spreadsheet for data entry, but needed to issue patches that had to be self-installing during the laborious data-entry process. We couldn’t afford to have offices get 90% through the data-entry process and then tell them “Sorry, here’s a better spreadsheet …”.

I can dream up several ways to implement patching on-the-fly, but maintaining the data entry sheets in a locked state would be tricky.

Since you have to realse patches to the original VBA code in some form, would it be feasible to treat the original data-entry workbooks as simple bootstrap devices and have them invoke all detailed code through a second workbook? The second workbook containing the mechanical code could then be re-issued by you as the working engine of the whole process, without needing to re-issue the data-entry workbook, and without needing to unlock any VBA code on site?

An alternative woulbe to use the data-entry sheet solely as a channel for data, and pass the data from the end-user the workbook for storage in a simple data structure away from the data-entry workbook.

Hope that makes sense.

Reply | Quote

What you are asking is technically possible, but extremely complex and very easy to create major problems. I would NOT recommend doing this without having VERY advanced programmers available to create and maintain the code.

When I faced a similar situation a few years ago, I ended up putting all of the code into a separate workbook that would never contain any data. This is a little tricky, particularly if there are event routines involved, but it can be done. Then if the code needs to be modified, this workbook can just be replaced. This is much safer and much less complex than the approach you were thinking of.

Reply | Quote

Thank you Chris and Legare
As always your comments were most helpful. You have convinced me to go with plan B.. If an update is necessary, I will include the complete suite of code in the Approval file, then copy all sheets from the existing report into the Approval file. Following this with judicious Kill and SaveAs commands, the task will be complete.

Once again thank you for sharing your experience.
Don

Reply | Quote