工作簿为只读，请重试

Question

我有这个代码检查，以确保工作簿没有打开/在使用中。我如何修改它在5秒内再次尝试，并在3次尝试后发送MsgBox?

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

Exit Sub
End If

Answer 1

您可以使用一个辅助方法，该方法根据您传递的参数为您提供了等待时间的灵活性:

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

在你的例子中，你可以这样调用:

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

Answer 2

像这样?

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