Microsoft KB Archive/96925

= Microsoft Knowledge Base =

Excel: Selecting Alternate Filename Fails with Formula Replace
Last reviewed: September 12, 1996

Article ID: Q96925

The information in this article applies to:


 * Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, versions 2.x 3.0, 4.0, 5.0
 * Microsoft Excel for OS/2, versions 2.x and 3.0

SUMMARY
In Microsoft Excel, you can use the Formula Replace command to find filename references in link formulas and replace them with a different filename. If the filename you type in the Replace With box does not exist in the current directory, Microsoft Excel displays the &quot;File Not Found&quot; dialog box which lists your directories and a list of files in the current directory. Selecting an alternate filename will not replace the text in the Find What box as might be expected. This is by design.

MORE INFORMATION
As long as a match is found on your worksheet for the text string specified in the Find What box, it will be replaced with the text in the Replace With box when you choose the Replace or Replace All button. If you are changing filename references in link formulas and the file does not exist in the current directory, when the &quot;File Not Found&quot; dialog box is displayed, you can change to the directory in which that file does exist. Selecting an alternate filename, however, will have no effect. The text in the Replace With box will not be changed and as a result, the link formula(s) will return the #REF! error value when you choose the Replace or Replace All button.

When you open that sheet and choose the Yes button in the &quot;Update Remote References?&quot; dialog box, and your worksheet contains a link formula for which the file cannot be found, you will receive the &quot;File Not Found&quot; dialog box. Selecting an alternate file will not change the references on your worksheet. However, if you change your directory to the one in which your file exists, your references will be correctly updated.

If you want to change the external reference in your link formulas to a different filename, use the File Links command. This method will change all of the link formulas containing this filename to the new filename. Alternatively, if you want to change only a limited number of your link formulas, you will need to use the Formula Replace command.

NOTE: Before you make any changes, ensure that the file you want to link to exists.