Return the name of the user that currently has workbook open

Suppose you have a XLSM that needs to open a XLSX and have exclusive access to it. However if you are working on a network, there is the possibility that another user already has this XLSX open.
I already have code that will detect if another user has the XLSX open or not. The problem I have is finding a way to determine the name of the user that is currently using this workbook.


What I have tried so far:

1. WriteReservedBy = No point discussing this one further. It doesn't work (websites say so + I tested myself).

2. I found a custom function that looked extremely promising (see code at end of this post) but when tested it couldn't return the name of the user (the strXL returned a complete mess. There was no readable name in it).


My current idea for solving (but I would like help on):

I notice that when another user has a workbook open on a network, Windows creates a hidden workbook while it is open. This hidden workbook exists in the same directory and has the same file name but with a prefix of "~$" added. Now the interesting thing is this: If you look at file properties (in Windows Explorer) the Owner property for the open Workbook remains that of the creator. However the Owner property for the hidden workbook is that of the user who currently has the workbook open. So how do I get the code to return this?


APPENDIX:
This is the code that didn't return the user name. However the LastUser function could be replaced with my idea above.

Please Login or Register  to view this content.

The idea that comes to mind for me is to add a Workbook_Open macro into that shared file. The task of that macro would be to write a TXT file into the same directory with a specific name and include the UserName of the person who opened the file. A Workbook_Close macro would delete that file.

Then you can check for that file in your other macros, if it exists you know someone has it open and can read the text file to see who.

That's a brilliant idea, I wouldn't have thought of that! +1

I only have one issue with the suggestion by JBeaucaire. It would only work where the file to test for open by another user is a XLSM and has code added to workbook_open.

Currently my most pressing need for this solution involves XLSX workbooks.

Going back to my idea in the OP, how would I obtain the Owner property of a Windows file using VBA? (I expect API calls will be needed?)

http://stackoverflow.com/questions/1...en-using-vbscr

For the benefit of posterity, the code from there (vb script, tweak as necessary for VBA):

Please Login or Register  to view this content.

Thanks for pointing this out to me +1

I have tidied the code (what is it with coders not declaring variables?) and made a few minor tweaks. But there are some unexpected issues. If anyone is interested in solving, please see code below...

Please Login or Register  to view this content.

It's coming along. See attached WB. The button runs a file picker which will return a message box as to whether it is open or not and the name of the user.

However this thread is not 100% solved. This code can not handle a workbook on a shared network drive. (See post #6). And I don't know WScript well enough to solve this myself. Any help (especially code) would be appreciated for solving this last obstacle.

Any forum user skilled with scripting? Alternatively is there a WScript equivalent of Excel Forum I could ask?

Oops. Thanks for pointing that out!

Strange. Ran some tests on a open file on a shared network drive and the current file picker function works fine. (No need to convert the Drive into a Shared Path name?)

I'll do some further tests to make sure but will mark as solved for now.

Confirm that code is working successfully on files on shared network drives. Final version of code uploaded.

Please Login or Register  to view this content.

I lost the project so I searched online to find this code again. While I was at it, I updated it.

UPDATE: Just discovered this code doesn't work if the open workbook has read-only file attribute

Thank you for the rep ashishmehra2010.

Unfortunately I don't think it would be possible to return the name of the user where the Excel workbook has a read-only file attribute (because there is no hidden file-lock file created when this workbook is opened).

Having said that, I did some googling and the code below will confirm whether the workbook file is open. You could easily integrate this code into my previous function so that if it appears that no user has it open, run the subroutine below. If this function returns TRUE then this probably means that the workbook is open as a read-only copy (but cant return the UserName)

Please Login or Register  to view this content.