Methods to Recover Corrupted Files in Microsoft Excel Wendal Dorsey Product Support Professional Pre

1
Methods to Recover Corrupted Files in Microsoft
ExcelWendal DorseyProduct Support
ProfessionalPremier Applications
SupportMicrosoft Corporation
2
Topics Covered

• What is file corruption in Microsoft Excel?
• General corrupted file troubleshooting
  troubleshooting corruption in Excel 97
• Troubleshooting corruption in Excel 2000
• Troubleshooting corruption in Excel 2002 (Office
  XP)
• Suggested reading and additional resources

3
Important Notes About This Presentation

• None of the following troubleshooting steps is a
  cure-all there is no magic bullet that will
  work in all cases.
• Known issues can sometimes appear to be file
  corruption. Do not assume that a file is
  corrupted! Check the Microsoft Knowledge Base at
  http//support.microsoft.com/ for known issues
  related to what is happening and/or what steps
  cause the issue to occur.

4
What Is Corruption?

• File does not open
• File does not open you receive an error message
• Excel stops responding (crashes) while attempting
  to open a file
• File opens, but causes problems
• Excel crashes while you are working
• File cannot be saved
• File does not function as expected
• Formulas are not properly calculated
• Some functionality is impaired or is not
  functional

5
What Is Corruption? (2)

• In general, if a file exhibits unexpected
  behavior, will not open, or causes problems to
  the application when it is opened, that file may
  be corrupted.
• Note Corruption occurs in many different forms
  for many different reasons. There is often no way
  to tell what caused a file to become corrupted!

6
General Excel 97 Corrupted File Troubleshooting

• No built-in corruption recovery functionality
  prior to Excel 2002 (Office XP)
• Least number of options in Excel 97
• In all versions, troubleshooting is limited if
  the file does not open
• Attempt to open the file on another computer
• Attempt to link to the file to extract data

7
General Excel 97 Corrupted File Troubleshooting
(2) If the File Does Open

• Manual re-creation of the workbook using copy and
  paste
• Long process with many obstacles (defined names,
  links, potential for copying corruption)
• Workbook recovery Macro utility available on
  Microsoft Web site
• http//download.microsoft.com/download/excel97win/
  util/1/WIN98/EN-US/Recover.exe
• If possible, troubleshoot the file in Excel 2000
  or 2002

8
General Excel 97 Corrupted File Troubleshooting
(3) When Formulas Dont Function Properly

• There are rare occasions when something causes
  functions to not calculate properly or links to
  not update properly.
• Excel files have formula dependency chains based
  on cell links to other cells and files.
• Troubleshoot calculation issues by rebuilding the
  formula dependency chains, as discussed in the
  following slides.

9
Rebuild Formula Dependency Chains

• First, on each sheet in the workbook, replace
  with
• On the Edit menu, choose Replace. The dialog box
  below appears.
• Add the text as shown below, and click Replace
  All.

10
Rebuild Formula Dependency Chains (2)

• After replacing with on each sheet in the
  file (thus converting all formulas to text), your
  formulas will look similar to this

11
Rebuild Formula Dependency Chains (3)

• At this point you should save, close, and then
  reopen the file.
• Next, reverse the process in the first step,
  replacing with on each sheet.

12
Rebuild Formula Dependency Chains (4)

• After you have replaced all with , the
  formulas and their dependency chains have been
  rebuilt.
• If calculation is set to manual, make sure that
  the calculation is done save the file and then
  close it.

13
Excel 2000 Corrupted File Troubleshooting

• The previously discussed methods can also be used
  in Excel 2000.
• In Excel 2000, another method exists Round Trip
  the file through the HTML file format.
• Follow the steps on the following slides.

14
Round Trip a File Through HTML

• With the file open, choose Save as Web Page from
  the File menu

15
Round Trip a File Through HTML (2)

• In the resulting Save As dialog box, make sure
  that entire file is selected, and save the file
  as HTML to an empty folder

16
Round Trip a File Through HTML (3)

• During the save process, you will be informed of
  what features in your workbook cannot be
  converted to HTML. Nothing that affects the data
  will be lost.

17
Round Trip a File Through HTML (4)

• After you have saved the file in HTML file
  format, close and reopen the file.
• When the file has been reopened, reverse the
  process, by choosing Save As from the File menu.

18
Round Trip a File Through HTML (5)

• In the Save As dialog box, choose Microsoft Excel
  Workbook as the Save as type.

19
Round Trip a File Through HTML (6)

• Saving the file with a new file name completes
  the Round Trip process.

20
Excel 2002 Corrupted File Troubleshooting

• In Excel 2002 (Office XP version), corrupted file
  recovery features have been built in
