To check whether any file is open

  • Hi,


    I want to know, whether there is any command line to check a specific excel file is open. If it is open, I want to open the file and check whether filter option is activated in any of the sheets. If it is not open, program should give a message saying that this file is opened by so and so user. Could anybody please help me in this

  • Re: To check whether any file is open


    Check these examples


    http://www.ozgrid.com/VBA/IsWorkbookOpen.htm


    This code will check for AutoFilter in a workbook

  • Re: To check whether any file is open


    Hi Kris,


    You may find this link useful (the function is included in my code below):
    http://support.microsoft.com/d…8/6/21.asp&NoWebContent=1


    Note that the AF function will only return true if the data is actually filtered.
    [vba]Sub Test()
    Const strWbkPath As String = "C:\My Documents\ABook.xls"
    Dim wbk As Workbook, ws As Worksheet

    If IsFileOpen(strWbkPath) = True Then
    MsgBox "File already open by another user!"
    Else
    Set wbk = Workbooks.Open(filename:=strWbkPath)
    For Each ws In wbk.Worksheets
    MsgBox "Autofilter status of the worksheet " & ws.Name & _
    vbNewLine & " is : " & AutoFiltered(ws)
    Next ws
    wbk.Close savechanges:=False
    End If

    End Sub


    Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer


    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


    Function AutoFiltered(ws As Worksheet) As Boolean
    With ws
    If Not .AutoFilterMode Then
    AutoFiltered = False
    Exit Function
    End If
    'No filter
    If Not .FilterMode Then
    AutoFiltered = False
    Exit Function
    End If
    'Has filter, but not filtered
    End With
    AutoFiltered = True
    End Function[/vba]HTH

  • Re: To check whether any file is open


    his might be more complete (combines Dave's Code & my AutoFilter suggestion) , but I am not sure if you are referring to a Workbook stored on a network, which would mean the code would need tweakin with the workbook address.

  • Re: To check whether any file is open


    Adapting my earlier code, try


    I have given the user the option to switch off AutoFilter or not.

Participate now!

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