Hi
Is there a better way to loop through the array of opened workbooks?
The aim is to ensure that workbooks from a specified folder are closed
before the main code is run. Any others may remain open.
In oApp_WorkbookOpen an array is made of wbooks from the specified folder.
In Main it closes those wbooks.
The code works but the loop gets longer as it works its way through the
Workbooks collection because the counter returns to zero each time. And I'm
not sure the array is set up properly either.

I would be grateful for any help.

Geoff

Public Const myPath As String = "C:\Jobs"
Public wbOpenArr() As Variant, wbOpenIndex As Long

In a class module:
Private Sub oApp_WorkbookOpen(ByVal Wb As Workbook)
Dim dirPath As String
dirPath = Left(Wb.Path, 7) '''blunt, but allows for directories with
sub directories

If dirPath = myPath Then
'''fill an array with opened wbook names from Jobs IP folder
ReDim Preserve wbOpenArr(0 To Workbooks.Count)
wbOpenArr(wbOpenIndex) = Wb.Name
wbOpenIndex = wbOpenIndex + 1
End If

End Sub

In a std module:
Public Sub Main()
Dim Wb As Workbook, i as Long

'other stuff

'''close tagged wbooks
If wbOpenIndex 0 Then
For Each Wb In Workbooks
For i = LBound(wbOpenArr) To UBound(wbOpenArr)
If Wb.Name = wbOpenArr(i) Then
Wb.Close False
Exit For
End If
Next
Next
End If

'other stuff

End Sub