XL: Linking to Data in a Damaged File (4.0 and Earlier)

• Microsoft Excel for Windows, versions 2.x, 3.x, 4.x
• Microsoft Excel for the Macintosh, versions 1.x, 2.x, 3.x, 4.x

SUMMARY

In Microsoft Excel, you can extract the values from a damaged file by using external references to link to the desired file. The file structure must be complete, otherwise Microsoft Excel cannot read the information.

MORE INFORMATION

Method 1

To use this method, follow these steps:

1. In Microsoft Excel, change the current directory to the directory that contains the damaged file.

2. Open a new worksheet. On the File menu, click New, and then click Worksheet.

3. Type =SHEET.XLS!A1 in cell A1 on the new worksheet, where SHEET.XLS is the name of the damaged sheet. A prompt appears and asks you to indicate which sheet in the workbook you want to link to.

4. Select an area that approximates the width of the damaged file. For example, select A1:H1, and then click Fill Right on the Edit menu.

5. Select the number of rows in the damaged file, for example, A1:H100, and then click Fill Down on the Edit menu.

6. While the cells are still selected, click Copy on the Edit menu.

7. Click Paste Special on the Edit menu. Click Values and click OK.

   This step removes the links to the damaged file and leaves only the data.

Method 2

This is one method of linking to the file. To use this method, follow these steps:

1. Open two new worksheets.

2. In the first sheet, select a range of cells (for example, A1:D10).

3. On the Edit menu, click Copy.

4. Switch to the second sheet and click Paste Link on the Edit Menu.

5. On the File menu, click Links.

6. Click the file name of the first sheet and click Change. A prompt appears and asks you to indicate which sheet in the workbook you want to link to.

NOTE: This procedure creates an array formula. To change a cell that is part of an array, you must either change the whole array or change the array definition so that it does not include that cell.

Changing the Size of an Array

To change the size of an array, follow these steps:

1. Select the range of cells that contain the link formula.

2. Press F2 to active the formula bar.

3. Press and hold the CTRL key in Microsoft Excel for Windows (or the COMMAND key in Microsoft Excel for the Macintosh) and press the ENTER key (or the RETURN key) to turn off the array formula.

4. Select a range on the worksheet that is large enough to accommodate the cells on the damaged worksheet. This range should include the range of cells you originally used to create the link.

5. Press F2 to activate the formula bar again.

6. Change the cell range specified in the link formula to match the range of data in the damaged worksheet.

7. Press CTRL+SHIFT+ENTER in Microsoft Excel for Windows (or COMMAND+RETURN in Microsoft Excel for the Macintosh) to reactivate the array formula.

Removing an Array

To remove an array formula from a range after you use it to link information from another spreadsheet, follow these steps:

1. Select the entire array.

2. On the Edit menu, click Copy.

3. While the array is still selected, click Paste Special on the Edit menu.

4. Specify Values and click OK.

   ARTICLE-ID: Q142117
   TITLE     : Excel: Summary of Methods to Recover Data from Corrupted
               Files

   ARTICLE-ID: Q147216
   TITLE     : XL: Linking to Data in a Damaged Excel 5.0 or 7.0 File

REFERENCES

"Microsoft Excel User's Guide 1," version 4.0, pages 157-158, 365-371 "Microsoft Excel User's Guide 2," version 4.0, pages 126-132 "Microsoft Excel User's Guide," version 3.0, pages 280-282, 307-316