Excel and SharePoint


Perhaps I have been going about this all wrong... looking for excel was to do this when the issue is really with SharePoint.

SharePoint is great for some files that but for excel, given SharePoint is new to many, having multiple users in excel files at once has started to cause us issues such as people overwritting people and multiple people doing tasks.   

Overwritting - a person has the file open and working on it and another person comes in before person A saves and person B starts working then when they both go to save, its a mess as we may have person A starting at one place and person B starting at another and the file may read from both and cause all kinds of issues and odd numbers.  Yes, there is box that shows who all is in the file, but if you happen to be looking at another screen when the initial pop up comes on and you are deep into work, you may not look up and notice and if you do..... when did they join? was it before or after your last save? etc....

Task being done multiple times - person A opens the file (say the file is a template that is to be "uploaded" somewhere else) ... person A starts to work and person B opens the file and before you know it, both have uploaded the template causing duplicate data elsewhere.


Been told to use the "on line" version of excel which would prevent multiple people in the file at once, but the on line version does not support macros / vba which most of our files have to streamline process'.  


Also, been told we could "check out" and "check in" files, but been advised not to do so by existing SharePoint users.  Reason being that if a person does not "follow" the correct sequence of steps to check in or check out, the file is essentially garbage.  People are use to seeing the file, as in file explorer, and the thought process is to "click on the file to open" and to try and change this thought process may simply not be worth the battle.  Perhaps what I have been told was an extreme case, but have not found too much on line to support checking out  / checking in.  Perhaps if there was a way to disable open if clicked on, inside SharePoint that would force the user to follow another route, such as download or check out, that may assist in changing the brains "auto click" thought process.


We are trying to use SharePoint to share files and get away from the server, but does not seem to have the "security of files" that we need.


Right now, I have been working inside excel trying to give pop ups if someone opens the file (in addition to the auto generated)  something the user would need to acknowledge before continuing and the same for the person opening.  However this has become a nightmare of code.


Is there away to accomplish having 1 person having a file open at a time or force the use of check in / check out, etc?


Any assistance is greatly appreciated as I am extremely close to scrapping all the work done in SharePoint and writing a capital for a faster on site server with "ease of use" being the justification.

1 Reply
Have to say, been playing with "check out" / "check in" and other than it seems to get stuck in read only sometimes when it is not checked out to anyone, I am not seeing where that is too difficult. Any advice on why it may be getting stuck in read only when not checked out? been reading it may have to do with the file being synced through one drive? if so any advice / links to un sync?