Repairing Excel Workbooks

[Stephen Spittal]

Good Morning all,

I am having difficulties opening an Excel Workbook which has been a year in development (I know I should have made a backup), I have tried to open and repair the file however, when I do that it I loose all the formula etc. My workbook took references from multiple workbooks to produce a summary sheet.

I have used the Code below before to turn the formula in the active sheet to string so that they could be printed is there away that I can set the reference range to a workbook with the file path such as 'H:\Desktop\Type 26 Detail Design Change Management\[Stage 2 Interactive Change Management Dashboard.xlsm]Dashboard Table'!A2

Sub Macro2()
Dim i As Integer
Dim targetCells As Range
Dim cell As Range
Dim referenceRange As Range
Dim thisSheet As Worksheet

Set referenceRange = activesheet.range("A2")
With referenceRange
  For j = referenceRange.Parent.Index To Sheets.Count
  Set thisSheet = Sheets(j)
  Set targetCells = thisSheet.Cells.SpecialCells(xlCellTypeFormulas, 23)
  For Each cell In targetCells
  If cell.HasFormula Then
  .Offset(i, 0).Value = thisSheet.Name
  .Offset(i, 1).Value = cell.Address
  .Offset(i, 2).Value = "'" & CStr(cell.Formula)
  i = i + 1
  End If
  Next
  Next
End With

End Sub

Thank you all in advance

 

[Hui]

Stephen

Firstly, Welcome to the Chandoo.org Forums

Are you trying to import a single value or a range of values?

In a Macro it is usual to:

Open the 2nd file
Copy a Block of Data to your worksheet as values
Close the 2nd file
Rearrange/process the imported data

Repeat for other files as necessary​

This is easier/quicker done as a Range rather than cell by cell

 

[Hui]

In regards to backups, One place a lot of people forget that they backup files automatically is in emails

If you have sent the file to anybody there will be a copy in your Outbox/Sent folder

 

[Stephen Spittal]

Hui,

Thank you for your reply, I am trying to identify what formula I have used across 88 cols of a table so that that I can rebuild the sheet. I have looked at my email sent items and the sheet is too out of date by about a year to even start reworking it.

 

[GraH - Guido]

Perhaps a wild shot... Change the extension to xls and open in compatibility mode.
I've noticed I get the same kind of errors from time to time, with a reference to "corrupt" xml. It seems to be more frequent with xlsb formats as well. I wonder if exchange between different Office versions is to blame.
I've asked around for tips to avoid this, without great success. Besides back-ups...
I recall reading an article about it, but it was too technical/too difficult English for me. Should find that reference back. If I do, I'll post it.

 

[Stephen Spittal]

Thank you all for your help I am still not able to open the file however I think this is because of a custom number format [$-409]mmm-yy;@ that I have used and now cannot remove it as the workbook wont open