Re: VBA to update Excel 2010 .xlsm on network when user has file open
A method immediately comes to mind...
In the Resource[Last Name].xlsm file for each user put some VBA code in the Workbook_Open event to open an update file from the Master and copy the updates as necessary. In the Master, instead of writing to the file the users open, write to an update file that will be deleted after the user file gets the updates from it. You could also use some kind of timing function to initiate an update while the user has the file open. The point is that there is no way to reliably update with Excel a network file that maybe opened by another user since Excel will try to keep the file exclusive to itself when open. Even if you try the Share facility in Excel, the results are spotty and can be tragic.
What you want to do is use the user files as the arbiter of when they get updated, not the Master. You are trying to do multi-user access which is hard to do in a good system designed for it. Excel is not designed to be multi-user so you have to hack together a procedure that works for you.
I do not have Excel 2010 but have 2003 and 2007. What would work there will work on 2010 also. We can talk more if you want about this.
Video Tutorials / Excel Dashboards Reports