Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 15 Extract Data from a Corrupt Workbook

Workbook corruption can mean the loss of vital
data, costing you more than just money. This hack explores some
methods that might recover your data.

Workbooks
sometimes become corrupt for no apparent reason. This can cause all
sorts of problems, especially if the workbook is vital and for
whatever reason you have no backup. Lesson 1: always back up your
data somewhere. Realistically, though, this does not always happen,
and corruption can, of course, occur right before your regularly
scheduled backup.

To add to your frustration, even though you know your workbook is
corrupt, you sometimes might still be able to open it and even
perform certain actions in it.

If You Can Open Your Workbook

If
you can open the offending workbook, before doing anything else, be
sure to save a copy of it; otherwise, you might regret it. If you
have a copy, you can always seek professional help!

Now, try opening the workbook in a later version of Excel and simply
saving. Obviously this is not possible if you already are using the
latest version of Excel.

If this doesn't work,
try opening your workbook and saving the file in HTML or HTM format,
then close the file and reopen it, this time saving again in the
format you requiree.g., .xls.

When saving in HTML or HTM format, the following features will be lost: Custom views Unused number formats Unused styles Data consolidation settings Scenarios Natural language formulas (they are converted to standard range references) Custom function categories Strikethrough, subscript, and superscript elements Change History Customized page setup settings for charts that are embedded on a worksheet List settings for ListBoxes and ComboBoxes from the Forms toolbar Conditional formatting that is stored on an XLM macro sheet Also, shared workbooks will no longer be shared. The "Value (Y) axis crosses at category number" setting on the Scale tab of the Format Axis dialog box is not saved if the "Value (Y) axis crosses a maximum category" checkbox is checked. The "Vary colors by point" setting in the Format Data Series dialog box is not saved if the chart contains more than one data series.

Finally, try
opening your file and saving it in SYLK (.slk,
for symbolic link) format. Note that when you save a workbook in this
format, only the active worksheet is saved. So, you will have to do
the same for each worksheet. Reopen the file and save it in a desired
format such as .xls.

If You Cannot Open Your File

If your workbook is corrupt to the point
that you cannot even open it, open your spreadsheet in Microsoft Word
or via the Spreadsheet viewer, which can be downloaded from the
Microsoft web site, then copy your data from the open file. Much of
your formatting, formulas, etc., will, however, be lost.

Next,
open a new workbook and create an external link to the corrupt
workbooke.g., ='C:\Documents and Settings\Raina\My
Documents\[ChookSheet.xls]Sheet1'!A1. Copy this link down
as many rows and across as many columns as needed. Do the same for
each worksheet in the workbook. If you cannot remember any of the
names of the worksheets, create any old sheet name using the correct
filename path, and Excel will display the sheet names for you when
you press Enter.

One
final thing you can do is visit the OpenOffice.org web site and
download the free version of OpenOffice.org. Except for different
names for different tools and commands, OpenOffice.org is very
similar to Excel. OpenOffice.org is based on the same basic
spreadsheet structure as Excel, making it simple for Excel users to
use. In fact, about 96% of the formulas used in Excel can be created
and applied by using the spreadsheet in OpenOffice.org.

To download the free version of OpenOffice.org, go to http://download.openoffice.org/indexl and
download it from the FTP site of your choice. Then install the
program. OpenOffice.org is also available for Macs.

In many cases, your Excel data can be
recovered. However, no VBA code can be recovered due to
incompatibility between OpenOffice.org and Excel.

Sadly, if none of these methods works,
you probably will have to pay to try to have your workbook recovered
with special software. One source where such reputable software (for
Windows) can be purchased belongs to the authors of this book and is
located at http://www.ozgrid.com/Services/corrupt-file-recovery-index.

After
purchase and installation, run the ExcelFix program. Click Select
File, select a corrupt file, and then click Diagnose to recover the
file. You should now see the recovered file in the workbook viewer.
Click Save Workbook to save the workbook into a new readable file
that you can open from Excel.

Also available is a demo version that does not enable you to save the
file, but all versions of the program enable you to start again and
recover as many files as you want.