Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: VBA to update Excel 2010 .xlsm on network when user has file open

  1. #1
    Join Date
    13th October 2011
    Posts
    5

    VBA to update Excel 2010 .xlsm on network when user has file open

    I have VBA code that modifies a master workbook and then performs "Save As" to 12 different users in the format Resource[Last Name].xlsm however, if one of the users has the workbook open the VBA wants to "End" or "Debug". Is there any way I can update these files while they are open so that the next time the user opens the file the updates are received?

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd March 2010
    Location
    Springfield, IL
    Posts
    191

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    13th October 2011
    Posts
    5

    Re: VBA to update Excel 2010 .xlsm on network when user has file open

    Thank you very much for your response! Actually you got me thinking and I have only tested it on one user but what I might try (if you agree ;-) is saving each Resource[Last Name] as a .xltm and then if they use a shortcut, either in the startup folder or on their desktop, then they are opening a copy "Resource[Last Name]1.xlsm" and this allows me to update while they are still using the copy and when complete I simply have to have them close their copy and open a new one. I tested it on a dummy user and it seemed to work just fine. Is there any reason that you know of that it wouldn't work for all 12?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    2nd March 2010
    Location
    Springfield, IL
    Posts
    191

    Re: VBA to update Excel 2010 .xlsm on network when user has file open

    That is certainly a way to do what you need to. This is why I made the suggestion - to stimulate you to look at the problem from a different perspective. The one concern I have is that while I think your solution will work, it requires some extra effort on the users to stop and start their sheets. From a programming point of view, it is always better to minimize the number of ways a user must interact with your program to accomplish a task. That is why I suggested a timer function to automatically update the user files while open. However, that being said, I now say go for your idea and see how it works for your users. I will be glad to be available to help or answer questions (if I can).

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 1
    Last Post: December 9th, 2011, 15:42
  2. Replies: 9
    Last Post: September 2nd, 2011, 06:27
  3. Replies: 3
    Last Post: October 18th, 2010, 16:31

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
  •  
porno