Corruption: Causes & Solutions?

gravanoc

Active Member
Joined
Oct 20, 2015
Messages
346
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
The point of this post is to get feedback from others on their experiences with corrupt :poop: 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:
1673719886315.png
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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
Back
Top