How to tell if an Excel 2007 spreadsheet is open and WHO has it open using VBScript

Question

How do I tell if an Excel 2007 spreadsheet is open and WHO has it open using VBScript?

I am trying to figure out whether or not an Excel workbook is currently open by another user and returning who that user is in my script.

I have already figured out who to determine if the workbook is currently open. It is a workaround, but I basically open the workbook and check if it is read-only. That works perfectly; I have tested it.

I know this is possible because Excel gives you the user who has the file open if you open it via the 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

Also, I run this from the command line:

cscript isWorkbookOpen.vbs

Answer 1

My Genious co-workers reminded me about Excel's "lock" file. When opening excel, you create a hidden system file that holds the persons name of who has the file open. A lock file starts with "~$" before the spreadsheet name. Example:

If you have a spreadsheet called testWorkbook.xlsx it's lock file would be ~$testWorkbook.xlsx located in the same directory.

This is also a faster and easier method to checking if the file is open because your not actually opening the file like I was doing before. Now I am just checking if the lock file exists and if it does, I check who is the "owner" of the lock file and that will be the person who currently have the spreadsheet open. Hopefully this will help someone out 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 guts. I linked to the website where I got that code in the function.

Also, if you don't have the location mapped to the spreadsheet and it is over the network, a UNC path will not work, you have to map a drive. This can be done using the following 2 lines of code:

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

Hopefully someone will benefit from this. I know there isn't much information about how to do this on the web since I have been searching forever for it!