Hi Geoff,

To close all open files from the specified
folder and any sub folders, try:

'=========
Public Sub Tester()
Dim WB As Workbook
Dim iLen As Long
Const myPath As String = "C:\Jobs"

For Each WB In Application.Workbooks
With WB
If .Path Like myPath & "*" Then
.Close savechanges:=False
End If
End With
Next WB
End Sub
'<<=========


---
Regards.
Norman

"Geoff" wrote in message
...
Hi Norman
Not quite because .Path returns the full workbbok path whereas myPath
returns just the folder name.

Geoff

"Norman Jones" wrote:

Hi Geoff,

Let me retry; cut and paste ailed me!

Perhaps I have missed something, but
will the following code not achieve your
objective?

'=========
Public Sub Tester()
Dim WB As Workbook
Const myPath As String = "C:\Jobs"

For Each WB In Application.Workbooks
With WB
If .Path = myPath Then
.Close savechanges:=False
End If
End With
Next WB
End Sub
'<<=========


---
Regards.
Norman


"Geoff" wrote in message
...
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