Access/Excel VBA - Skip for each loop

Question

The following code edits multiple excel workbooks however before editing the workbooks it first checks to see if the workbook is in read/write mode. If it isn’t then it will close and open the workbook till read/write is active.

My concern is that this loop will continue forever if I don’t incorporate some sort of escape option to the next workbook.

Is there a way of implementing a simple dialog box with a 'Retry’ and ’Skip’ button if the loop reaches a certain number attempts e.g 5

Retry – Reattempt loop

Skip - Skip to the next workbook

For Each i In MyArray

    xl.Workbooks.Open (i)
    'If workbook in read only mode , close and open till read/write is active
    Do Until xl.ActiveWorkbook.ReadOnly = False
        xl.ActiveWorkbook.Close (False)
        If GetAttr(i) = vbReadOnly Then _
            SetAttr i, vbNormal
        xl.Workbooks.Open (i)
    If xl.ActiveWorkbook.ReadOnly = False Then Exit Do
    Loop    'Loop above till read/write active

    '''''More code here when workbook read/write mode
Next

Loop all workbooks. Inside that loop, loop all worksheets in current workbook. — MatthewD
– MatthewD, Commented Aug 25, 2015 at 18:32
You could just increment a variable every time and check if has reached a certain value before looping. — Kyle
– Kyle, Commented Aug 25, 2015 at 18:37

tittaenälg · Accepted Answer · 2015-08-25 18:41:08Z

I would add a counter variable to keep track of how many times the loop has run and then have the form pop up once it crosses a threshold.

I would implement it into your code like below:

For Each i In MyArray

xl.Workbooks.Open (i)

'Set an attempts counter
attempts = 0

'If workbook in read only mode , close and open till read/write is active
Do Until xl.ActiveWorkbook.ReadOnly = False
xl.ActiveWorkbook.Close (False)
If GetAttr(i) = vbReadOnly Then _
SetAttr i, vbNormal
xl.Workbooks.Open (i)
If xl.ActiveWorkbook.ReadOnly = False Then Exit Do

'Increment the attempts counter on each pass
attempts = attempts + 1
if attempts > 4 then
    'Create your dialogue box, maybe have it set the attempts 
    '    counter back to zero and try the loop five more times
    '    before hitting this stop again, or have it exit the loop
    '    if the user chooses to skip
end if
Loop    'Loop above till read/write active


‘’’’’More code here when workbook read/write mode


Next

David G · Accepted Answer · 2015-08-25 20:24:17Z

Alternatively if you're just looking for the msgbox part, you can use something like this to ask the user whether they want to continue and inform them of the attempt number:

Sub doloop()
    Dim i As Integer
    Dim answer
    Do
        i = i + 1
        answer = MsgBox("Try again?", vbQuestion + vbYesNo, "Attempt " & i & " Failed!")
        If answer = vbNo Then Exit Sub
    Loop
End Sub

OR you could ask this before the end of your own loop:

 If 'Condition to check that it failed' Then
        i = i + 1
        If i > 4 then
            answer = MsgBox("Try again?", vbQuestion + vbYesNo, "Attempt " & i & " Failed!")
            If answer = vbNo Then Exit Sub
        End if

       'Code to retry'
 End if

This puts a msgBox in the way of retrying, and exits early before retrying if the user presses No. (Only after 4 failures)

Collectives™ on Stack Overflow

Access/Excel VBA - Skip for each loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related