Help with a For Next Loop

I have a routine in Access that loops through an Excel workbook and
executes code base on the work*** name.

If the work*** name contains the word 'Stats' than that is a *** I
want to read (the code).

However, I want to process the company level first because I am going
to need to compare the company stats with those of the individual
agency stats.

Here is the code I have:

bolCompanyDone = True
For Each oXlSheet In oXlBook.Worksheets

If InStr(1, oXl***.Name, "Stats", vbTextCompare) > 0 Then
If bolCompanyDone = False Then
If InStr(1, oXl***.Name, "Overall Stats", vbTextCompare)
0 Then
'Subroutine
bolCompanyDone = True
Else ' *** Not The Company One - Get Next ***
End If
Else
If InStr(1, oXl***.Name, "Overall Stats", vbTextCompare)
= 0 Then
'Subroutine (dont want to process company here - its been
done already)
End If
End If
Else ' Name in Work*** Not Stats
End If

Next oXl***

The problem I am having is that once the company is done - I need to
reset the For Next loop and basically start over since the Overall
Stats workbook can be anywhere in the sheets. If I dont restart the
For Next - then I may have already skipped worksheets that I do want to
process.

Now the easy way to solve this is to have two for next loops - One to
first handle the company and the second to handle all the others.

Is that my answer or is there a better way to do this?

Thanks!

.

Quantcast