When you open a workbook, references may be listed as missing in the References dialog box in the Visual Basic Editor.
The references are listed as missing even though the referenced are stored in the correct location
and have not been moved, renamed, or deleted.
This behavior may cause problems when you run any macros that rely on
references, when you switch to Microsoft Excel 97 from any other version of
Microsoft Excel, or when you open a workbook on a computer that uses a
different folder structure from the computer on which you created the
workbook.
This problem occurs because Microsoft Excel 97 does not automatically
search all the same locations that other versions of Microsoft Excel
search.
To correct this problem, obtain and install Microsoft Excel 97, Service
Release 1 (SR-1). For information about how to obtain this service release,
please see the following article in the Microsoft Knowledge Base:
172475Â
(http://kbalertz.com/Feedback.aspx?kbNumber=172475/EN-US/
)
OFF97: How to Obtain and Install MS Office 97 SR-1
If you are unable to install Microsoft Excel 97 SR-1, you can work around
this problem by doing any of the following:
- Move the referenced files into the folders in which they existed when
you created the references.
- Move the referenced files into the same folder as the referencing
file.
- Move the referencing file into the same folder as the referenced
files.
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. This problem was corrected in
Microsoft Excel 97 SR-1.
When you create a workbook that uses Visual Basic for Applications macros
in Microsoft Excel, you can create references to other files, such as
workbooks, add-ins, XLL files, dynamic-link library files (DLLs), or object
libraries (OLBs). To create a reference in Microsoft Excel 97, follow these
steps:
- On the Tools menu, point to Macro, and click Visual Basic Editor. Or,
press ALT+F11.
- In the Visual Basic Editor, click References on the Tools menu.
- If the file to which you want to create a reference is already in the
list, click to select the check box next to the reference.
Otherwise, click Browse. Use the Add Reference dialog box to select
the file to which you want to create a reference, and then click Open.
- Click OK.
When you create a reference to another file, the path to that file is
stored in the workbook.
When you open the workbook, Microsoft Excel may search for referenced files
in the locations that are listed in the following table.
Location Description
----------------------------------------------------------------------
1 The folder in which the file existed when you created the
reference
2 The folder in which the referencing file (the workbook you
are opening) currently exists
3 The folder in which the Microsoft Excel progrm, Excel.exe,
currently exists
4 The Windows folder (for example, C:\Windows)
5 The Windows\System folder (for example, C:\Windows\System)
6 Any folders listed in the PATH statement in your
C:\Autoexec.bat file
7 Any folders listed in the following key in the registry:
HKEY_CURRENT_USER\Software\Microsoft\Office\8.0\Excel\
Microsoft Excel\AddIn Path
NOTE: This key is present only if you install Microsoft
Excel 97.
Different versions of Microsoft Excel search for referenced files in
different locations. The locations that the different versions search are
listed in the following table.
Version 1* 2 3 4 5 6 7
-----------------------------------------------------------------
Microsoft Excel 5.x yes yes yes yes yes yes
Microsoft Excel 7.0 yes yes yes yes yes yes
Microsoft Excel 97 yes yes
Microsoft Excel 97 SR-1 yes yes yes yes yes yes yes
*The numbers represent folders that are described in the previous table.