Use Array to activate workbooks

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

The following code works fine, in a situation where the workbooks
are originally opened, worked on, and then closed. FilesArray
holds the names of the previously opened workbooks, and can be
used again to open the books:

If FileCounter > 0 Then
Application.ScreenUpdating = False
For LoopCounter = 1 To FileCounter
Workbooks.Open vFilename & FilesArray(LoopCounter), False

In a slightly different situation, the books are still open. Can I modify
the
code and use FilesArray to activate them in turn?

What I'd like to say, is:
Workbooks.Activate vFilename & FilesArray(LoopCounter), False
but that is not supported (Excel2000).

Regards.
 
if filesarray contains just the name of the workbook and not the path
ex: myworkbook.xls vice c:\myfiles\myworkbook.xls

then you can

Workbooks(filesarray(counter)).Activate

Do you know they are all open or do you need to text and either if open,
then activate or if not open open and activate?
 
Stuart,

For XL2000 onward, where InStrRev exists:

Dim myName As String

For LoopCounter = LBound(FilesArray) to UBound(FilesArray)

myName = FilesArray(LoopCounter)
Workbooks(Mid(myName, InStrRev(myName, "\") + 1)).Activate
'Do other stuff here

Next LoopCounter

HTH,
Bernie
MS Excel MVP
 
Many thanks to you both. Will look at your solutions.

Yes, FilesArray holds only the book names, not the path.

I will need to post back here, I fear.

Regards and thanks, both.
 
Back
Top