Clean/Purify possible corrupted Excel file?

[Mdarkx]

Hello,

I have an Excel project I've been working on last couple of months. Contains about 10 sheets, 15 queries, and 3 macros. Not a huge file, but I would hate to have to start over.

Lately I've encountering a few issues with the error "Could not save changes due to file sharing violation". File is saved locally. Sometimes I can re-create the issues by triggering a macro, updating a query and then saving.

Is there any way to clean/purify the Excel file, to make sure it wont eventually end up corrupted? E.g. I remember hearing about trick saving it as .xlsb and then back to .xlsm. Any other tricks like that?

Running Excel 365 on Windows 11.

 

[rlv01]

It has been my experience that when it comes to file corruption, mostly the 'trick' methods cannot be relied on. One method that has worked for me:

1. Save a backup copy as insurance.
2. Use the VBE to export all code modules (worksheet, standard, class)
3. Use the VBE to export all userforms
4. Save the workbook to .xlsx format (to strip out all macros).
5. Close .xlsx file.
6. Re-open .xlsx file (steps 5 & 6 are important).
7. Import all the files exported in steps #2 & #3.
8. Compile (Debug -> Compile VBAProject)
9. If compile is successful, save the file to a new filename as a macro-enabled file (.xlsm).
10. Test.
11. Test again.​