Is there a way to use VBA to know a xlsm file is opened by other person?

Two files: a xlsm file with VBA code (filename: XYZ_application.xlsm), another xlsx file (filename: output.xlsx)

There are around 20 employees in the team.

XYZ_application.xlsm opens an input excel file, and also opens output.xlsx, then pull some data from the input excel file to output.xlsx, then uses another program (non VBA program) to import output.xlsx data into MS SQL database. People prefer to do it this way, there is hard code for file path of output.xlsx in import program.

Ideally, both XYZ_application.xlsm and output.xlsx are closed before being used.

But some people forget to close the files are using XYZ_application.xlsm and/or output.xlsx

There will be issue if multiple people working on output.xlsx at the same time. There should be only one person using the program at a time(everyone tries to import his own data into MS SQL database), since it is not often used program, it is okay that only one person uses the program at a time. The bigger reason is: there is hard code for file path of output.xlsx in import program (written by former employee). We cannot have multiple people working on output.xlsx at the same time, moreover, there is VBA code in XYZ_application.xlsm to open output.xlsx, so output.xlsx cannot be already opened.

Question: Is there a way to put some code in XYZ_application.xlsm Workbook_Open to detect if current file XYZ_application.xlsm is already opened by others and if output.xlsx is already opened. If it is possible, is there a way to know who keeps the file open?

Two approaches ...

#1 You could forgo trying to track who has opened the file/s but placing a timer in each workbook that automatically closes the workbook after "x" minutes.

#2 You can hide a sheet in each workbook that tracks the computer name of each individual that accessed the file with a time stamp and date.

There are plenty of samples on this forum and the internet with complete code for you to search for.

The following function will check if the file is open.

Please Login or Register  to view this content.

Artik

Originally Posted by Artik

The following function will check if the file is open.

Please Login or Register  to view this content.

Artik

The issue: if a file is already open, the second person may open xlsm file as read only and could not run any program.

The following code construction will not allow you to use a workbook that is opened as ReadOnly.

In Thisworkbook module:

Please Login or Register  to view this content.

and in standard module (i.e. Module1):

Please Login or Register  to view this content.

Artik

Originally Posted by Artik

The following code construction will not allow you to use a workbook that is opened as ReadOnly.

In Thisworkbook module:

Please Login or Register  to view this content.

and in standard module (i.e. Module1):

Please Login or Register  to view this content.

Artik

Application.OnTime Now, "CloseMe"
Sub CloseMe()

Could you please explain more how these two lines work together?

If the workbook is opened in read-only mode, the part of the procedure that calls the OnTime method to close the workbook will be executed. If the workbook is opened in full access mode the part of the procedure marked (...) will be executed. Although the OnTime method is defined to run immediately (Now() ), it will not execute immediately, but only after the Workbook_Open procedure has completed.
In theory, you could put the command to close the workbook in Workbook_Open, instead of calling OnTime. However, life experience tells you to do it in a separate thread.

Artik

Thank you very much.