Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Detecting when file is open (Excel 2003 VBA)

    Hi all

    I am trying to build a system for the real time collection of manufacturing data from several points on the shop floor.

    The data is entered by operators at the end of a task by a combination of barcode reading and typing into a User Form.
    This data is accumulated into a spreadsheet at each workstation.
    When a task batch is finished the spreadsheets upload that portion of the data to another workbook on the server.

    I upload the data by opening the Server Workbook writing and processing the data then closing the server workbook.

    I need a method of detecting if the Server Workbook is available or is being written to by another workstation ?

    Any help would be greatly appreciated.
    Thanks

  2. #2
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Jeddah, Saudi Arabia
    Posts
    243
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Detecting when file is open (Excel 2003 VBA)

    Here is some code that will detect if a file is open.

    Option Explicit

    Sub Test()
    Dim iFileStatus As Integer

    iFileStatus = FileStatus("c:kev.xls") ' Put the fully qualified file name here

    Select Case iFileStatus
    Case 0
    MsgBox "File is not open"
    Case 1
    MsgBox "File is already open"
    Case 2
    MsgBox "File does not exist"
    Case 3
    MsgBox "Path does not exist"
    Case Else
    MsgBox "Unknown error" & Str(iFileStatus)
    End Select

    End Sub
    Function FileStatus(sFile As String) As Integer
    Dim iFileNumber As Integer

    iFileNumber = FreeFile

    On Error GoTo fileError
    Open sFile For Input Lock Read As iFileNumber
    Close iFileNumber

    FileStatus = 0

    Exit Function

    fileError:
    Dim errNumber As Integer

    errNumber = Err.Number

    If errNumber = 70 Then ' Permission denied
    On Error GoTo 0
    FileStatus = 1
    Exit Function
    End If

    If errNumber = 53 Then ' File does not exist
    On Error GoTo 0
    FileStatus = 2
    Exit Function
    End If

    If errNumber = 76 Then ' Path not found
    On Error GoTo 0
    FileStatus = 3
    Exit Function
    End If

    On Error GoTo 0
    FileStatus = errNumber ' Other error

    End Function

    I hope this helps.

    Regards,
    Kevin Bell

  3. #3
    Star Lounger
    Join Date
    Apr 2002
    Location
    Sydney Australia, New South Wales, Australia
    Posts
    72
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Detecting when file is open (Excel 2003 VBA)

    Thanks Kevin

    That works
    I was hoping for something simpler and I am a little surprised that there is not a simple FileStatus function already built into VBA

    Thanks Again

Posting Permissions

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