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
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.
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.
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.
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!
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With