Results 1 to 4 of 4
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Modifying code remotely (Excel 2000)

    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. <LI>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?
      <LI>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'?
      <LI>And can someone point me in the right direction to gain a deeper understanding of the details?
    Regards
    Don

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modifying code remotely (Excel 2000)

    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 [i[through[/i] the workbook for storage in a simple data structure away from the data-entry workbook.

    Hope that makes sense.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Modifying code remotely (Excel 2000)

    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.
    Legare Coleman

  4. #4
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Modifying code remotely (Excel 2000)

    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
    Regards
    Don

Posting Permissions

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