Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
like image 777
Steven Avatar asked Mar 21 '13 18:03

Steven


People also ask

How do you know who is opening an Excel file?

First, if you have the file open yourself, you can check the Recent list to see who else has opened it recently. To do this, click the File tab, then click Recent. Next to each file name in the list, you'll see the name of the person who last opened that file.

How do you check who worked on an Excel sheet?

In the Review tab, select Show Changes. Changes are shown in the pane with the most recent changes on top, in the order the changes were made. You can see who made edits, exactly where in the workbook, when, and what they changed.

How do you tell who created a spreadsheet?

Open the folder with Excel files in Windows Explorer. Select the file you need. Right-click and choose the Properties option in the context menu. Move to the Details tab to view the title, subject, author of the document and other comments.


2 Answers

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!

like image 66
Steven Avatar answered Oct 13 '22 00:10

Steven


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 
like image 37
Eric Avatar answered Oct 12 '22 23:10

Eric