Check if File locked in excel 2016

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Check if File locked in excel 2016

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Check if File locked in excel 2016

     
    Hi ,

    I found this function , but I get an error :75

    What I am doing wrong?



    Option Explicit


    Sub Sample()
    Dim Ret

    Ret = IsWorkBookOpen("C:\myWork.xlsx")

    If Ret = True Then
    MsgBox "File is open"
    Else
    MsgBox "File is Closed"
    End If
    End Sub

    Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

    On Error Resume Next
    ff = FreeFile()
    Open FileName For Input Lock Read As #ff
    Close ff
    ErrNo = Err
    On Error GoTo 0

    Select Case ErrNo
    Case 0: IsWorkBookOpen = False
    Case 70: IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
    End Function

  2. #2
    Board Regular
    Join Date
    May 2015
    Posts
    851
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if File locked in excel 2016

    Is the path valid?
    Tab indent for sanity.

  3. #3
    Board Regular
    Join Date
    May 2009
    Location
    Perth, Australia
    Posts
    1,622
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if File locked in excel 2016

    Err.Number = 75 if readonly
    Err.Number = 70 if open
    and return false of not open

  4. #4
    Board Regular
    Join Date
    May 2009
    Location
    Perth, Australia
    Posts
    1,622
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if File locked in excel 2016

    Try modified code below

    Code:
    Sub Sample()
        Dim Ret
        Ret = IsWorkBookOpen("C:\myWork.xlsx")
        If Ret = True Then
            MsgBox "File is open"
        Else
            MsgBox "File is Closed"
        End If
    End Sub
    Function IsWorkBookOpen(FileName As String)
        Dim ff As Long, ErrNo As Long, ErrNoDescription As String
        On Error Resume Next
        ff = FreeFile()
        Open FileName For Input Lock Read As #ff
        Close ff
        ErrNoDescription = Err.Description
        ErrNo = Err
        On Error GoTo 0
        Select Case ErrNo
        Case 0:    IsWorkBookOpen = False
        Case 70:   IsWorkBookOpen = True
        Case Else: MsgBox ErrNo & " " & ErrNoDescription, vbCritical
        End Select
    End Function

  5. #5
    New Member
    Join Date
    Apr 2017
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Check if File locked in excel 2016

      
    Hi,

    I always get error = 75 - I thinck it because microsoft changed in excel 2016 the default of openning files to read only

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com