2

I have an issue where certain workbooks (.XLSM) with included macros and conditional formatting are not saving properly on my Windows 10 machine. I have not been able to determine what is triggering the issue...

Upon trying to save even just a minor change (eh: adding a 1 to an empty cell), I encounter the following error:

Errors were detected while saving to 'my_path'. Microsoft Excel may be able to save the file by removing or repairing some features. To make the repairs in a new file, click Continue. To cancel saving the file, click Cancel.

If I try the Continue option, I encounter further errors:

Excel encountered errors during save. However, Excel was able to minimally save your file to my_path.

Even the 'repaired' files cannot be saved. All changes are essentially lost (unless using the temporary solution I describe at the end)

What is strange is that this seems to occur only on my own machine. Some other details about this case:

  • Office 2016 - Excel 32bit
  • Windows 10 21H1
  • All updates applied
  • Same behavior occurs after uninstall and reinstall of Office 2016
  • Same behavior occurs after uninstall using Microsoft's stand-alone removal tool
  • Same behavior in Excel safe mode
  • Same behavior with Add-Ins and COM Add-Ins disabled
  • Same behavior whether file/save path is local (C:\path) or on a network drive
  • Same behavior when saving with new filename
  • Same behavior when saving/exporting to .xlsx, .xlsb (and .xlsm)
  • Error doesn't occur on other Windows machines

The ONLY way to save the document seems to be to save the file as an .xls.

Why is this and what could the matter be with my particular Office installation?

Martin
  • 401
  • 6
  • 15
  • 1
    Depending on how complex your spreadsheet is, you could copy all the content sheet by sheet into a new work book along with vba scripts, export and import forms if required then try saving as a new .xlsm document and see if you get the same issues. – 5202456 Aug 02 '22 at 11:27

2 Answers2

2

Thanks for the other answer which mentioned a suspicion of the date format. Indeed, I continued trying things till I noticed a difference in terms of regional settings. There seemed to be an error between using a European formatted Excel sheet while also having a regional setting following US values.

Explicitly, the problem may come from the List seperator = , vs ;.

To check whether this is the case for you, start in the Windows 10 search bar typing Set regional format > Additional date, time & regional settings > Region. The format with be shown in the Formats tab.

Now if you go under Additional settings... and look at List separator and notice that there are differences between two regions that you are using, this may be the source of your problems.

To solve the issue, I returned the Region format to a European country and everything worked again.

Martin
  • 401
  • 6
  • 15
1

Two solutions which worked for me:

  1. Double-click each module in the VBA project in turn, without changing anything, try to Save. This resolved my issue most of the time.
  2. In the VBA editor, pick Tools > Options, pick General tab, and uncheck Compile On Demand. Try to run any sub and correct any compiler errors, even on subs you no longer use. I use Option Explicit but inside the module I found an old 'test' sub which does not comply with option explicit, I never use the sub so there's no error in my macro but fixing the issue or removing the offending sub allowed the file to save.

As for the root cause, I don't know what causes it but I'm suspicious about date format changes.

starball
  • 20,030
  • 7
  • 43
  • 238
Kyle Strachan
  • 66
  • 5