Rescuing Sheets in Corrupt Workbook

[Guest]

I have a workbook which I suspect is corrupt, or at least becoming so: I'm
getting truly funky characters, saved changes don't "stick" when I close out
and open it back up, etc.

I'd like to just move the active sheets out of the current workbook and put
them in a new workbook to see if that helps. My plan was to move them and
then use "Replace" to delete the file path to the original workbook which
Excel inserts into the formulas. Unfortunately, I can't even get to this
point because the appended formulas are too long and get cut off.

Is there a way to tell Excel NOT to update the formulas to include paths to
external workbooks? I need the formulas to be functional in the new workbook,
so I don't want to convert to values only.

Thanks for any help anyone can offer!

 

[Bernie Deitrick]

Michael,

Try this. For each sheet in your workbook, run the first macro. Then copy the cells and paste as
values into a blank sheet in your new workbook (one for each existing sheet). Then run the second
macro on all sheets in your new workbook. These macros will work on regular formulas, but not
multi-cell or single-cell array formulas.

HTH,
Bernie
MS Excel MVP

Sub FormulaToText()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
myCell.Formula = "'" & myCell.Formula
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub


Sub TextToFormula()
Dim myCell As Range
Dim myCalc As Variant

With Application
.ScreenUpdating = False
myCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
End With

On Error Resume Next

For Each myCell In ActiveSheet.UsedRange
myCell.Formula = myCell.Text
Next myCell

With Application
.ScreenUpdating = True
.Calculation = myCalc
.EnableEvents = True
End With
End Sub

 

[Dave Peterson]

Maybe you could change your formulas to strings.

Select all the cells (before you've copied the sheet)
edit|replace
what: = (equal sign)
with: $$$$$=
replace all

then move/copy the sheet

And when you're ready, just replace the $$$$$= with =.

Don't forget to do the original worksheet, too (if you copied).

 

[Guest]

Actually, I think this would work, but just because it was slightly simpler,
I tried Dave P's solution below first, and that did the job. I think I'll
keep this macro on file, though, as a backup. Thanks for the solution and for
the super fast response!

 

[Guest]

Your idea did the job, Dave: Thanks! I wouldn't have thought of that at all!
Now all I have to do is play around with the spreadsheet to see if the new
workbook addresses the corruption issue. If not, perhaps I'll just call it a
day and get a margarita!