0

I have a workbook file that is used by 1 team with a few people; but soon it will be disparate teams (2 teams; 8 people) accessing the file on our corporate share... This could grow to more teams and people. There is a concern with the 2 teams using the same workbook that there will be “saving conflicts” making it difficult to synchronize work flows between the isolated teams. Most of the time the users will just open it and refresh the data connection and read the data in the file-- But sometimes a user might want to save the workbook after refreshing it in the morning, or maybe they want to save another small change they made.

My Question:

Assuming USER1 has the workbook open, is there a simple way that USER2 can prompt USER1 to close the workbook and give USER2 the editing user access. Keeping in mind USER1-Team1 and USER2-Team2 use the same data, but do not know each other or have a close working relationship; So a phone call or email is not convenient for their workflows.

Some Background:

The workbook contains PowerPivot data model from SQL Server datasource. When users refresh the workbook they are updating the data model, and the pivot table reports connected thereto.

EDIT: The version for the workbook is Excel 2013 with Power Pivot


Solutions I've considered ...

  • 1) MASTER FILE with CHILDREN -- This is a "Go pull an updated working copy off the shelf" method, in which there is a Master file ("MasterFileReportsHere.xlsx" in myNetwork\Path\Location on the network share) and 1 power user will communicate when updates are ready in the Master and should be download to separate team workbooks for use by the disparate teams (“Group1 Reports Here.xlsx”, “Group2 Reports Here.xlsx” in myNetwork\WhoKnows\WhereTheyPutIt or local C: or ThumbDrive).

What do you you think about this strategy? I think this creates risk that users will not download they file when they are supposed to, and then you have different versions and not 1 true master. Also, you cannot really tell where they may have moved the CHILD files to (or at lest, no way that I am aware of).

  • 2) File In Use> Notify -- I have shown them that when USER1 has the file open, how USER2 can select the "File In Use> Notify" option which prompts when the file is opened for editing.

The shortcoming with this if feature is it simply tells USER2 when USER1 has closed the file and it then prompts USER2 to open the file in editing mode. USER1 could close it in a few minutes or a few hours from when USER2 wants to edit it, which isn't quite what they need.

  • 3) EXCEL: REVIEW> Share Workbook -- I have briefly looked into the Share Workbook feature, but the workbook has tables and this feature will not support workbooks with tables (I could research removing the tables)

    • I have never used this before, but it looks like it has options such as "Allow changes by more than one user at the same time". With this enabled it appears that you can configure these options--> UPDATE CHANGES as When the file is saved or Automatically (x Minutes): USER1-SaveMyFile and USER2-SeeTheirChanges or Automatically (x Minutes): USER2-SeeTheirChanges // CONFLICTING CHANGES BETWEEN USERS as Ask me which changes win or The changes being saved win.

Has anybody used this before? Would they recommend is? Should I consider hunting down the Tables in their file for prospectively deleting them (this is possibly not an option).

4
  • EDIT: The version for the workbook is Excel 2013 with Power Pivot Sep 18, 2018 at 21:20
  • UPDATE: When I google "excel workbook sharing" one of the first results is a Microsft Docs article "What happened to shared workbooks?", which then talks about "co-authoring" feature. Looks like "co-authoring" requires Office 365 and OneDrive ("Office 365 Excel for Office 365 for Mac Excel 2016 Excel 2016 for Mac More...")-- this is interesting, but not applicable. Sep 18, 2018 at 21:33
  • 1
    Not really a technical answer, but is providing the people who need to use this worksheet with the contact information of the others out of the question? That sounds like the simplest way, plus your team can build a relationship.
    – BruceWayne
    Sep 18, 2018 at 21:35
  • @BruceWayne I wouldn't say it is out of the question to have them share their contact info. But they're all busy people, and I think it is probably annoying to them to have to contact someone so they can use the file (especially Team1 that has been using this file for a while and now Team2 is suddenly in their file). Sep 18, 2018 at 21:39

1 Answer 1

1

It could be done using VBA and Shell to send a Msg to users, though your work's network permissions could prevent this.

Msg Syntax

msg {<UserName> | <SessionName> | <SessionID>| @<FileName> | *} 
[/server:<ServerName>] [/time:<Seconds>] [/v] [/w] [<Message>]

Have a look at paul bica's answer for using Msg and VBA to send a message to users. Integrated with Steven's solution for finding the user who has workbook open.

I could not test it successfully as my workplace has its network held with a tight vice grip. However I hope this answer can at the very least point you into the right direction.

1
  • I am shying away from converting to a macro workbook (.XLSM), because I'd prefer to avoid further maintenance and development. But thanks for letting me know about the VBA messaging option-- I will keep this in mind. Sep 20, 2018 at 13:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.