Workbook is read only try again-CodePudding

I have this code that checks to make sure the workbook is not open/in use. How would I modify it to try again in 5 seconds and after 3 tries then send the MsgBox?

 If wBook.ReadOnly = True Then
        MsgBox "Database is in use. Please try after sometimes.", vbookonly   vbCritical, "error"
        
        Exit Sub
    End If

CodePudding user response：

Something like this?

For i = 1 To 3

    If ActiveWorkbook.ReadOnly = True Then
        Application.Wait Second(Now())   5
    Else
        ActiveWorkbook.Activate
        MsgBox "write"
        Exit Sub
    End If

Next
    
If ActiveWorkbook.ReadOnly = True Then
    MsgBox "Database is in use. Please try after sometimes.", vbookonly   vbCritical, "error"
    Exit Sub
End If

CodePudding user response：

You can use an auxiliary method that gives you some flexibility in how much to wait depending on the arguments you pass:

Public Sub TryWriteMode(ByVal book As Workbook _
                      , ByVal numberOfTries As Long _
                      , ByVal secondsWaitAfterFailedTry As Long)
    Const maxSecondsWait As Long = 60
    If book Is Nothing Then
        Err.Raise 91, "TryWriteMode", "Book not set"
    End If
    If numberOfTries < 1 Then Exit Sub
    '
    'Cap seconds
    If secondsWaitAfterFailedTry < 0 Then
        secondsWaitAfterFailedTry = 0
    ElseIf secondsWaitAfterFailedTry > maxSecondsWait Then
        secondsWaitAfterFailedTry = maxSecondsWait
    End If
    '
    Dim i As Long
    Const secondsPerDay As Long = 24& * 60& * 60&
    '
    For i = 1 To numberOfTries
        On Error Resume Next
        book.ChangeFileAccess xlReadWrite
        On Error GoTo 0
        If Not book.ReadOnly Then Exit Sub
        Application.Wait Now()   secondsWaitAfterFailedTry / secondsPerDay
    Next i
End Sub

In your example you could call like this:

If wBook.ReadOnly = True Then
    TryWriteMode book:=wBook _
               , numberOfTries:=3 _
               , secondsWaitAfterFailedTry:=5
End If
If wBook.ReadOnly Then
    MsgBox "Database is in use. Please try again later.", vbOKOnly   vbInformation, "Read-only book"
    Exit Sub
End If