Find out who has Excel file locked for editing

Introduction - Excel locked for editing

Use VBA to find out who has your Excel file “locked for editing.” There are few things more frustrating than trying to open a shared workbook on a network drive and discovering another user has it locked. Save this macro to your spreadsheet and it will tell you when someone else has your file opened and locked.

Most of the time, the “Notify” prompt that appears when the file is locked will automatically tell you who has the file open, but often when files are opened on a network, it gets confused and doesn’t know who has it open. This tutorial will show you a reliable alternative to finding out who has your file locked.

Example - Excel locked for editing

Create a log of who has your file open

'Place these macros in your "ThisWorkbook" object
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next ' ignore possible errors
    If Not ActiveWorkbook.ReadOnly = True Then
        'only try to delete the file if the user has it locked
        Kill ThisWorkbook.Path & "\usage.log" ' delete the file if it exists and it is possible
    End If
    
On Error GoTo 0 ' break on errors
End Sub

Private Sub Workbook_Open()
Dim file1 As Integer
Dim strLine As String
file1 = FreeFile
    If Not ActiveWorkbook.ReadOnly = True Then
        'only add name to the usage log if the user has it locked
        Open ThisWorkbook.Path & "\usage.log" For Append As #file1
        Print #file1, Environ("USERNAME") & " at " & Now()
        Close #file1
    Else
        'if someone else has the file open, find out who
        Open ThisWorkbook.Path & "\usage.log" For Input Access Read As #file1
            Do While Not EOF(file1)
               Line Input #file1, strLine
            Loop
        Close #file1
        MsgBox "The file was locked by following user: " & strLine 'last line of file"
    End If
End Sub

Make powerful macros with our free VBA Developer Kit

Tutorials like this can be complicated. That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Grab them below and you’ll be writing powerful macros in no time.

Tutorial - Excel locked for editing

What this macro does

A version of the example macro above was sent to me by a brilliant wellsr.com reader named Remo. Remo was looking for a way to check if a spreadsheet was opened by a user on his network so he could tell them to close it. In his application, he wanted the code to check the status of the file from MS Access instead of Excel, but I’ve modified the code so it works entirely in Excel.

So how does it work? All you have to do is paste the example macro in the ThisWorkbook object in your VBA editor. You’ll find the ThisWorkbook object in the Project Explorer pane:

Once you do that, save your workbook and close it.

When the next person opens it, the macro will check to see if the file is read-only. If it’s not, it means the user is locking the file, which prohibits other users from editing it.

If the user has the file locked, it will record the user’s username and the date/time into a file called usage.log. The usage.log file shows up in the same folder the spreadsheet is saved.

When another user tries to open the file and the file is read-only, they’ll still get the annoying “Notify” prompt, but then they’ll also get a helpful message reliably saying who has the file locked and when they locked it:

More about the usage.log file

The usage.log file is actually even more valuable than it sounds! Once the user locking your spreadsheet closes the file, the usage.log file is deleted.

That means you really don’t even have to open your spreadsheet to see if someone has your file locked. If you see the usage.log file, that means someone has your spreadsheet open.

You can just open the usage.log file and instantly see who has your workbook locked. Again, you don’t even have to open the spreadsheet to see!

Alternate way to check who has Excel file locked

This isn’t the only way to check who has your file open. There’s actually a clever way to do it using the Windows Management Instrumentation (WMI) and VBA FileSystemObject. This solution was sent to me by Philip Hanebeck:

Function Excel_File_in_use_by(FilePath As String) As String
Dim strTempFile As String
Dim iPos As Integer, iRetVal As Integer
Dim objFSO As Object, objWMIService As Object, objFileSecuritySettings As Object, objSD As Object
iPos = InStrRev(FilePath, "\")
strTempFile = left(FilePath, iPos - 1) & "\~$" & Mid(FilePath, iPos + 1)
Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists(strTempFile) Then
    Set objWMIService = GetObject("winmgmts:")
    Set objFileSecuritySettings = objWMIService.Get("Win32_LogicalFileSecuritySetting='" & strTempFile & "'")
    iRetVal = objFileSecuritySettings.GetSecurityDescriptor(objSD)
    If iRetVal = 0 Then
        Excel_File_in_use_by = objSD.Owner.Name
    Else
        Excel_File_in_use_by = "unknown"
    End If
    Set objWMIService = Nothing
    Set objFileSecuritySettings = Nothing
    Set objSD = Nothing
Else
    Excel_File_in_use_by = vbNullString
End If
Set objFSO = Nothing
End Function

The nice thing about this function is that it doesn’t have to be placed in the workbook you’re interested in. You pass the function the path of the locked Excel workbook and it’ll look for the temporary ~$filename file that’s created in the directory. It queries this temporary file and reports the owner. It won’t work for files saved on a SharePoint, and I couldn’t get it to work on old .XLS files, but it works great for all other Excel files I’ve tested.

Application Ideas

If you’re in charge of a shared schedule or KPI you track on a spreadsheet, it can be so frustrating to discover someone else locked you out of your own work. By adding the example macro in this tutorial to your spreadsheet, the log file it produces will at least tell you who you need to bug to regain access to your workbook!

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below. Once you do that, take a moment to share this tutorial on Facebook and Twitter.