Check if file is in Readonly state (If Statement) VBA

Question

All, I have the below code which I require to check for the first available file which is not in a read only state and use the file name as part of a larger module.

For example if Transactions1.csv is in use by another user, then check if Transactions2 is in use etc.

The issue I am having is it always seems to be using Transactions3.csv and ignoring files 1,2 & 4. (Even if they are not in a read only state). Any help would be much appreciated.

Sub CheckIFFileisopen()

'checking multiple files

PMFTransFile = "\\Csdatg04\psproject\Robot\Project Preload\Transactions\Transactions1.csv"
Set TransworkBook = Workbooks.Open(PMFTransFile)
'Check to see if file is already open

If TransworkBook.ReadOnly Then
    ActiveWorkbook.Close
'check if 2nd file is available
PMFTransFile = "\\Csdatg04\psproject\Robot\Project Preload\Transactions\Transactions2.csv"
Set TransworkBook = Workbooks.Open(PMFTransFile)
        If TransworkBook.ReadOnly Then
        ActiveWorkbook.Close

'check if 3rd file is available
 PMFTransFile = "\\Csdatg04\psproject\Robot\Project Preload\Transactions\Transactions3.csv"
 Set TransworkBook = Workbooks.Open(PMFTransFile)

        If TransworkBook.ReadOnly Then
        ActiveWorkbook.Close

 'check if 4th file is available
 PMFTransFile = "\\Csdatg04\psproject\Robot\Project Preload\Transactions\Transactions4.csv"
 Set TransworkBook = Workbooks.Open(PMFTransFile)

     MsgBox "Cannot update Transactions, someone currently using file.  Please try again in a few minutes."
     Application.ScreenUpdating = True
     Application.Calculation = xlCalculationAutomatic
     End
     Exit Sub

   End If
   End If
   End If
 End Sub

score 1 · Answer 1 · edited Aug 07 '18 at 10:10

Since you have same path and similar file names, we can use a loop to check for state of the files. Also you may want to see THIS link on how to check if a file is open or not.

Sub Sample()
    Dim sPath As String, SFile As String
    Dim i As Long
    Dim Ret As Variant

    sPath = "\\Csdatg04\psproject\Robot\Project Preload\Transactions\Transactions"

    For i = 1 To 4
        SFile = sPath & i & ".csv"

        Ret = IsWorkBookOpen(SFile)

        If Ret = True Then
            MsgBox SFile & " is open. Will now check for next File"
        Else
            MsgBox SFile & " is Closed. We will work with this file"
            Exit For
        End If
    Next i
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

Check if file is in Readonly state (If Statement) VBA

1 Answers1