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

Hi Geoff,

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 = "'=========>>
Public Sub Tester()
Dim WB As Workbook
Const myPath As String = "C:\Users\Norman\Documents\Newsgroup"

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

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" <(E-Mail Removed)> wrote in message
news:0D8E002D-5334-4A03-B401-(E-Mail Removed)...
> 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 = '=========>>
Public Sub Tester()
Dim WB As Workbook
Const myPath As String = "C:\Users\Norman\Documents\Newsgroup"

For Each WB In Application.Workbooks
With WB
If .Path = myPath Then
.Close savechanges:=False
End If
End With
Next WB
End Sub
'<<=========
> 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
>

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" <(E-Mail Removed)> wrote in message
news:0D8E002D-5334-4A03-B401-(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
> news:0D8E002D-5334-4A03-B401-(E-Mail Removed)...
> > 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
> >
>
>

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" <(E-Mail Removed)> wrote in message
news:CF8913B6-3957-4A24-AB8F-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:0D8E002D-5334-4A03-B401-(E-Mail Removed)...
>> > 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
>> >
>>
>>

Hi Norman
I've just come up with:

Dim foldername as String

For Each Wb In Application.Workbooks
folderName = Mid(Wb.Path, 1, Len(Wb.Path) - InStrRev(Wb.Path, "\"))
With Wb
If folderName = myPath Then
.Close False
End If
End With
Next Wb

This seems to work but I'm still testing with various scenarios.
Lastly, if I still want an array for other purposes is that set up ok in
your opinion?

Geoff


"Norman Jones" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:CF8913B6-3957-4A24-AB8F-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:0D8E002D-5334-4A03-B401-(E-Mail Removed)...
> >> > 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
> >> >
> >>
> >>
>

oops my mistake, like InStr, InstrRev reads from the left not the right of a
string. So it should be:

For Each Wb In Application.Workbooks
folderName = Mid(Wb.Path, 1, InStrRev(Wb.Path, "\", -1))
If folderName = myPath Then Wb.Close False
Next Wb

Geoff

"Geoff" wrote:

> Hi Norman
> I've just come up with:
>
> Dim foldername as String
>
> For Each Wb In Application.Workbooks
> folderName = Mid(Wb.Path, 1, Len(Wb.Path) - InStrRev(Wb.Path, "\"))
> With Wb
> If folderName = myPath Then
> .Close False
> End If
> End With
> Next Wb
>
> This seems to work but I'm still testing with various scenarios.
> Lastly, if I still want an array for other purposes is that set up ok in
> your opinion?
>
> Geoff
>
>
> "Norman Jones" wrote:
>
> > 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" <(E-Mail Removed)> wrote in message
> > news:CF8913B6-3957-4A24-AB8F-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > >> news:0D8E002D-5334-4A03-B401-(E-Mail Removed)...
> > >> > 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
> > >> >
> > >>
> > >>
> >

to get the folder, you could use something like this, too:

wpath = Split(ThisWorkbook.Path, "\")
If foldername = wpath(UBound(wpath)) Then
--


Gary


"Geoff" <(E-Mail Removed)> wrote in message
news:297944B0-E4AF-4B1A-A641-(E-Mail Removed)...
> Hi Norman
> I've just come up with:
>
> Dim foldername as String
>
> For Each Wb In Application.Workbooks
> folderName = Mid(Wb.Path, 1, Len(Wb.Path) - InStrRev(Wb.Path, "\"))
> With Wb
> If folderName = myPath Then
> .Close False
> End If
> End With
> Next Wb
>
> This seems to work but I'm still testing with various scenarios.
> Lastly, if I still want an array for other purposes is that set up ok in
> your opinion?
>
> Geoff
>
>
> "Norman Jones" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:CF8913B6-3957-4A24-AB8F-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:0D8E002D-5334-4A03-B401-(E-Mail Removed)...
>> >> > 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
>> >> >
>> >>
>> >>
>>

Hi Gary
As I'm calling this from an addin
wpath = Split(ThisWorkbook.Path, "\")
If myPath = wpath(UBound(wpath)) Then...... returned the Addin folder but:

wpath = Split(Wb.Path, "\")
If myPath = wpath(UBound(wpath)) Then..... returned the folder name without
the root directory.

I started of with:
Public Const myPath As String = "C:\Jobs" which I could just as easily make:
Public Const myPath As String = "Jobs"

So split would suit very well. Thanks.

Geoff

"Gary Keramidas" wrote:

> to get the folder, you could use something like this, too:
>
> wpath = Split(ThisWorkbook.Path, "\")
> If foldername = wpath(UBound(wpath)) Then
> --
>
>
> Gary
>
>
> "Geoff" <(E-Mail Removed)> wrote in message
> news:297944B0-E4AF-4B1A-A641-(E-Mail Removed)...
> > Hi Norman
> > I've just come up with:
> >
> > Dim foldername as String
> >
> > For Each Wb In Application.Workbooks
> > folderName = Mid(Wb.Path, 1, Len(Wb.Path) - InStrRev(Wb.Path, "\"))
> > With Wb
> > If folderName = myPath Then
> > .Close False
> > End If
> > End With
> > Next Wb
> >
> > This seems to work but I'm still testing with various scenarios.
> > Lastly, if I still want an array for other purposes is that set up ok in
> > your opinion?
> >
> > Geoff
> >
> >
> > "Norman Jones" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:CF8913B6-3957-4A24-AB8F-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
> >> >> news:0D8E002D-5334-4A03-B401-(E-Mail Removed)...
> >> >> > 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
> >> >> >
> >> >>
> >> >>
> >>
>
>
>

In fact to cater for wbooks in a root directory it should really be:
For Each Wb In Application.Workbooks
With Wb
If InStr(.Path, "\") > 0 Then
If Mid(.Path, 1, InStrRev(.Path, "\") - 1) = myPath Then
.Close False
End If
End If
End With
Next

But that's a lot more than:

If .Path Like myPath & "*" Then ........

So hats off to Norman <bg> Thank you for the simplicity.

Geoff

"Geoff" wrote:

> oops my mistake, like InStr, InstrRev reads from the left not the right of a
> string. So it should be:
>
> For Each Wb In Application.Workbooks
> folderName = Mid(Wb.Path, 1, InStrRev(Wb.Path, "\", -1))
> If folderName = myPath Then Wb.Close False
> Next Wb
>
> Geoff
>
> "Geoff" wrote:
>
> > Hi Norman
> > I've just come up with:
> >
> > Dim foldername as String
> >
> > For Each Wb In Application.Workbooks
> > folderName = Mid(Wb.Path, 1, Len(Wb.Path) - InStrRev(Wb.Path, "\"))
> > With Wb
> > If folderName = myPath Then
> > .Close False
> > End If
> > End With
> > Next Wb
> >
> > This seems to work but I'm still testing with various scenarios.
> > Lastly, if I still want an array for other purposes is that set up ok in
> > your opinion?
> >
> > Geoff
> >
> >
> > "Norman Jones" wrote:
> >
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:CF8913B6-3957-4A24-AB8F-(E-Mail Removed)...
> > > > 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" <(E-Mail Removed)> wrote in message
> > > >> news:0D8E002D-5334-4A03-B401-(E-Mail Removed)...
> > > >> > 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
> > > >> >
> > > >>
> > > >>
> > >