9

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
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Steven
  • 422
  • 1
  • 4
  • 12
  • 1
    @AnsgarWiechers It is not a duplicate. That post is asking how to determine if an excel spreadsheet is already open, which with my code I have already demonstrated. I'm looking to get WHO has it open. I already know how to get IF it is open. I could be doing it wrong for what I am looking for though. – Steven Mar 22 '13 at 17:47

2 Answers2

13

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!

Steven
  • 422
  • 1
  • 4
  • 12
  • This method is so genius! It did solve me big problem. Thanks for sharing it! – lovechillcool Apr 11 '15 at 00:14
  • BTW, this method only works for Excel 2007 and above version files; it doesn't work for .xls – lovechillcool Apr 15 '15 at 20:47
  • @Steven I have tried using this in VBA and it works fine on my local C:\ drive but when running this code on our server all the is returned from your function is a NULL. And yes, I did map a drive to the file I am querying on the server. Any ideas? – Josh Dec 19 '16 at 00:42
  • @Steven Thank you for mentioning that UNC path will not work! Of all the versions of this functions I've run across, none mentioned this caveat. So others may know, `objFSO.FileExists` does work for UNC file paths however, the WMI class _Win32_LogicalFileSecuritySetting_ does NOT work with UNC file paths. – ChrisPBacon Oct 18 '19 at 17:15
1

Have you tried the Workbook.UserStatus property? Here's a code snippet quote from the 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
Eric
  • 145
  • 1
  • 1
  • 9
  • This gives me what I am looking for IF the spreadsheet is not currently open. My current script opens the file in Read-Only and you cannot retrieve anything from the Workbook.UserStatus property without getting an error stating you cannot access a read-only file... Not sure where to go from here. – Steven Mar 22 '13 at 18:19