How to auto update/sync an Excel file with VBA code when user opens it? RRS feed

  • Question

  • For example, I developed an Excel file with VBA code and sent it to all users. After I made some changes to that Excel file (both the VBA code and the sheet format), how can I set up this file to auto update when users open the file that I sent to them in the beginning?

    By "auto update" I mean, sync the VBA code and sheet format -- all the changes I made, to their local file. I think it's a little bit like git... but our users can't use git.

    PS: Solutions that we have already tried:

    Send them the new Excel file and ask users to use the latest version, but not all of them would listen and follow;

    Add VBA code to detect the file version, if it's not the latest version, close it. But all the old version files with history data can't be opened once I launch a new version.

    Please do share if you have any good idea for this question. Thank you!

    Tuesday, April 16, 2019 3:13 AM

All replies

  • I had this problem when creating an engineering app that is used to calculate power, area and performance of a microchip.  I divided the problem into 2.  1) I created an Excel addin (.xlam) that has all the VBA to perform the calculations and all the forms required by app.  It has no user data.  2) The user workbook that contains all info about microchip (.xlsx).  It contains no VBA code or forms. 

    When the user runs the app they select the user workbook they want to use.  If it is a blank workbook it initializes workbook by adding a few sheets that the user needs to fill out.  One field that is automatically filled out is the version of the app.  If they open an existing user workbook the app checks the version number and if it is an old number it asks if they want to update workbook.  There is VBA code in app to update workbook.

    The app ships with an Access db that contains engineering data.  Almost all version info is in db so it is a fairly automated process to update.   Please note that very few addin updates required updating user workbook.  Most updates were adding new features.  Only a few involved changing existing sheets in user workbook.  Remember there is no VBA code in user workbook.

    • Edited by mogulman52 Tuesday, April 16, 2019 1:01 PM
    Tuesday, April 16, 2019 11:34 AM