How to find out if an Excel 2007 spreadsheet is open, and WHO opened it with VBScript

Question

How do I know if an Excel 2007 spreadsheet is open, and that WHO opened it with VBScript?

I am trying to find out if an Excel workbook is currently open by another user and which of the users of this user is in my script.

I have already figured out who should determine if the book is open now. This is a workaround, but I basically open the book and check if it is read-only. This works great; I tested it.

I know this is possible because Excel provides the user who has the file open if you open it through a browser.

Here is my code (isWorkbookOpen.vbs):

Set objExcelTestWorkbook = CreateObject("Excel.Application") objExcelTestWorkbook.DisplayAlerts = False 'doesn't display overwrite alert testWorkbookFile = "I:\test_workbook.xlsx" Set objBook = objExcelTestWorkbook.Workbooks.open(testWorkbookFile) If objBook.ReadOnly Then Wscript.echo "The file is read only" Call EndScript Else Wscript.echo "The file is available" Call EndScript End If Function EndScript objExcelTestWorkbook.Workbooks.close objExcelTestWorkbook.Quit WScript.Echo "Closed " & testWorkbookFile WScript.Quit End Function 

In addition, I run this from the command line:

 cscript isWorkbookOpen.vbs 

Answer 1

My brilliant colleagues reminded me of the Excel lock file. When you open excel, you create a hidden system file that stores the names of the people who have the file open. The lock file begins with "~ $" before the table name. Example:

If you have a spreadsheet called testWorkbook.xlsx , then the lock file will be ~$testWorkbook.xlsx located in the same directory.

It is also a faster and easier way to check if a file is open, because you are not actually opening the file, as I did before. Now I just check if the lock file exists, and if so, I check who the "owner" of the lock file is, and that will be the one who has the spreadsheet currently open. Hope this helps someone in the future!

This is my code that works flawlessly:

 testWorkbookLockFile = "I:\~$test_workbook.xlsx" Set objFSO = CreateObject("Scripting.FileSystemObject") If objFSO.FileExists(testWorkbookLockFile) Then WScript.Echo "The file is locked by " & GetFileOwner(testWorkbookLockFile) Else WScript.Echo "The file is available" End If Function GetFileOwner(strFileName) 'http://www.vbsedit.com/scripts/security/ownership/scr_1386.asp Set objWMIService = GetObject("winmgmts:") Set objFileSecuritySettings = _ objWMIService.Get("Win32_LogicalFileSecuritySetting='" & strFileName & "'") intRetVal = objFileSecuritySettings.GetSecurityDescriptor(objSD) If intRetVal = 0 Then GetFileOwner = objSD.Owner.Name Else GetFileOwner = "Unknown" End If End Function 

I want to point out that I did not write the GetFileOwner Function gut. I contacted the website where I got this code in a function.

In addition, if you do not have a location mapped to a spreadsheet, and it is on the network, the UNC path will not work, you need to map the drive. This can be done using the following 2 lines of code:

 Set objNetwork = WScript.CreateObject("WScript.Network") objNetwork.MapNetworkDrive "Z:", "\\Server1\Share1" 

Hope someone benefits from this. I know that there is not much information on how to do this on the Internet, since I have always looked for it!

Answer 2

Have you tried the Workbook.UserStatus property? Here is a quote from a code snippet from Excel VBA Help:

 users = ActiveWorkbook.UserStatus With Workbooks.Add.Sheets(1) For row = 1 To UBound(users, 1) .users = ActiveWorkbook.UserStatus With Workbooks.Add.Sheets(1) For row = 1 To UBound(users, 1) .Cells(row, 1) = users(row, 1) .Cells(row, 2) = users(row, 2) Select Case users(row, 3) Case 1 .Cells(row, 3).Value = "Exclusive" Case 2 .Cells(row, 3).Value = "Shared" End Select Next End With