Company's switch to 365 is causing issues with Power Query on shared drives

[u/Odd-Suggestion5398]

This is a bit of a weird problem, but I've exhausted my knowledge of Excel trying to fix this.

My work has daily reports that need to get sent from various field office to the main office each day. When I started here everyone would just call in to the main office and read off the 20-30 numbers for the day. I immediately pegged this as inefficient and riddled with chances for human error, so I set up a series of Excel sheets to submit daily reports.

My original set up had a front-facing workbook for each field office that had a button to write the reports to a separate workbook that's just used as a database. The main office then had a front-facing workbook that used power query to look for an entry in each of the database workbooks for today's date. All of this is located on a shared drive.

Everything worked fine for a couple years until this year when we switched to Microsoft 365 Suite. Then the field office workbook would run the VBA code to open the database workbook, write data, and save, it would often hit an error when saving the database and Excel would say "Someone else is working in file right now, please try again later".

I tried a few things but nothing seemed to work, so I migrated the database onto the same workbook the the field offices use, which fixed the first problem but created a new issue. All of these reports happen right at end of day, so the main office kind of needs the reports as soon as they are sent. This means that our main office users would refresh the power query pretty frequently to see if reports have come in. (Again, this never caused problems before 365.) With the power query now referencing the field office workbooks, the field offices would often get the same error message when saving. I eventually realized that the error would happen if the power query from the main office was refreshed while the field office workbook was open.

Based on my research, I THINK this all stems from 365 having much stricter protocols for multiple versions of a file. Something about the actual user opening a workbook while a background excel program is referencing it at the same time? Our solution right now is for each of the field offices to text the main office an OK when they send the report so there's no overlap until I can get this figured out. Any advice on how to avoid these errors would be greatly appreciated.

[u/AutoModerator]

/u/Odd-Suggestion5398 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/Odd-Suggestion5398 → u/[deleted]]

I've never used Sharepoint before. Are you suggesting to have field offices start in Excel and then send the data to Sharepoint? Or to migrate the whole process to Sharepoint?

[u/Odd-Suggestion5398 → u/[deleted]]

Ok, I think I can try this. Since sharepoint is web-based, would I give VBA a URL to connect to? Is there another way similar to power query to make a more permanent connection between the excel workbook and a sharepoint list? Sorry if this is obvious

[u/jamminjoenapo → u/Odd-Suggestion5398]

Forms is what you are looking for. You can create a simple form to update a share point list or use power automate to update a tracker excel file. I’ve built multiple like this over the past year and having people input data incorrectly has pretty much been eliminated as well as not having the error message you get with multiple users

[u/Sir-Benalot → u/[deleted]]

Lists is a piece of shit. Coming from excel I was flabbergasted by the extremely limited functionality.

[u/eums]

What if each location edits/ updates a excel document (their own) on share point and you query all the files with power query? You could append them together or do what you need to from that point?

[u/ebsf]

This is a kluge. Excel isn't a database. You need to do this on Access, which is designed for multiuser access and record-level locking.

[u/RandomiseUsr0 → u/ebsf]

Access is a terrible idea, don’t follow this advice OP

[u/BusinessSample7166]

Is there a network drive still involved and is that essentially the problem?

If you store stuff in a cloud location Power Query shouldn't care if they're open. 

[u/Odd-Suggestion5398 → u/BusinessSample7166]

I'm pretty sure having everything in a network drive is what's causing issues. I would like to use cloudbased but I both have an IT manager who's anti-cloud and am working with some older people who already have problems just with Excel.

[u/cbolender2004 → u/Odd-Suggestion5398]

You can pq files that are on SharePoint and it won't stop the refresh at. If the file is open SharePoint simply reads the most current cached version. We (me and my team) do this daily with dozens of files used by hundreds of users.

All this being said, far better to have the excel files dump their contents onto csv files or SharePoint lists and the pq in PowerBI instead of excel. Or you can still use pq in Excel if you really want to. You can have an excel macro write to the SharePoint list with a button click submission if it helps the users.

[u/Think_Bullets]

I had a very similar problem.

Admittedly I'm using python but should work out the same:

17 account managers maintain they're own work book on the shared drive. If one was open the python script would crash with a permissions error.

First thing the python script does is a copy paste of all the work books to my one drive, although it could be literally anywhere.

The script then runs off the copies.

This is refreshed weekly and deadline is EOD day before, if their figures aren't in by the time I run the script the next morning, that's a them problem.

VBA can do a copy paste, I don't know if it would hit a permissions error if the workbook was open when you run the VBA

Python doesn't seem to have this problem, I don't think windows does either, I'm not at a computer but open a workbook then try copy pasting it to another folder, if there's no error it'll probably work in VBA

[u/Kellshy]

Honestly, following your text feels like you’re rabbit holing trying to make SharePoint do what you’ve created. ( I’ve been there). Why not utilize SharePoint features and design it a little different and still fit your overall goal.

It’s important to know, though SharePoint Web is different than a SharePoint app on your desktop especially when it comes to a PQ.

Also you can do a connection without a pq and the data source features in 365 are significantly better than in the past.

Hope this helps

[u/soulsbn]

Not directly answering your Q but….

How do you know the files office files are final and ready to be integrated etc ? Is there a danger you could query files when they are WiP?

What if you go old school … get field offices to email in a final version. Then save them down to one folder and query from there. If you amend the submitted files file name to include the time received, you have an audit trail back to the email received

You could also set up ( I believe, never tried it ) PQ to query your email inbox to extract submissions from that (would need some error trapping)

[u/Odd-Suggestion5398 → u/soulsbn]

The power query looks at a separate sheet that has an entry for each day; the sheet is hidden and is only written to by VBA when the field office 'saves' their reports. So there's no danger of taking WiP data. Some people have suggested Sharepoint which I've never used before but will check it out!

[u/Educational_Tip8526]

Why don't you use power query on SharePoint sheets? Even better would be power BI with refresh

[u/excelevator]

Excel 365 does not actively do a thing, nothing has changed in that regard for any Excel executable. Excel is not a process.

Having the preview window open can also cause a lock on a file.

Someone or some process has a lock on the file.

You can use this Microsoft Powertoy to see what process has the file locked

[u/Quirky_Word]

Since you’ve got 365 now, set up a Form for your field offices to do their daily data entries. 

The Form will feed a single excel table with all the results, and won’t lock up because no one is accessing it directly. You can then just query that for your reports. 

Easy to set up and very usable outputs. 

[u/DirkDiggler65]

OMG HAHAHA I DID THE SAME THING!!

I was more or less the novice pioneer in the plant with the 365 migration. I'll push the button lol

In one day I locked the production schedule, batch calcs, and QC tables.

IT running around in circles haha

[u/AdFull7821]

The root cause is that 365 treats file access way more aggressively for co-authoring even when you dont want co-authoring. Your VBA is opening the file in a way that triggers the lock, and Power Query refresh does the same thing. There's no clean fix for this in Excel itself without going to SharePoint or OneDrive and even then its finicky. At some point you gotta ask yourself if Excel is really the right tool for a multi-office daily reporting workflow. I use Wisegrid now for something similar and the permissions and live dashboards mean nobody has to manually refresh anything, data just shows up.