+ Reply to Thread
Results 1 to 5 of 5

Updating workbook_open from workbook_open

  1. #1
    Forum Contributor
    Join Date
    01-26-2005
    Posts
    108

    Updating workbook_open from workbook_open

    I have a workbook which automates some testing (loading results etc) and is used by a number of users.
    My copy is the "master" and is not available to them other than as a read-only version from which they "save as" their own versions (they each have several copies of it - one per test case they are building)

    When I first created this "tool" I thought about releasing new versions of the main macros that sit inside it. The approach I took was to have a workbook_open macro that loaded the latest version of the main macros from a .bas file on the LAN. This has worked a treat but I've now hit a slight snag.

    I want to make the workbook_open macro do some extra bits as well. Obviously that's easy for me to do in MY copy but I want to distribute the latest workbook_open to all users.

    I'm about to set off opening loads of their workbooks and copy/pasting my new version in but I feel sure this will need to be done again at some point so would like to take the opportunity to make it future-proof.

    Is there any way I can set the workbook_open macro to check for new versions of ITSELF in a given location? Probably not but if anyone has any brilliant ideas how to achieve the same result I would be grateful.
    Tony

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi Tony,

    I haven't got time to search all of my old VBA modules at the moment, but I'm fairly certain that I HAVE written self-modifying code (which is effectively what you need). The significant thing is that the code OVERWRITES itself rather than loading a replacement module.

    I'll see what I can do later on to find an example of self-modifying code, but I thought I'd at least let you know that in prnciple it should be possible to do what you propose.

    Hope this is at least encouraging!

    Regards,

    Greg M

  3. #3
    Forum Contributor
    Join Date
    01-26-2005
    Posts
    108
    Thanks Greg.

    I'm no expert but I've tried all sorts of ways and am at my wits end.
    VERY nice to know it can be done.

    T.

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi again Tony,

    I'll see what I can to to dig out an example of self-modifying code - obviously it won't enter the code YOU require, but it might at least show you how to go about it.

    Best regards,

    Greg M

  5. #5
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467
    Hi Tony,

    I've been thinking about your requirements but I need a bit more information from you.

    If I understand correctly, the "Workbook_Open" routine in a User's workbook automatically updates VBA code modules (other than the "ThisWorkbook" module) in that workbook.

    I'm assuming that the replacement VBA code modules are contained in a stand-alone workbook, e.g. "Company Project - New VBA Modules.xls".

    I'm also assuming that the above updating occurs every time a workbook is opened - i.e. there's no check performed to determine whether or not the workbook NEEDS to be updated, the latest versions of the VBA code are loaded even if those versions are already present in the workbook.

    Now you want to insert additional VBA instructions into the "ThisWorkbook" module.

    The fundamental RESTRICTION is that you don't want to have to access Users' workbooks manually.

    The fundamental PROBLEM is how to use the EXISTING code contained in a User's "Workbook_Open" routine to update the code contained in that "Workbook_Open" routine itself.

    So, I need to know what method you use to update the other VBA modules. What happens after the "Workbook_Open" routine in a User's workbook opens the "Company Project - New VBA Modules.xls" workbook? How does your code specify which module(s) to replace? Does it look for one or more specific modules, or does it use all of the modules it happens to find in the "Company Project - New VBA Modules.xls" workbook? If we want to introduce a completely new module, can this be done using the existing code?

    It would be useful to know how computer-literate your users are. Ideally the updating we're talking about should happen totally in the background without any intervention on any user's part, but if push comes to shove, would they be capable of running a specific macro (e.g. via Alt+F8) on a once-off basis? This might be a cheap 'n' cheerful method of getting the first version of the new "ThisWorkbook" module deployed, with its self-updating code incorporated in it for use on subsequent workbook opening.

    If there's anything else you think I should be aware of please pass it on.

    Best regards for the time being,

    Greg M

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1