Check if workbook file is open in another instance of Excel and close if so

Hi,

See the file associated with post #2 in the following thread (API references coded for 32 bit Excel only): http://www.excelforum.com/excel-prog...instances.html

The file contains the Macro DisplayDataForAllOpenExcelInstances() below that displays data about each open Excel File in all instances of Excel which should help you get started. There is also a Macro in the file that closes all open Excel Files in All Instances of Excel, except the file that is running the code. Please NOTE that Macro DisplayDataForAllOpenExcelInstances() overwrites the contents of Sheet 'Main' in the 'Active Workbook'. A runtime error will occur if Sheet 'Main' DOES NOT EXIST.

Please Login or Register  to view this content.

Lewis

Originally Posted by mc84excel

Question as per title ...

1. In total, the topic is not entirely clear, because it is not known whether it is an open file on the local computer, or whether it is an open file from e.g. a company network. Depending on where you open the file, different ways to handle these situations will apply

2. "Open ...For Input ..."

This method is not credible (reliable ?), see 'Open' statement description in Microsoft context help:

"Important - In Binary, Input, and Random modes, you can open a file using a different file number without first closing the file. In Append and Output modes, you must close a file before opening it with a different file number",

so using 'Input' for 'Open' statement you can open an already open file and declare (state, tell) that it has not been opened before.

3. A brief "web query" shows interesting solutions that can be customized to this topic.

A. Solution of Peter Thornton - lists all Excel instances nicely:
https://social.msdn.microsoft.com/Fo...forum=exceldev

B. "Check Whether an Excel File Is Open on a Network"
http://www.java2s.com/Code/VBA-Excel...onaNetwork.htm
and in many, many places ...
... on the forum something would also be found, but I admit that I was not looking here ...