Results 1 to 5 of 5

Check if File locked in excel 2016

This is a discussion on Check if File locked in excel 2016 within the Excel Questions forums, part of the Question Forums category; Hi , I found this function , but I get an error :75 What I am doing wrong? Option Explicit ...

  1. #1
    New Member
    Join Date
    Apr 2017
    Posts
    5

    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
    724

    Default Re: Check if File locked in excel 2016

    Is the path valid?
    Tab indent for sanity.

  3. #3
    Biz
    Biz is offline
    Board Regular
    Join Date
    May 2009
    Location
    Perth, Australia
    Posts
    1,564

    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
    Biz
    Biz is offline
    Board Regular
    Join Date
    May 2009
    Location
    Perth, Australia
    Posts
    1,564

    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

    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

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