Backup your files, always take backups

Location:   http://www.mvps.org/dmcritchie/excel/backup.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]
Three things are certain:  Death, taxes, and lost data.
You can put them off but you can’t eliminate them.
 
The only difference between a good hard drive and a bad hard drive, is that the good hard drive hasn't crashed yet.
 
[auto], [demand], [autosave], [xlfiles], [prntscrn], [office], [files], [backup], [offsite], [multipledrives], [system], [runbat], [removable], [briefcase], [readonly], [recovery], [precautions], [prohelp], [prorepair], [prorecovery], [wbrecovery], [backupto_CD], [relCD], [relCloning], [relDataAdmin], [relDonate], [relFileRecovery], [relHDref], [refUtil], [relSettings], [relos], [relOE], [misc], [changed], [whogot], [word], [funstuff],

Automatic Backup (.XLK) when File is Saved (#auto)

Setting up for automatic backup of a file.  (on a single file basis, not global) Now, as to the backups: they are always one save behind the original document.  Excel backups have the original name and the suffix .xlk (with a displayed filetype of “Microsoft Excel Backup File”), and will appear in the same folder as the original.  With the Excel backup you can use Autosave and backup simultaneously.  roneville@sprintmail.com (2000-11-29)

See Workbook Recovery for files left behind from Excel without a file extension such as 7655D900 or 2CB4D900 with a file type of “File” instead of “Microsoft Excel Workbook”,

Immediate solutions -- Backup on Demand -- Excel oriented (#demand)

Don’t forget to check if UNDO will work for you, but you have to use it before you save your file, or before it gets automatically saved like via the Excel Autosave option.  In Excel UNDO is under the Edit menu.  If UNDO won’t work because you were in a macro, you might be able to exit immediately without saving, but you lose all of your changes.  Here is a method to make a quick temporary backup of a workbook, especially if you create a button for the macro.  (remember temporary) 
  Sub backup()
    'Don Guillett, misc, 2000-05-08
    Fname = ActiveWorkbook.Name
    ActiveWorkbook.SaveCopyAs "C:\msoffice\personal\BACKUP\ " & Fname
    ActiveWorkbook.Save
  End Sub
To copy to the same directory with a different filename:
    ActiveWorkbook.SaveCopyAs ActiveWorkbook.Path & "\" & Filename

This version creates a directory, if missing, for the date: 

  Sub backupBYDATE()
    'Don Guillett, misc, 2000-05-08  modified filename for date......
    'documented in http://www.mvps.org/dmcritchie/excel/backup.htm
    Dim dname as String, strTest as String
    dname = "c:\mybackup\B" & Format(Now(), "yyyy_mmdd")
    strTest = Dir(dname, vbDirectory)
    If (strTest = "") Then MkDir (dname)
    ActiveWorkbook.SaveCopyAs dname & "\BK_" & ActiveWorkbook.Name
    ActiveWorkbook.Save    'also save current file
  End Sub

To make this automatic use the Before_Save event as opposed to Before_Close
to save a backup each time you are finished but not if you if you decide
not to save your current changes.   Based on Don Guilett and
Harald Staff (2002-03-07 worksheet.functions).   Install in ThisWorkbook,
modified to allow for several differently named workbooks. 
Note the c:\backups  directory must exist.

  Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
     Cancel As Boolean)
    Application.DisplayAlerts = False  'based on Harald Staff 2002-03-07
    Dim Fname as string
    Fname = ActiveWorkbook.Name
    If UCASE(ThisWorkbook.FullName) <> UCASE("c:\backups\" & Fname) Then _
      ThisWorkbook.SaveCopyAs "c:\backups\" & Fname
    Application.DisplayAlerts = True
  End Sub
Modifying the filename with “BK_” prefix in the SaveCopyAs avoids accidentally opening and confusing it in the last used list with the good production file.

Harold Staff (2000-09-08 in misc) mentioned that SaveCopyAs added to important Excel workbooks prevents someone from saving with Word and messing up because the command will not function in Word therefore the previous SaveAsCopy version is safe.

Robert Bruce (2001-10-04 in programming) mentioned that you can use  Environ("Temp")  to get the path to your PC’s environmental Temp directory.

David McRitchie (2001-10-24 in misc) a variation that includes check for and creation of any required directory based on the filename being created.  Example creates files based on filenames in a selection of cells.  (folderexists, createfolder, savecopyas)

Archiving and Backup (#autosave)

For a more complete solution you can pick up a copy of AutoSave add-in at http://www.oaltd.co.uk/mvp/MVPPage.asp

What files should be backed up in Excel (#xlfiles)

Like anything else your own files should be separated from system, and application software files; since this is in an Excel area will include some files that you might want to make sure that you backup.  You should check the libaries that you find such things as .XLB, .XLS, .XLT, .DOC, .DOT to see if there are other files and if the entire directory should be saved.  My software is on drive H:, my data files are on D: or specific directories on H:.  (update:  My laptop did not come with disk partitioning software so all my files are now on the C: drive, but I do segregate personal files from software)

Restoring an .XLB file from a backup may require reentering the assigned macros, see BarHopper macro to recursively reassign the macro names, seems to reset something internally to get them functional.

The directories and the drive letter will vary, the above happen to be where mine are.

With Office versions after 2000, you have the “Save My Settings Wizard” in Office Tools.  This is very reliable for all Office Apps  In Windows...Start>All Programs>Microsoft Office>Microsoft Office Tools>Save My Settings Wizard (Nick Hodge, 2005-05-30).  Also see how to restore settings.

Firefox:, backup your profile directory.

Quicken, backup the entire folder that contains qdata.QDF You should keep your data separate from the software.  (also see stocks.htm page.

Printing a picture of your Desktop or Application and documenting toolbars (#prntscrn)

In addition to backing up your files you might want to take a picture of your desktop, and you might want to document your toolbars custom buttons and menus (see barhopper).  To print a view of the Desktop, Winkey + D to minimize all application [Winkey shortcuts], then use Print Scrn (Print Screen) key, paste into a word document and print.  To print an application screen use Alt+Print_Scrn to limit copied material to the application -- use to show placement of your Excel toolbars and toolbar icons.

What about backing up or saving other Office Settings (#office)

Save Your Office XP Settings http://office.microsoft.com/assistance/2002/articles/oSaveSettingsWizard.aspx

Office 2000 Save My Settings Wizard http://office.microsoft.com/downloads/2000/o2ksmsdd.aspx (archived copy for text)

What other files should be backed up (#files) ⌘

There are many other files.  Basically you want to backup data that you have entered that can’t be replaced.  Reinstalling software is often faster than trying to back it up.  A error in 1 byte could destroy a software package, but an error of 1 byte in your data may not be serious unless it had been compressed or encrypted, in which case the entire structure may be at risk with the loss of a byte.

Backup (#backup)

Windows NT users must also create a current copy of the Emergency Repair Disk [archived] (START --> HELP --> ERD).  Purpose of ERD:  backs up Registry, keeps record of current desktop, and other things.  If you ever have to use the ERD and it effects restores from the installation CD, you must also rerun your latest Service Pack.  (on NT service packs supercede previous service packs).

 
I am using DOS batch (.bat) files in a DOS Command Window to backup my data.  BAT files are invoked instantly just like .exe files so you want to use an editor to change the text content.  There are only two commands besides HELP that are needed to find the directories and to copy directories and files.  On some systems HELP may not work in which case try  XCOPY /?   or DIR /?
  DIR, Displays a list of files and subdirectories in a directory.
  SUBST, Associates a path with a drive letter.
  XCOPY, Copies files and directory trees. 
HELP DIR  |more
HELP XCOPY |more
DIR d: /s /on
You want to set up your Command Prompt so that it does not start in a system directory, and some other things you might want to change in your properties for Command Prompt. Shortcut Tab:
  Target: %SystemRoot%\system32\cmd.exe -- which you probably already have
  Start in: c:\
  Run: Normal Window
Options Tab:
  Cursor size: Small, Display options: Window
  Buffer size: 50, Number of buffers: 4, [x] Quick Edit mode, [x] Insert mode
Font Tab:
  8x12, Raster Fonts, (each character is 8 screen pixels wide, 12 screen pixels high)
Layout Tab:
  Scree buffer size: Width: 100, Height: 300
  Window size: Width: 100, Height: 39; [x] Let system position window
Colors Tab: (important if want to see black on white)
  Screen text RGB(0,0,0), Screen background RGB(255,255,255)
  Pop-up text RGB(128,0,128), Pop-up background RGB(255,255,255)
Compatibility Tab: [ ] Run in compatibility mode (leave it off)

Hidden Files: You probably won’t get far with these directions unless you unhide hidden files.  .XLB is a hidden file for instance. (topic hidden files)
      My Computer, Tools(or View), Folder Options, View and make sure “show all files” is checked.

Backup,  Backup, Backup always take Backups
I create some online backups with a miscbkup.BAT file that included some coding like this, only a lot more extensive.  The drive where most of my data is on is backed up including a duplicate that exists in the d:\miscbkup subdirectories.  A backup has been stored off-site (one is a lot better than nothing).  The following  not a real backup because it is stored on the same physical drive as the original data but it does provide a means to restore a file that I accidententally incorrectly modify or delete.   Lacking a real backup device you can zip the miscbkup folder to a whole lot of diskettes.

          REM --  d:\miscbkup\miscbkup.bat --
          XCOPY  c:\netscape\users\*.htm   d:\miscbkup\c\netscape\users\*.*    /S /D /H
          XCOPY  c:\*.xlb   d:\miscbkup\c\*.*    /S /D /H
          XCOPY  d:\myfiles\*.xls   d:\miscbkup\d\myfiles\*.*    /S   /D  /H  /Y&nbbsp;
          XCOPY  d:\data\*.*   d:\d\data\*.*   /S  /D  /H

It is real easy to run and real easy to restore a file to when you ran the  miscbkup.BAT batch file.  The beauty of using XCOPY is that if the destination directory does not exist it will be created.  The /S includes subdirectories,  and the /D checks the timestamps to see if it needs to be copied.  The /H copies hidden filess.  The /Y suppresses prompting to confirm you want to overwrite an existing destination file.  There are other options and you should check them out in the Help.  There is also a list option to see what would be copied without actually doing the copy.

Again you need to have off-site backup in case of disaster (#offsite) in some form such as  tape,  diskettes, zip-drive,  CD-R, or if you are on a network to your server -- I am not referring to your ISP, and those servers had better be backed up.  Generally if you are responsible for the data, it is your responsibility to insure that there is backup.   Don’t forget to locate and backup your Email.  Backing up your data only to another hard drive may  not do you much good if you get serious hardware or software problems, you need something stored elsewhere.   If you are a business and you don’t have off-site backup and have a disaster that destroys your computer and data you can say good-bye to your business.

The media that you use for backup is not permanent no matter what anyone tells you.  Rainforest data was put on tape backup probably in a temperature controlled backup, the tapes became brittle and unusable and what was left couldn’t be used because the tape drives no longer exist.   Remember punched cards.  I'd heard of an operations manager who didn't trust tape and backed up their disk drives to punched cards.  Though they were just a backup think about it, the punch card readers aren’t around, those old machines, aren’t around, the programs probably aren’t around and the people who ran them aren’t around.  The point -- your backup is not permanent no matter what anyone tells you.

Backup Considerations using a CD-R for backup
A CD-R can be treated as a big diskette.  If a file is deleted or rewritten on a CD-R the old space is still there and is not reusable.  Files that have been rewritten or deleted cannot be read by ordinary means as they are not accessible through the directory.   A CD-R holds about 650 MB and costs about $.30 US.    A CD-RW which can be rewritten on by reformatting the entire CD-RW once filled up holds only about 450 MB and costs about  $1 US.  Since the purpose of a backup is to keep data for awhile, a CD-R works very well.  Sometimes CD-R($.30) and CD-RW($1).

Backup considerations if you have two hard drives or a partitioned hard drive (#multipledrives)

On a former system my c: drive contained my WinNT system and commercial software programs.  My d: drive contained the files that I put a lot of work into and would lose if all software were reinstalled on a new machine after a disaster.  More specifically my d: drive contains .my data; the data I create such as Excel files, Word files, HTML files, REXX files, REXX macros.   My d: drive contains more data than will fit on a 650 MB CD-R so some folders will be backed up less frequently on another CD-R.    The backup looks very similar to the previous code.  Again this is just an example of some of the code.   Folder dataq1 is not being backed up.  Individual files on the d: drive that are not in a folder are being backed up, followed by selected folders.  I create the basis for this list from  a list of the main directories on the d: drive  --   DIR d:\*.  >> xxxx.bat     and then using my REXX macros to reformat the output, you will probably have to reformat using some other means.
 

REM  -- d:\backupd.bat --
xcopy d:\*.*            f:\bk1\d\*.*  /d
xcopy d:\clubs\*.*      f:\bk1\d\Clubs\*.*     /s /d
xcopy d:\comics\*.*     f:\bk1\d\comics\*.*    /s /d
REM   d:\dataq1\*.*     f:\bk1\d\dataq1\*.*    /s /d
xcopy d:\dcf40\*.*      f:\bk1\d\dcf40\*.*     /s /d
xcopy d:\download\*.*   f:\bk1\d\download\*.*  /s /d
xcopy d:\drivem\*.*     f:\bk1\d\driveM\*.*    /s /d
xcopy d:\excelgrp\*.*   f:\bk1\d\excelgrp\*.*  /s /d
xcopy d:\family\*.*     f:\bk1\d\family\*.*    /s /d
...
xcopy d:\miscbkup\c\*.*   f:\bk1\d\miscbkup\c\*.*    /s /d
xcopy d:\website\*.*    f:\bk1\d\website\*.*   /s /d
xcopy h:\excel2k\*.*    f:\bk1\h\excel2k\*.*   /s /d
...
xcopy d:\images\*.*     f:\bk2\d\images\*.*    /s /d
...
The last entry shown above is copying the files that were previously copied from the c: drive to the miscbkup folder.  Since I am backing up the d: drive, I have no need to create a second backup copy of those files.

Drive F: is the staging area for my backups.  Currently I create 3 CD-RW (bk1, bk2, bk3) with bk1 having the most important frequently changed files.  The letter after that is the drive letter.  The actual backup CD for BK1 does not include BK1 directory -- it would have the directories that have the drive letter (c,d,h) as the major directories.

Some system files you will want to backup   (#system) ⌘

The types of data that I save from C:  drive include my Excel toolbars,  my Netscape bookmarks,  my Email, things that contain my own material that are on the C: drive.   The MISCBKUP.BAT  ptfile also clears out my Netscape, IExplorer, and AOL cache first before it finishes.  The cached files are on my c: drive and don’t get backed up anyway.   It is a good idea to empty the RECYCLE folders before doing backups.   I don’t copy them with my selective XCOPY commands.  The first time I backed up the entire d: drive, and my browser files, I saw some files being backed up unnecessarily including the RECYCLE folders, and cached.

Personal Data and Settings in Web Browsers (#browsers)

You will want to make sure you are exporting your IE Favorites, and IE cookies files to an area you regularly backup.

In Firefox you want to backup your profile directory use about:cache to locate it.  In Opera you might want to check help for the word backup.

Running the backup batch file you created (#runbat)

Currently I begin another CD-R every two months and update it weekly.  With the  /D  option on XCOPY  only the files that got updated are rewritten or added so this can continue for some time if I still had say 20% empty space after the first use.  The CD-R cannot be used on a regular CD-ROM drive until a formatted for such use afterwards -- that takes about two hours.   There is no need to actually make it readable on a regular CD-ROM drive since if you lost all your hardware, you can do this on  another CD-Writer later.   So I wait until it is filled up before making it readable on a normal CD-ROM.

You can use CD-R platters to backup your data and rotate one off-site and continue to use both until one fills up.  When one of them fills up format it to be readable on a regular CD-ROM and retire it as a permanent backup.  Replace the filled up one with a backup to a new CD-R and continue rotating them off-site, on-site.  

CD-RW do not have the capacity nor the longevity of a CD-R but have the advantage of being rewriteable so data will be erased as you delete files or replace them.  A CD-R can only be written to once, but you can use multi-sessions with most CD-R writers.  Software varies on whether you can continue to write later on a CD that has been formatted for use on any CD drive.  There is difference in the longevity of the dyes and reflective surface on a CD.  It is best to only write on the clear part of a CD as the ink in any pen will eventually work through the surface.  Same applies to sticking labels on a CD.  Check current best practices with CD media. 
additional references:  Fred Langa > Langa Letter: Time To Check Your CDRs > November 3, 2003 – CDs thought to be good for a century may be useless within 2 years,  Understanding CD-R & CD-RW - Author’s Notes

Transferring Data to Removeable Media (#removable)

You should never use removeable device media with Excel.  Diskettes especially should never be used with Excel, the data should be copied to the hard drive for use in Excel or from the hard drive to the diskette.  Excel needs space for two copies of the file even when there are no other problems.  Reading a diskette directly from Excel is the most common avoidable destructor of data, another is accessing an Excel file from MS Word.  A zip-drive is an unreliable backup and does not hold as much as the far cheaper CDs.  A diskette is not reliable, even a CD-R, CD-RW is not as reliable as your hard drive, but the fact that they are removeable could cause loss of data.

Thumbdrives:, hold 32MB to 2GB, are very useful for backing up data and transferring data quickly.  But before removing a thumbdrive be sure to close the device or your directories otherwise files may not be actually updated on the device, sharing some of the problems of other removeable media devices.  Just like any other removeable device don't save to a remomveable device directy from Excel. 

Use of Briefcase (#briefcase)

Rob Nobel (2000-05-09 misc) uses 'My documents' folder and any other folders and files such as templates, Custom dictionary, etc. to “My Briefcase”, which he stores on a second HD.  He creates a shortcut to this and places it on the desktop. 

Each time he wants to make sure all files are up to date on the 2nd HD, he right clicks the Shortcut to “My Briefcase” and select “Update All” and presto, in seconds all files are checked whether they are the latest version and if not gives you the option whether you want to update or not.  Which he indiates is very fast and easy, but only suitable for those with that 2nd HD.

You can create a backup batch file using Excel.  In the following example all data is on the c: drive because the hard drive is not partitioned and there is only physically one hard-drive.  Place the resulting column F into a batch file for backing up your data files.

Dir c:\*. /ad /l /on  >  c:\temp\bk1.csv
start c:\temp\bk1.csv

then open using Excel with Data, Text to columns, place columns at locations 15, 20, 28, 36 43 and upon completion place the following formula into cell F1 and fill-down:

=IF(B1<>"<dir> ","","xcopy c:\"""&1& """" & LEFT(REPT(" ",20),MAX(2,20-LEN(E1))) &"d:\bk1\"""&E1&""" /s /d")

Use of the macros TRIMALL, lower_case might be useful.  Paste the resulting column F into a .bat file.  i.e. batbacks.bat
 
 ABCDEF
1albums<dir> 02/22/20024:48aalbums xcopy c:\"albums" d:\bk1\"albums" /s /d
2data<dir> 05/17/200110:40pdata xcopy c:\"data" d:\bk1\"data" /s /d
3progra~1  03/13/20011:52pprogram files 
4scanso~1<dir> 05/19/20012:16pscansoft documents xcopy c:\"scansoft documents" d:\bk1\"scansoft documents" /s /d
5snakeyes<dir> 02/18/20028:37psnakeyes xcopy c:\"snakeyes" d:\bk1\"snakeyes" /s /d
6temp<dir> 05/20/200112:44atemp xcopy c:\"temp" d:\bk1\"temp" /s /d
7webpages<dir> 06/23/20011:18pwebpages xcopy c:\"webpages" d:\bk1\"webpages" /s /d
8windows  03/13/20011:52pwindows 

Files Restored from a CD backup reported as Read-Only (#readonly)

Not all backups to a CD will turn on the Read Only attribute bit, but those that do will cause a problem when you try to update those files once back on your hard drive.  To turn off the Read protect attribute you can group select the files or go to the directory or any directory above and then right-click to get to properties and turn off the read only attribute.  [Additional reference:  problem with Read-Only files]

Using XCOPY in the WinNT DOS Command Window, I personally to not have a problem with files restored from a CD backup.  Those on Windows XP also should be unaffected as it apparently (see article above) will not turn on the Read Only bit. 

For those that have a problem here are some additional solutions to remove the read-only attribute on your restored files.

 

RECOVERY / The Second Half is on Recovery when you fail to have a backup (#recovery) 

Recovery   (#recovery)

Basic Precautions: ⌘   (#precautions)

Copy your file to another dataset, and work with the copy so you don’t mess up the original if there is anything there.  If the file is small enough make another copy onto a diskette and set that aside away from harm.  Don’t run a recovery against your original file, because you may well end up with nothing as a result.

Free Self Help for Damaged Files   (#selfhelp)

Professional Help for Damaged Files   (#prohelp)

I bring you some feedback from a presentation by someone who restores data as a profession.

Sooner or later you will experience a hard disk crash, so back up your data.  Most of the people who spend the bucks to try to restore data, have very good data backup procedures in place but one critical dataset got caught in between.

Some Basic Facts (#prorepair)

  1. If the hard drive suddenly stopped without any indication of any problems, that is a good sign that recovery is an electrical problem and can be repaired.  (connections, wiring, circuit board on drive, etc.)
  2. Data is seldom recovered from a disk crash.  There are outfits that have a customer service department that will tell you they have a 95% recovery rate from hard disk crashes -- they lie.  How would you know if you had a disk crash without paying someone to tell you.  If you had a scratching sound, or a clicking sound your data is doomed.  There is no data once the surface of the disks has been converted to grooves and metal filings (dust).
  3. If the drive can be identified, there is a good chance of recovering some data.
  4. If the drive makes a clicking noise, you have about 20 minutes to two hours of usage left.  Backup your files immediately starting with your critical files first.  DO NOT RUN utilities that write to the disk such as Norton Utilities to fix up disk errors -- it will destroy your data.  Such utilities are designed for repairing minor errors and are not designed for massive failures to rewrite data to another location.

How is data recovered (#prorecovery)

Data recovery is a large field, and involves not only physically damaged media, but deleted files, files with lost passwords,  A customer’s hard disk is removed and cloned so that all work in done on the copy, preserving the customer’s original disk as untouched as possible.

How much is this going to Cost?

A lot.

Your recovery cost, if a file can be recovered will be based on such things as.

  1. You will be asked for a list of files that have to be recovered, the bigger the list the more files that will have to be reassembled back into a file.  The bigger the list the more files segments that will have to be put back together; and, of course, the more it will cost to reassemble the pieces back into a file.  The list of files will be what determines success or failure.  Adding more files to the list will, of course, increase the costs.
  2. The bigger the hard drive, the more searching it will take for files; however, restricting to a smaller partition will help keep down data recovery cost.  Files have to be reassembled from pieces scattered all over a drive.  The bigger the drive the more searching.
  3. Don’t ask for recovery of programs, a one byte error will render program worthless; whereas, 800 bytes missing/lost/damaged in a data file may be hardly noticeable.  If you have data compression on that is another story, any loss there will destroy the files.

What can the user do to prevent a hard disk crash

What can the user do to prevent a hard disk crash on an already purchased system, where the components have already been selected and assembled.

Nothing.  But that is not entirely true as you should protect your equipment from overheating and electrical damage.  Overheating is a at least a major contributor to equipment failure.  Lots of dirt and dust inside computer and poor ventilation are major causes of overheating.  In the home or home office environment, backup drives typically do not include a fan, you should only have backup drives turned on when in actual use and you should have an external fan running on them when in use.  Backup drives when not in use should be unplugged to help avoid electrical damage to equipment when not in use including problems with lightening which can damage nearby unplugged equipment in close proximity with plugged in equipment.

General Rules to avoid data recovery expenses

  1. Take backups.
  2. Back up your data, don’t bother backing up software packages that you simply reinstall.
  3. Put your data on reliable drives, not on the latest drive with the most GB capacity.  All the drives are the same size so if you buy a 8 GB hard drive it will be less intricate and less prone to errors than 17 GB drive, get the picture.

Eliminate unnecessary files

Eliminate unused files for performance such as your browser cache files, your wastebasket (recycle bin), and your temporary files, and then Defrag your hard drives.

Scandisk (chkdsk /f), Open Excel in safe mode

Workbook Recovery, and Recovery of Damaged Workbooks   (#wbrecovery)

You may have a Backup from Excel in the same directory

Preliminary Notes: ★

Copy your file to another dataset, and work with the copy so you don’t mess up the original if there is anything there.  If the file is small enough make another copy onto a diskette and set that aside away from harm.  Don’t run a recovery against your original file, because you may well end up with nothing as a result.

Quick Method to see data ★

Even though the intent would be to recover the workbook you may need to see what would have been the visible data immediately.  If you see the data here it gives more hope of recovery of at least something.  This information is part of the method described in Q214253 found from Q142117.  Place one of the following formulas into cell A1 and replicate across and down using the fill-handle.  The longer formula will prevent reporting zeros for cell that are blank or look blank.  (single quotes around file and sheetname, two double quotes together to indicate no text contained)

   =IF(trim('J:\myfolder\[myfile.xls]mysheet'!A1)="","",'J:\myfolder\[myfile.xls]mysheet'!A1)

   ='J:\myfolder\[myfile.xls]mysheet'!A1

The actual instructions have you use File Open to browse to the folder of the damaged file, then cancel out of it, then File, New to create a new Excel file.  This simplifies formula entry and File save for the new file.  The simplied entry of the better formula would then be:

   =IF(trim('[myfile.xls]mysheet'!A1)="","",'[myfile.xls]mysheet'!A1)

Remove the formulas you just created which are dependent on the corrupt workbook.
   Select ALL, Copy (ctrl+c), Edit, Paste Special, values

Attempt to recover the formulas (and constants):  not tested yet
You could get the formulas with something like:
  =personal.xls!getformula('[workbookname.xls]sheetname'!A1)
or perhaps, something that you use change all to fix up
  ="xxyy=" & personal.xls!getformula('[workbookname.xls]sheetname'!A1)

Read as a webpage Corrupt Files, Jan Karel Pieterse, some options to try and open a corrupt file or retrieve information out of it,

Recovery of workbook, some things that often work ★

Password Recovery

One of the most common recovery problems is associated with password protection.  Password removal for sheet level and workbook level passwords with the almost free (postcard thankyou) Excel® Password Remover*.  Finding the actual password takes much longer but free software is available.  Recovery of file protected passworded files can be very expensive.  A list of recovery sites for of passworded sheets, workbooks and/or files.
* http://www.straxx.com/excel/password.html was formerly http://www.elkraft.ntnu.no/~huse/xlpassword.htm

Data Recovery

Commercial recovery programs or services, the following have been mentioned in the Excel groups and may be worth looking into if you have serious problems with corrupted data.  Checking out the costs might be a further incentive to back up your files. 
http://www.officerecovery.com/excel/ Excel Data Recovery Tool, purchase about $150.  (formerly conceptdata ?); and,
http://www.datamechanix.com
alternative: for Excel 97 check MS KB Q142117 for recovery tool for XL97 only.

MS KB recovering data -- also see related area below

Related Information -- Excel

I have absolutely no experience with any of these and keep hoping that I never will have to use extraordinary means to recover an Excel file.

When Excel works on a file it renames and saves a copy of the original in the same directory with a random name comprised of 8 hex digits with no extension.  If Excel was terminated you may still have this file and you can try opening (a copy of) this file in Excel.  When Excel completes normally the new version is saved again and the original is deleted and the new version is renamed to the original version.  This is part of the reason one should never keep their original files on floppies, the other reason is that floppies are not very reliable.  Renaming of files is a problem to security administration and to AntiVirus programs since this appears to be an attempt to defeat some security measures.

How to find more information on recovery:

go to  http://support.microsoft.com/support/search/   to search the MS KB
and search for article ID Q142117, or a keyword (Boolean) word search for “recover data”, (1) Excel, (2) keyword, (3) recover data, (4) Full Text, Titles with Excerpts.  In the MS KB, “AND” is default.  Logical operators AND, OR, AND NOT can be used in upper or lowercase, parenthesis and symbols are not understood by MS KB.
Check Google Usenet Advanced Search for “Corrupted Files” in newsgroups:  microsoft.public.excel.*

Some more links on recovery of corrupt Excel files

Recovery of other files

Related Information - beyond Excel

Backup to CD by zipping personal folders (#backupto_CD)

Because it is hard to make up a Batch file the will keep putting the right files to a CD.  I'm switching to backing them up manually with Back4win by folders to a CD as zip files, which you can do easily by folder(s), and skipping any file you don't want.  (Alt+N brings up a checklist for backup]. back4win can also to a restore.

The primary thing is to keep your data in your own folders not in system folders and to backup your folders.  For instance my Excel files are at least two levels down in my own folters, and my Outlook Express files are are in my own directories structures so I can just backup a high level directory.

  • [B] Back4Win - Backup Program by Alistair George Manufacturing ★, free for homeuse, backup selections to a CD or CD-RW.  make sure you specify your CD drive when giving a name for backup.  Creates .zip files (suggest using UltimateZip as a companion).  [~2004-06-24 LG].  The read-only bit is fortunately not set on these zipped files on a CD-R. /\
    (Some additional free backup programs [LG 2005-02-14 from PC World])
     
  • Abakt - Abakt...?, Free backup software, providing advanced filtering and command line options. [LG 2005-01-06]
     
  • [B]
    Extract to...
    Extract to here
    Extract to folder
    Create Self-Extractor (.exe)
    E-mail ...zip
    Add to Archives
    Add to "filename".zip
    Zip and E-mail "filename".zip    		 
    UltimateZip - Download ★★, better than WinZip.  though UltimateZip is no longer free for home use, it is still a bargain compared to other payfor software (don't know if there are free alternatives, Windows 2002 has something builtin).  Can unzip to new folder same name, same folder, or anywhere else.  Can look at files without permanently unzipping.  Right-click options.  Note with 3.0 the licensing has changed -- now Shareware for companies, still Freeware for private (home) use(see news, FAQ page, Order).  {uzip(/\), directory}.  Windows XP reportedly has a file unzipper.  Outlook & OE block .zip attachments see Woody's Office Watch articles n45, and n46. 
Additional downloadable software (mostly free)

Related Information -- Backup id="relbackup"

Related Information -- CD, [CD-RW, DVD, DVD-RW, DVD+RW] (#relCD)

CD - Related Information -- General Information

CD - Related Information -- CD Recovery

CD - Related Information -- HP CD Writers

      From http://www.hewlett-packard.com search:  read-only  +cd-writer  +copied

Related Information -- Cleanup (#relCleanup)

Be careful what you ask to be deleted.  You probably do not want to delete MRU files (Most recently used files list), nor your history.  In any case look at each option and the amount of space that wold be saved before giving final okay.  Running a cleanup program might help speed up your system.

Related Information -- Cloning, Ghosting, Mirroring (#relCloning)

Related Information -- Data Administration (#relDataAdmin)

Related Information -- Data Removal, donating old computer (#relDonate)

Related Informaiton -- Documentation and Planning (#documentation)

Make sure you know what you have on your system, you will need to know what to reinstall, what your serial numbes were for software. [also check out Things you should have that are Free which has additional utilities.]

Related Information -- File Recovery   (#relFileRecovery)

Interesting Information -- Hard Drives (HDD) and Floppy Drives   (#relHDref)

Disk Utilities - backup, recovery, status (#refUtil)

Related Information -- System Settings (#relSettings)

Related Information -- NT   (#relos)

Related Information -- Outlook Express (#relOE)

Related Information -- Outlook Express 5.0 and Outlook Express 6.0 (subtopic)

Related Information -- Outlook (#reloutlook)

I don't have outlook but here is some information.

Other Miscellaneous Applications (#misc)

Related Information -- What’s changed (#changed)

Related Information -- Whogot (#whogot)

Related Information -- Word (#word)

Fun Stuff and similar things (#funstuff)

New Stuff (#newstuff)

Haven't updated this page in a long time so just adding this at bottom of Related Information.
This page has many links, you may want to distinguish the links by clicking on the following bookmarklets: 
  highlight links (for IE, or use the Mozilla version of highlight links) providing a yellow background to all links, then use
  int/ext links to distinguish internal links in Red font and External links in Blue font, Use F5 to RESET this page back to normal. 
This page was introduced on April 6, 1999. 
 
[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP] 

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2009,  F. David McRitchie,  All Rights Reserved