is file in use??

  • Hi all

    I want to check if an Excel file is already opened by someone else and if true display a message like 'File in use by please try again in afew minutes.'

    At the moment I have buttons that open workbooks by macros which turn off screen updating and disable alerts, load the file, check for readonly and close the file if readonly is true with a message that file is in use.

    I want to avoid the Excel user option of 'Open as readonly' but display who is using the file.

    Any suggestions appreciated.

  • Hi,

    There are a number of ways to test whether a workbook is open. Here is one:


    (Not sure about the username part - perhaps somebody else can add that).

  • Hi Richie,

    Thanks for the reply, but perhaps to clarify my question:

    Working on a network I need to test if another user has a workbook open on their computer thus making the file readonly to anyone else who wants access to it.

    I do not want to 'share the workbook' and would be nice to obtain the office username as normally displayed by Excel under these conditions without the option of opening readonly.

    BTW the files in question are communal data entry tabels that autoclose after 5 mins, hence my message 'Try again in a few minutes'

  • Insomniac,

    Below is a hotch botch of hobbled together code from various sources plus some of my own ingenuity.

    You may also find J-Walks site of interest on this topic.

    So far from my searching no one has been able to figure a work around for doing what you and I want. It's especially more difficult if not (dare I say) impossible to do this with a hidden worksheet / workbook or addin.

    But luckily I don't understand impossible ;)

    Hope it helps.

    1. J-Walks bits:

    2. My bits: (not as clean as J-Walk and others, but hey I'm fairly new to the VBA thing).

    I'll break it down to steps for easy of explanation.

    A. Open Two New Workbooks.

    B. In the first Workbook under Module 1 place the following function. (I can't remember who's work this is but it's not mine.)

    Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer
    ' This function checks to see if a file is open or not. If the file is
    ' already open, it returns True. If the file is not open, it returns
    ' False. Otherwise, a run-time error will occur because there is
    ' some other problem accessing the file.
    On Error Resume Next ' Turn error checking off.
    filenum = FreeFile() ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As #filenum
    Close filenum ' Close the file.
    errnum = Err ' Save the error number that occurred.
    On Error GoTo 0 ' Turn error checking back on.
    ' Check to see which error occurred.
    Select Case errnum
    ' No error occurred.
    ' File is NOT already open by another user.
    Case 0
    IsFileOpen = False
    ' Error number for "Permission Denied."
    ' File is already opened by another user.
    Case 70
    IsFileOpen = True
    ' Another error occurred.
    Case Else
    Error errnum
    End Select
    End Function

    C. In the first Workbook under Module 2 place the following code. (This is mostly my hobbled code.)

    Sub CheckOpenUser()
    On Error Resume Next
    Application.ScreenUpdating = False
    ' Specify the Path to the file of your choice
    If IsFileOpen("P:\Yadda\Yadda\Allocation Numbers AXXXXX.xls") Then
    Application.ScreenUpdating = False
    Application.ActiveWorkbook.Names.Add Name:="UserName", RefersToR1C1:="=Sheet1!R1C1"
    'Specify the Path to the file of your choice and the Range Name UserID
    Application.ActiveCell.FormulaR1C1 = "='P:\Yadda\Yadda\Allocation Numbers AXXXXX.xls'!UserID"
    'Fairly straight forward I'm telling it that the UserID = the UserName
    UserID = Application.Range("UserName").Text
    'A message to give the info
    MsgBox "The Allocation Register is currently being used by: " & UserID & vbCr & vbCr & "Please try again later.", vbInformation
    Application.ActiveWorkbook.Close savechanges:=False
    Workbooks.Open filename:="P:\Yadda\Yadda\Allocation Numbers AXXXXX.xls"
    End If
    End Sub

    Because I'm running my code from an addin I don't have a visible Worksheet to capture the returned Name and feed through to VBA, so I simply make one with screenupdating turned off so that no one sees it appear and then when the message has been read I dump it.

    D. In the Second Workbook ( the one you want to check if it is open and who has it open) place this code under "This Workbook"

    Sub Workbook_Open()
    'Spec up the user
    Users = ActiveWorkbook.UserStatus
    'Spec in the sheet and range for the UserID, My Sheet name = Allocation No. , Range name = UserID (don't forget to name the range !)
    ActiveWorkbook.Sheets("Allocation No.").Activate
    'UserID = the user name
    Application.Range("UserID").Value = Users
    Application.StatusBar = "Updating Records.........Please wait."
    'just something that needed to be done
    Application.StatusBar = ""
    End Sub

    Now what should happen is that when you run CheckOpenUser.

    1. It will check to see if the file is open by someone else
    2. If open it will bring back the username to the temporary workbook and from this fill in the message box.

    Hope it works.



  • Thanks to all replies,

    Richie, no need to appologise I realise my original post was a bit ambiguous.

    Im sure there must be a way to access the username of person who has edit rights to a file?#?#?####?

    At the very least I have realised that if you open the file by macro with 'alerts disabled' the user does not get the 'open read only option' but will still get a notification that the file is available for editing when the read/write user closes the file, better than nothing.

    Thanks again.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!