Corruption of Workbooks Stored in SharePoint

[MartinS]

Firstly, am not sure if this is the 'correct' place to post this as it's not about Excel per se, but with the large number of users, I'm hoping that it's a 'set-up' that others may have come across. Apologies if it's not the correct place: Admin - please delete if necessary.

We are seeing a major issue with a template based (macro-enabled) spreadsheet where, for some more than others (as the usage varies across the 258 instances), the workbook is opened to an error 'Can't find project or library' and no changes can then be saved.
Each version of the spreadsheet is stored in it's own specific SharePoint (Online) site, and as it's a macro-enabled workbook, users can only make changes when the file is opened in the Excel app. All users use company supplied laptops with the same version of Windows (10)/Office (365 ProPlus) as they are locked down to a corporate image.
The only way to fix these corrupted file(s) is with an Excel tool I have written that will copy all the data from the corrupted workbook to a clean template, and then re-save the file over the top. Although this doesn't take long to run, it's frustrating for the teams who get this error (some more often than others) and means they have to rely on me having time (and being available/online) to run my 'fix' before they can make any subsequent changes.
We also had this in the previous on-premise version of SharePoint, so it's not specific to the online version, but I have never been able to figure out the cause of these corruptions and feel it's time to attempt to find (and fix, if possible) the underlying cause.
For reference there is a workbook open which initialises a usage log, but there is no on-save event as we found that these would not fire when, in SharePoint 2010, users would simply check a file in and the on-save event would never fire.
Has anyone seen anything similar with Excel macro-enabled workbooks saved to SharePoint?
Thanks
Martin

 

[severynm]

Has anyone seen anything similar with Excel macro-enabled workbooks saved to SharePoint?

Yepp.

This doesn't actually fix the root cause, but I have found that for about 95% of the cases of this happening where I'm at, moving the version history back one version fixes things (in File Explorer, right click on the file → Version History).

At the company where I'm at, all users are using M365 and online SharePoint/OneDrive with files synced to their local computers. Everything is coauthored, so no dealing with checking in and out. We only really had the issue with a few users, but they were also by far accessing the most files, so that is probably meaningless. I have yet to really figure anything out about what might actually be causing it, and I only have two ideas left to try (and none of them seem great).

1. Convert everything to xlsx/xlsm. For some reason, all of our files, even those without VBA, are xlsb, and it's always been that way. Based on my research, xlsb does not corrupt more often than xlsx/xlsm, but if it does, it is much more difficult for excel to do an autorecovery. Maybe moving to a format that is easier for excel to recover might help in some of the cases.
2. All of our templates and files are descended from the same file created 20 years ago. Maybe something in that long chain has went wrong - some bits had flipped or some tiny incompatibility has snuck in over the years that might cause an issue in certain circumstances. My last resort would be to duplicate the templates from a totally new blank workbook.

Although, a little over a month ago, one of the users who reported the problems most often got a new computer (which needed to happen anyways), and the problems have seemed to disappear since then. So yea. Everything is a big question mark for me.

 

[MartinS]

Yepp.

This doesn't actually fix the root cause, but I have found that for about 95% of the cases of this happening where I'm at, moving the version history back one version fixes things (in File Explorer, right click on the file → Version History).

At the company where I'm at, all users are using M365 and online SharePoint/OneDrive with files synced to their local computers. Everything is coauthored, so no dealing with checking in and out. We only really had the issue with a few users, but they were also by far accessing the most files, so that is probably meaningless. I have yet to really figure anything out about what might actually be causing it, and I only have two ideas left to try (and none of them seem great).

1. Convert everything to xlsx/xlsm. For some reason, all of our files, even those without VBA, are xlsb, and it's always been that way. Based on my research, xlsb does not corrupt more often than xlsx/xlsm, but if it does, it is much more difficult for excel to do an autorecovery. Maybe moving to a format that is easier for excel to recover might help in some of the cases.
2. All of our templates and files are descended from the same file created 20 years ago. Maybe something in that long chain has went wrong - some bits had flipped or some tiny incompatibility has snuck in over the years that might cause an issue in certain circumstances. My last resort would be to duplicate the templates from a totally new blank workbook.

Although, a little over a month ago, one of the users who reported the problems most often got a new computer (which needed to happen anyways), and the problems have seemed to disappear since then. So yea. Everything is a big question mark for me.

Thanks @severynm appreciate your observations.
Agreed, it makes sense to roll it back as they can't do anything, but it's bizarre how it happens to some more than other, and others not at all.
It's less than 60 seconds to recreate and upload back to the original site using my Excel tool, but the most frustrating part is not being able to replicate the issue.
I have a team where it's happened a few times over the past few months so have asked them to let me know the last person who saved the file when it does open corrupted again, and I'll repeat the request for a few other teams to see if I can find something consistent with the users...not holding out much hope but worth a try.
Thanks again
Martin

 

[MartinS]

Oddly, I'm finding that often, I can open the same file that crashes for other without any issues, re-save and it's all OK again? The only difference is that user PCs are locked down, whereas mine isn't. I can't help but wonder if it's a permissions issue?

 

[severynm]

It could be, but I often encounter the same thing and my account is locked down like everyone else's. Sometimes an issue pops up that only requires the user re-open and just save again for the file to work properly. So far I've just figured it was a byproduct of us running a ton of legacy VBA that has never been updated since it was written. I don't seem to have the issues on newer files I create, but those are also used much less frequently by fewer people...so not sure I can make too much of a conclusion from that.

 

[Mr Denove]

Did you find a resolution to this issue? I had the same error message and eventually Microsoft go involved and put in a 'fix' to resolve the problem. Oddly I could run the same file in 2007 and it would work but had the issue when running 2010. The fix was for every excel user and not sure what the exact fix was but it took that to resolve the problem as it was nothing to do with not being able to find the project or library.

 

[MartinS]

Did you find a resolution to this issue? I had the same error message and eventually Microsoft go involved and put in a 'fix' to resolve the problem. Oddly I could run the same file in 2007 and it would work but had the issue when running 2010. The fix was for every excel user and not sure what the exact fix was but it took that to resolve the problem as it was nothing to do with not being able to find the project or library.

No, it's an odd one as am now finding that, although we see issues less often, when we do, I only have to open the file to fix it. The users get all sorts of issues that either makes the workbook crash when macros are enabled, or doesn't allow them to save the file as it says it's corrupted. I get a call, open the file, save it and then it's working for them again. The only difference I know of is that I have admin rights on my PC, whereas others in the business don't.

 

[Mr Denove]

Are you all on the same version of Excel?

 

[MartinS]

Are you all on the same version of Excel?

Yes, it's managed by IT so we all run the same version of Windows, Office and SharePoint.