ACC95: Can't Use Visual Basic Procedure to Reference Project

• Microsoft Access version 7.0
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Project for Windows, version 4.0
• Microsoft Project for Windows 95, version 4.1

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

In Microsoft Excel, Microsoft Project, or Microsoft Access, you cannot use a Visual Basic procedure to establish a reference to a project or to an object library. A project is any file that contains a Visual Basic for Applications module, such as an .xls, .xla, .mpp, .mdb, or .mda file.

RESOLUTION

To establish a reference in a Visual Basic module to a project when you will be copying the module to another computer, do either of the following:

• In the module that you want to copy to a different computer, use the References dialog box on the Tools menu to reference the project before you copy the module to a different computer. Then, copy the file that contains this module and the referenced project to the same folder (directory) on the second computer. When you use this method, the application (Microsoft Excel, Microsoft Project, or Microsoft Access) finds the referenced project and establishes the reference again when you run the procedure.

  You can also copy the referenced project to the application's folder, the Microsoft Windows folder, the Microsoft Windows\System folder, or any folder that is in the MS-DOS path statement.

  -or-

• Using Microsoft Excel, call a Visual Basic procedure that is located in another project by first opening the project file, and then using the Run method of the Application object and specifying the name of the referenced project and the name of the procedure as in the following example.

  NOTE: Microsoft provides examples of Visual Basic procedures for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This Visual Basic procedure is provided "as is" and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose.

        Workbooks.Open("BOOK1.XLS")
        Application.Run "BOOK1.XLS!Macro1"
  

  Using Microsoft Project, call a Visual Basic procedure that is located in another project by first opening the project file, and then using the Macro method of the Application object and specifying the name of the referenced project and the name of the procedure as in the following example:

        FileOpen "C:\MSOFFICE\WINPROJ\PROJECT1.MPP"
        Macro "[Project1]Module1!macro1"
  

  Using Microsoft Access, there is no way to create a reference to a project through a Visual Basic procedure.

MORE INFORMATION

When you open a Visual Basic module that references a project, the application (Microsoft Excel, Microsoft Project, or Microsoft Access) searches for the referenced project file in the following folders:

• The folder that contained the project file when you first established the reference.
• The folder in which the file that contains the reference is located.
• The folder in which the Microsoft application is installed.
• The folder in which Microsoft Windows is installed.
• The Windows\System folder.
• Any folder that is located in the path statement in the Autoexec.bat file.

   MISSING: Northwind.mdb

REFERENCES

"Visual Basic User's Guide," version 5.0, chapter 4, "Organizing Procedures and Modules in Workbooks"

For more information about the Reference command, search on the phrase "Referencing, other databases," and then "Make a Visual Basic for applications procedure available to more than one database" using the Microsoft Access for Windows 95 Help Index.