- #EXCEL FILE LOCKED FOR EDITING ON NETWORK SHARE CODE#
- #EXCEL FILE LOCKED FOR EDITING ON NETWORK SHARE FREE#
- #EXCEL FILE LOCKED FOR EDITING ON NETWORK SHARE WINDOWS#
This solution was sent to me by Philip Hanebeck:įunction 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.
#EXCEL FILE LOCKED FOR EDITING ON NETWORK SHARE WINDOWS#
There’s actually a clever way to do it using the Windows Management Instrumentation (WMI) and VBA FileSystemObject. This isn’t the only way to check who has your file open. Again, you don’t even have to open the spreadsheet to see!Īlternate way to check who has Excel file locked You can just open the usage.log file and instantly see who has your workbook locked. If you see the usage.log file, that means someone has your spreadsheet open. That means you really don’t even have to open your spreadsheet to see if someone has your file locked. 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. 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: The usage.log file shows up in the same folder the spreadsheet is saved. If the user has the file locked, it will record the user’s username and the date/time into a file called usage.log. If it’s not, it means the user is locking the file, which prohibits other users from editing it. When the next person opens it, the macro will check to see if the file is read-only. Once you do that, save your workbook and close it. You’ll find the ThisWorkbook object in the Project Explorer pane:
#EXCEL FILE LOCKED FOR EDITING ON NETWORK SHARE CODE#
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.Īll you have to do is paste the example macro in the ThisWorkbook object in your VBA editor. 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. Tutorial - Excel locked for editing What this macro doesĪ version of the example macro above was sent to me by a brilliant reader named Remo. Grab them below and you’ll be writing powerful macros in no time.
#EXCEL FILE LOCKED FOR EDITING ON NETWORK SHARE FREE#
That’s why we created our free VBA Developer Kit and our Big Book of Excel VBA Macros to supplement this tutorial. Make powerful macros with our free VBA Developer Kit 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 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. ReadOnly = True Then 'only add name to the usage log if the user has it locked Open 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 try to delete the file if the user has it locked Kill ThisWorkbook. 'Place these macros in your "ThisWorkbook" object Private Sub Workbook_BeforeClose ( Cancel As Boolean ) On Error Resume Next ' ignore possible errors If Not ActiveWorkbook.
0 kommentar(er)
