none
Merging Changes using VBA from the users end. RRS feed

  • Question

  • Hi All,

    I have a workbook saved on a server in my office. What I want people to be able to do is save a copy of the workbook to their own laptop, so that they can take it home with them. They can work from home, making whatever changes are required. When they connect back up with the server and hit save on their copy, the excel sheet pushes the changes back to the original file on the server,  without removing any changes another user may have made.

    I already looked at this link: https://support.office.com/en-us/article/Overview-of-sharing-and-collaborating-on-Excel-data-3c4fb64f-bf46-42a4-9f6a-73cc6367d378?ui=en-US&rs=en-US&ad=US&fromAR=1

    but the best method (Distributing copies of a workbook and then merging or consolidating data from each copy) requires my intervention to merge the data, while I want the data to be merged from my user's end.

    I am happy for a VBA code solution.

    (I already made an attempt at this by having an after_save event save a copy to the server, but because one person had an un-updated version that they saved, it overwrote the more up-to-date version).

    Any help is appreciated.

    Wednesday, November 30, 2016 12:40 AM

All replies

  • Hi Alex,

    Are there any possibilities which two of them modify the same cell value?
    I'm afraid what you want is not possible, if some restrictions/regulations do not exist.
    Do you have some regulations or rule that prevent them from update the same cell?

    Regards,
    Ashidacchi
    • Edited by Ashidacchi Wednesday, November 30, 2016 5:26 AM
    Wednesday, November 30, 2016 5:25 AM
  • Hi,

    Please visit Considerations for server-side Automation of Office:

    Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

    So alternatives to server-side automation are Open XML library and Excel Services.

    According to Excel Services OverviewHowever, you cannot use Excel Services to create new workbooks or to edit existing workbooks

    The most possible solution is using Open XML library to manipulate files. It might be complicated based on the change from the users, because it need to write all possible changes using Open XML object model. For more information, you could visit Spreadsheets (Open XML SDK) & Modifying Open XML Documents using the SharePoint Object Model

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 2, 2016 3:21 AM
    Moderator