I have a large workbook that utilizes many Excel features. It is an ongoing data project for over 12 years. it is increasingly behaving "as if corrupted" but so far it has not fallen over the edge. But, I am worried. I wonder if anybody has suggestion on "how to purify"?
The workbook:
The symptoms
So, basically i am worried.
Does anybody have a suggestion as to how to recover all that this workbook is, but remove any possible corruption?
Is it practical to think of a macro that converts every formula to text and then recreates the workbook, and recreates charts series by series, and PivotTables, etc? What about formats? If you just copy formats from one workbook to another cell by cell (with macro) is it likely to end up pasting the corruption anyway? Would you have to interpret the format in one workbook and re-create it in the other?
Or, any suggestions at all for this case?
Thanks!
The workbook:
- 10.5MB
- 38 sheets
- worksheets and chart sheets
- Pivot tables
- Conditional formatting
- tables
- Images
- Many defined names
- Largest sheet 1758 rows by 100 columns currently
- Macros
- UDFs (from an add-in, the BERT add-in, a bridge between Excel and the R-Language)
- Array formulas, and some were changed to have @ sign when Excel version upgraded.
The symptoms
- At times opens up with #REF all over the place. So far it has always fixed itself when opened with "Repair". Happening increasingly often.
- Sometimes all the chart sheets suddenly lose their names when i open it and get named "Chart1", "Chart2", etc. Sometimes fixed with "repair" and sometimes i've had to fix them manually.
- A few days ago macro that i run all the time mysteriously gave an error for this line: ActiveSheet.PivotTables("PivotTable1").Refresh . (Object does not support this method). When it stopped, in the immediate window i typed ?ActiveSheet.PivotTables("PivotTable1").Name and it echoed back "PivotTable1". So hard to tell what that was. Since then no further stop at that line.
So, basically i am worried.
Does anybody have a suggestion as to how to recover all that this workbook is, but remove any possible corruption?
Is it practical to think of a macro that converts every formula to text and then recreates the workbook, and recreates charts series by series, and PivotTables, etc? What about formats? If you just copy formats from one workbook to another cell by cell (with macro) is it likely to end up pasting the corruption anyway? Would you have to interpret the format in one workbook and re-create it in the other?
Or, any suggestions at all for this case?
Thanks!