Const strFileToOpen As String = 'G: All Users Jurie Test Book 2.xlsx' If IsFileOpen(strFileToOpen) Then MsgBox strFileToOpen & ' is already Open' & _ vbCrLf & 'By ' & LastUser(strFileToOpen), vbInformation, 'File in Use' Else Workbooks. Open 'G: All Users Jurie Test Book 2.xlsx' End If End Sub Function IsFileOpen(strFullPathFileName As String) As Boolean '// VBA version to check if File is Open '// We can use this for ANY FILE not just Excel! '// Ivan F Moala '// [url]Dim hdlFile As Long '// Error is generated if you try '// opening a File for ReadWrite lock >>MUST BE OPEN! On Error GoTo FileIsOpen: hdlFile = FreeFile Open strFullPathFileName For Random Lock Read Write As hdlFile IsFileOpen = False Close hdlFile Exit Function FileIsOpen: '// Someone has it open! IsFileOpen = True Close hdlFile End Function Function LastUser(path As String) As String Dim text As String Dim strFlag1 As String, strflag2 As String Dim i As Integer, j As Integer strFlag1 = Chr(0) & Chr(0) strflag2 = Chr(32) & Chr(32) Open path For Binary As #1 text = Space(LOF(1)) Get 1,, text Close #1 Dim n As Long, nam As String For n = j - 1 To 1 Step -1 If Mid(text, n, 1) = Chr(0) Then Exit For nam = Mid(text, n, 1) & nam Next LastUser = Name End FunctionThis code works well, however it fails to supply the current user name of the person who has the file open. So far I have know syntax like this; compare to Your code: Sub NameOfSub() ' Where is this part?
Endif EndSub 'My codes': As I tried to express, those code should be in those files which You want to check. That means, those have to run from those files. Just copy those to any macro-sheet.
Or You should modify samples as those could run and give result from 'Your ' Return User name who has file open' -file'. Something like next, NOT TESTED AT ALL! ( no files, no Windows ). Sub OpenBook2() ' ' OpenBook2 Macro ' ' Const strFileToOpen As String = 'G: All Users Jurie Test Book 2.xlsx' If IsFileOpen(strFileToOpen) Then MsgBox strFileToOpen & ' is already Open' & _ vbCrLf & 'By ' & LastUser(strFileToOpen), vbInformation, 'File in Use' Else Workbooks. Open 'G: All Users Jurie Test Book 2.xlsx' End If End Sub Function IsFileOpen(strFullPathFileName As String) As Boolean '// VBA version to check if File is Open '// We can use this for ANY FILE not just Excel! '// Ivan F Moala '// [url]Dim hdlFile As Long '// Error is generated if you try '// opening a File for ReadWrite lock >>MUST BE OPEN!
I am trying to use VBA to determine what user has an excel workbook open on a shared network server. I have written a basic routine with two separate functions that. Each file is opened in turn if it is not already open by another user. If the file is open it is skipped and the update does not take place. I know Excel knows who has the file open as this usually generates a msg box stating the file is locked for editing by 'user_X', open as read only, notify or cancel.
Microsoft Reporting Services Tutorial Pdf. On Error GoTo FileIsOpen: hdlFile = FreeFile Open strFullPathFileName For Random Lock Read Write As hdlFile IsFileOpen = False Close hdlFile Exit Function FileIsOpen: '// Someone has it open! IsFileOpen = True Close hdlFile End Function Function LastUser(path As String) As String Dim text As String Dim strFlag1 As String, strflag2 As String Dim i As Integer, j As Integer strFlag1 = Chr(0) & Chr(0) strflag2 = Chr(32) & Chr(32) Open path For Binary As #1 text = Space(LOF(1)) Get 1,, text Close #1 Dim n As Long, nam As String For n = j - 1 To 1 Step -1 If Mid(text, n, 1) = Chr(0) Then Exit For nam = Mid(text, n, 1) & nam Next LastUser = Name End Function. How To Convert Video In Adobe Media Encoder Mac.
Artcut Software Manual. I believe the short answer is: NO The name that displays in file_is_already_open dialog contains the name entered in Excel Options. Tools.Options.General.Username (if I recall correctly) In large companies, many times it contains a default value: 'Microsoft Customer','MS Office User' or whatever the user entered there: 'I hate working here' In order to do what you're asking for, you'd need administrator access on your network and code to access the correct API call to get the ID of the person in that file. Regards, Ron Coderre Microsoft MVP - Excel.