• Repair file or extract data
• Rebuild of dependency chain during recalculation
• The repair or extract functionality is automatic
  when a file is opened, but can be startd manually
• Excel 2002 attempts recovery only on Excel
  version 5.0 and later file formats

21
Excel 2002 File Recovery Functionality

• Three File Open modes in Excel 2002. They are
  indicated by status bar text
• Normal mode status bar states Opening
  
• Repair File mode - status bar states Repairing
  
• Extract Data mode status bar states Recovering
  
• Failure in one open mode automatically starts the
  next mode

22
Repair File Mode

• Opens the file slowly, performing consistency
  checks
• If corruption is detected, it is either repaired
  or removed (if either is possible)

23
Repair File Mode (2)

• If a file is repaired in Repair File mode, a log
  is produced and the document recovery work pane
  is displayed

24
Repair File Mode (3)

• You can access the repairs log by clicking the
  file in the work pane, which causes a menu to
  appear

25
Repair File Mode (4)

• Click the Show Repairs menu item to see a dialog
  box with the log text

26
Extract Data Mode

• If Repair File mode is unsuccessful, Excel
  automatically starts Extract Data mode.
• Extract Data mode attempts to recover only the
  data and formulas. No VBA objects, OLE objects,
  graphics, or formatting is recovered.
• Extract Data mode cannot recover formulas with
  references that are not on the same sheet, or
  formulas that refer to named ranges.

27
Extract Data Mode (2)

• The text in the resulting log file indicates that
  Repair File mode was unsuccessful

28
Excel 2002 and Crashes During File Open

• Office XP also has crash recovery functionality,
  which works in conjunction with Excel corrupted
  file recovery functionality
• If a file being opened causes Excel to crash,
  Excel automatically escalates to the next open
  mode after Excel is restarted by Office Watson

29
Excel 2002 and Crashes During File Open (2)

• The Office Watson dialog box allows you to
  attempt to recover from a crash

30
Excel 2002 and Crashes During File Open (3)

• If a file being opened crashes Excel in all three
  open modes, it is added to the Disabled Items
  list in the local machine registry.
• This list is viewable by selecting About
  Microsoft Excel from the Help menu.
• Clicking the Disabled Items button in the
  lower-right corner of the About Microsoft Excel
  dialog box produces the list seen on slide 31.

31
Excel 2002 and Crashes During File Open (4)
32
Excel 2002 and Crashes During File Open (5)

• After a file has been disabled, Excel warns you
  if you attempt to open the file again

33
Manually Starting File Recovery

• The file recovery process can be manually started
  from the File Open dialog box
• The Open button is split, with a drop-down menu
  providing the Open and Repair option

34
Manually Starting File Recovery (2)

• When manually started, file recovery allows you
  to choose between Repair and Extract Data modes.
  
• Even when started manually, Extract Data mode is
  automatically attempted, if Repair File mode
  fails.

35
Manually Starting File Recovery (3)

• If you choose Extract Data mode, you can then
  choose how Excel should handle formulas with
  unrecoverable references.
• Excel can convert those formulas to values, or
  can attempt to recover as much of the formula as
  possible

36
Rebuilding Formula Dependency Chains in Excel 2002

• Excel 2002 has a new feature that rebuilds
  formula dependency chains during calculation.
• Keystrokes to start this functionality
• CTRLALTSHIFTF9
• Note This functionality is not equivalent to
  replacing with as was discussed earlier.
  This functionality does not convert the formulas
  to text, as was done in the previously mentioned
  process.

37
Points to Remember

• There is no cure-all for corruption!
• Always check the Microsoft Knowledge Base to see
  if what you are experiencing is a known issue.
• If possible, troubleshoot corruption on Excel
  2000 or 2002, where more options exist.

38
Appendix

• Knowledge Base articles about corrupted file
  troubleshooting techniques
• Q179871, XL2000 Methods for Recovering Data
  from Damaged Workbooks
• Q214253, XL2000 Linking to Data in a Damaged
  File
• Q291057, XL2002 Methods for Recovering Data
  from Damaged Workbooks
• Q202283, XL2000 Some Workbook Features Not
  Retained When Saving as a Web Page
• Q142117, XL Summary of Methods to Recover Data
  from Corrupted Files

39
Appendix (2)

• Knowledge Base articles about corrupted file
  troubleshooting techniques
• Q213951, XL2000 Files Corrupted on Save How to
  TShoot Source of Problem
• Q300643, XL2002 Macro to Extract Data from a
  Chart
• Q289273, XL2002 Overview of AutoRecover
  Functions
• Q286017, XL2002 Error Message The Document
  Caused a Serious Error the Last Time It Was
  Opened
• Q289840, XL2002 Registry Keys That Control the
  File Repair Feature

40
(No Transcript)