Corruption of Workbooks Stored in SharePoint

MartinS

Active Member
Joined
Jun 17, 2003
Messages
444
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

severynm

Active Member
Joined
Jan 8, 2021
Messages
273
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jun 17, 2003
Messages
444
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,957
Messages
5,767,306
Members
425,403
Latest member
MellieD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top