Corruption: Causes & Solutions?

[gravanoc]

The point of this post is to get feedback from others on their experiences with corrupt files:

1. How often does it happen to you?
2. What causes it?
3. How to fix them (if possible)?
4. What do you do if they can't be fixed?

Occasionally one of my files becomes corrupted for some generally unknown reason, so I create a new workbook and do the following:

1. Copy the code to Notepad (instead of exporting it as a .bas)
2. Create new modules in the new workbook
3. Copy the code from Notepad to the new modules
4. Copy everything on each tab one-at-a-time using Ctrl A then Ctrl C, then
5. Paste each tab on a new tab in the new workbook (instead of using the Move or Copy feature)
6. Use some UDFs to determine old column widths & heights, shape sizes, etc. Sometimes the formatting carries over ok with the paste, but usually there are still issues to iron out.

There are still some problems with this:

1. User Forms are difficult to recreate from scratch, especially complex ones, so I usually just export and import these into the new workbook
2. It is very time consuming
3. It doesn't always work, and I'm left with no option but to revert to an older version and hope the same issue doesn't happen again.
4. It doesn't tell me why it happened in the first place

These are the causes from what I've read and my own experience:

1. A crash when saving the file
2. Any other crash or freeze might cause it too, generally with little info to go on
3. Pictures seem to be a problem, and often they get removed if I try to repair the file
4. Shapes and similar objects, especially when you make them complex in an attempt to be artistic
5. User Form issues
6. Other VBA-related causes

The end result of this risk is that I keep a ton of different versions around of the same file, and it ends up looking like this in a folder:

 

[dmt32]

I suspect from its earliest days; Excel has been prone to file corruption & even today using Excel 2021 my copy occasionally just decides all on its own to close down & reopen again although, the data is all in tact.

when I was at work, we would as matter of corporate protocol, guard against workbook corruption risk by first ensuring that a master copy of important workbooks like budgets etc. were kept on a separate file.

None of these workbooks though, held the raw data as this was all stored on the corporate server (Oracle database) which the workbooks connected to. This way, if a workbook became corrupt which from time to time did happen, all we needed to do was bin the corrupt file & re-install from master copy.

I did a similar thing for my daughter place of work about 15+ years ago creating for her a timesheet application add-in that connected to a central database (in this case, this was a master workbook) on their network. On the few occasions she had issues with an install, she simply installed a fresh copy from master file – problem solved.

Maybe its an approach you could consider?

Hope helpful

Dave