Issues with multiple users accessing a file.

[Kryztof]

Hi everyone,

I'm having a weird issue where if someone is using an Excel file, then sometimes when another person goes to access it, they get in without any prompt saying that the file is currently in use. So it goes something like this:

Person A opens up Excel file XY and starts entering some data.
Person B opens up Excel file XY and starts entering some data.
Person A goes to save file, and gets told that the file is currently in use and saving data may overwrite anything that Person B has entered.
Person B can save the file normally.

At the same time, I am also experiencing this issue when only one person is in the file, at least to my knowledge. It goes something like this:

Person A opens up Excel file XY and starts entering some data.
Person A goes to save file, and gets told that the file is currently in use and do you want to overwrite.

I believe I have been told that sometime it works in reverse as well; as in, person A will open the file and be told that it is currently in use, yet when asked everyone states that they are not in said file. I've only been told this once however so it is on the backburner.

I have checked and the file is not set to share (at least the "Un-share Workbook" is greyed out. I will admit that I know very little about Office 365, so there very well may be a setting that I am simply overlooking, and I initially thought it might have because I believe all of the users share the same 365 license; however, it has happened to someone using Excel 2010 as well. Searching online hasn't netted me much as everything seems to be in reference to Excel 2007; plus the fact that actual answers where in the form of "make sure it isn't set to share.." or "make a macro to open the file". The file is on a shared network if that makes any difference, but I'm not having issues anywhere else. At least no issues that I am aware of.

Any help on the matter would be greatly appreciated.

[davesexcel]

I don't know if you are referring to a shared workbook, but if you goto Review Tab and click the share workbook, then save and close it. Practice with a test workbook to see if it is something that you want.

[Kryztof]

Hi davesexcel,

The Workbook is not set to be shared. It is used by multiple employees; however, only one employee should be in it at a time. But I will give that a shot just in case it thinks it's a shared workbook for whatever reason (yay technology).

Thanks for the response.

[Kryztof]

Update:

The file has not been set to be shared, especially since no one is signed into OneDrive so they aren't even able to do so in Office 365 (please correct me if I am mistaken). However, I did experience one thing while opening up a Word document via hyperlink. It seemed to bypass the warning that the file was already in use and just opened the document. Now granted, I wasn't able to do anything. Wasn't even able to click "Enable editing". I'm not sure if Excel handles it in a bit of a different manner, where the user is still able to click "Enable editing".

[Kryztof]

Shameless bump.

Still trudging through the internet trying to find an answer. The closest thing I might end up doing is simply setting the file itself to read-only so everyone will have to click on Enable Editing. Not sure if that will solve the issue though. Other thought was to find some sort of software that can monitor activity on that specific file (i.e. Who opens it/ when they open it, who edits it, when people close it, etc. etc.). Not sure if something like that is floating about but I wouldn't be surprised if it was.

[davesexcel]

I was referring to setting the workbook to shared, then multiple people can have it opened, and save it.

I was suggesting that you do a practice workbook and make it a shared workbook. Then close it and have other people open it, do stuff and save it at the same time, then see what happens.
That was just a suggestion so you would know how a shared workbook works.


I really don't like shared workbooks, there are limitations.

---------------------------------------------

In the past I have used a "Master Workbook" that nobody is allowed to touch.

I then create other workbooks that will either get data or give data to the "Master" workbook.

These workbooks are the workbooks that the staff use.

The "Master" workbook would be just a Data Storage, and saved as an .xlsx

The "Tools Workbook" would be an .xlsm workbook

[Kryztof]

Hi davesexcel,

Thanks for the response.

I see what you mean, I guess the initial point flew right over my head. Ideally we would not have the workbook as a shared file as theoretically, only one person is supposed to be in it at a time. I like the idea of a Master workbook with everyone having a 'Child' workbook. I do have a question though, is it possible to create a macro that would import the data from various difference workbooks (one at a time would be alright too, but if multiple is possible that would be better), and sort it into it's proper section? I.e., this workbook has data from the last 14 months, it is split up by week (i.e. whenever the week ends, we mark off a new week in the same sheet so everything is still together). Would it be possible to have the macro look for the specific week in question and insert the data accordingly? I'm curious if this is within Excel's possibilities.

Thanks again.

Edit: Would also like the macro to not copy over any duplicate data.

[davesexcel]

The "Child" workbooks would open and close the master workbook to get or give information.

You need to ask a specific question, supplying a sample workbook with your question helps as well.

[Kryztof]

Hi davesexcel,

I've attached a sample workbook (I think) of what I am working with (the actual one is actually much larger, has more formula's and has some that references other sheets). However, this is the base principal. I added some on the second sheet as well as an example of what would be needed to be brought over from a separate workbook.

What I am asking is, is there a way to take the information from the 'Child' workbook, place it into the Master workbook, but taking the "Week of ___" into consideration? It can't simply be by date as it is quite possible that something won't be dealt with until a week or so later; however, it needs to stay within the week that it was done (i.e. if I receive something on Dec 4th but it is dated November 28th, it goes into the "Week of Dec 4" section and not the one prior).

At the same time, would there be a method to look for duplicates? (i.e. if the 'Child' workbook doesn't get refreshed so it has data in it that has already been brought over to the master workbook). Or it would just be best that every day, the employee saves the workbook as Child_James_Nov29 // Child_James_Nov30 etc. etc.?

Thank you for your assistance.

[davesexcel]

The layout would have to be different, we want to just copy and paste the data to the master sheet and sort it., the individual rows for the "week of.." is a problem.

Can you explain how the data in being inputted to the sheet?

[davesexcel]

How is this information being received or entered in the worksheet?

[Kryztof]

As of right now, they are being entered manually by various employees periodically throughout the day. I don't have exact methods, just that whenever people have something to enter, they do so.

Edit: Note sure if it would be easier, but maybe the macro could be on the Child files instead, that way at the end of the day, the person can just run the macro, it inputs the data into the master file, makes a backup of the current child file (i.e. Child_James creates a Child_James_backup_-today's date-), then clears the data from the initial workbook, preventing the macro from having to look for duplicates).

[Richard Buttrey]

Excel is a single user system (unless you happen to turn on the sharing functionality).

That doesn't mean many users can't access and read it at the same time, but it does mean that only the first person can change and save it.
Others can make changes but they have to save with a different file name.

I'd hazard a guess that one of your users has accidentally left the file open and forgotten about it so that when you think Person A is the first person to have opened it they are actually the second person.

[Kryztof]

Hi Richard Buttrey,

That has been my general opinion of the matter; however, people are stating otherwise.

I am aware of a slight issue if people open up files via hyperlink where it sometimes bypass the "file already in use" message and loads straight into Read Only; however, that tends to solely be a Viewer version of the application, where there is no way to alter any data. So I'm not quite sure what has been going on (unable to replicate the issue myself). So unless people are ignoring the "file already in use" window that pops up, I'm at a loss as to why it is happening.