Hello Guys,

We need help with excel. There are many users who forgot to close excel and open files and when someone else tries to open file he gets error message. Is there a way to configure so that excel shows who is user and if i can configure auto close after example 5 min.

Thank you in advance

Hi,

According to your description, your request could be done via some VBA codes. Let me explain it:

When we opening excel, we 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 we 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. Now we are just checking if the lock file exists and if it does, we check who is the "owner" of the lock file and that will be the person who currently have the spreadsheet open.

Sample code:

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

Then, if we have found who locked the file, we still need some codes to close the file automatically after a moment.

I'm not familiar with it, I recommend you post the question to MSDN forum:

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Regards,

George Zhao
TechNet Community Support

HI George,

Thank you for replay. I will do it. Hope that they can give me info on how to configure this because we have a very big issues with this.