Arrays and Loops

[Geoff]

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

 

[Norman Jones]

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

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
'<<=========

 

[Norman Jones]

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
'<<=========

 

[Geoff]

Hi Norman
Not quite because .Path returns the full workbbok path whereas myPath
returns just the folder name.

Geoff

 

[Norman Jones]

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
'<<=========

 

[Geoff]

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

 

[Geoff]

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

 

[Gary Keramidas]

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

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

 

[Geoff]

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

 

[Geoff]

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