Excel: Macro Statement to Test If File Is Open or Available

The information in this article applies to:

• Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0
• Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0
• Microsoft Excel for OS/2, version 3.0

SUMMARY

The following macro statement will check to see if a particular file is currently open in Microsoft Excel:

   =OR("filename"=DOCUMENTS(3))

MORE INFORMATION

The DOCUMENTS function returns an array of all open filenames. To evaluate this statement, Microsoft Excel converts the single value "filename" into an array of the same size as the one returned by the DOCUMENTS function. Each element in the first array is then evaluated against the corresponding element in the second array. If a match is found, the OR function returns TRUE, indicating that "filename" is currently open.

Example

The following macro statement will test to see if the file TEST.XLS is open. If TEST.XLS is not currently open, the macro will open it. Otherwise, TEST.XLS is activated.

=IF(OR("test.xls"=DOCUMENTS(3)),ACTIVATE("test.xls"),OPEN("test.xls"))

Note that if the file TEST.XLS is not in the current directory, you must include the entire path for the file as the argument to the OPEN command.

This method can also be used to test if a particular file is located in a directory by replacing the DOCUMENTS() function with the FILES() function. For example,

=IF(OR("myfile.xls"=FILES("c:\excel\*.xls")),open("myfile.xls"))

will look for the file "myfile.xls" in the \Excel directory. If it exists, Microsoft Excel will open the file.

REFERENCES

"Function Reference," version 4.0, pages 118-119, 227-228, 248-249, 267-268

"Function Reference," version 3.0, pages 60-61, 126-127, 135-136, 148