Captions for Files

[MikeDBMan]

Elsewhere I have created captions for files I load. Normally I would use the command Windows("myfile").activate but the captioned name is what I need to do:

CurrentWorkBook = ActiveWindow.Caption
Windows(CurrentWorkBook).Activate

But I don't know how to navigate to the current book without knowing it's captioned name. How can I get that property so I can navigate to it?

 

[Firefly2012]

Rather than using Windows/captions, you should use object variables to reference your workbooks. This facilitates very easy referencing of these workbooks in your code:

Dim wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
 
Set wb1 = Workbooks.Open("C:\SomeFile1.xls")
 
Set wb2 = Workbooks.Open("C:\SomeFile2.xls")
 
Set wb3 = Workbooks.Open("C:\SomeFile3.xls")
 
'now you can switch back and forth as required:
 
wb1. Activate
 
'eg to copy a range:
wb1.Sheets(1).Range("A:B").Copy
 
'copy range into wb3:
wb3.Sheets(1).Range("A1").PasteSpecial
 
'now close wb1:
 
wb1.Close SaveChanges:=False
 
'now add a sheet tpo wb2:
 
wb2.Worksheets.Add

Hope that makes sense

 

[MikeDBMan]

Your suggestion makes sense but I am dealing with potentially dozens of files, some of which I may have already checked in and some I may not have. I suppose I could maintain a master list of what is in memory each time I do it, but here is what I am going to do. And I load them all at once and then perhaps do things with some and not with others. Then I need to put the ones I still have open back on SharePoint. So what I devised is this:
I go through all windows and get the number of windows that exist. Then I analyze the caption name and if it meets the criteria of the files I need to check back into SharePoint, I have the caption name.

On Error GoTo OutOfHere
For X = 1 To 155
    Let TheCaption = Windows(X).Caption
Next

OutOfHere:
Let MaxX = X - 1

On Error GoTo 0
'now I have the number of open windows and I can get the caption "name easily enough
For X = 1 To MaxX
    If LCase(Windows(X).Caption) = "projects.xlsm" Then
    Else
       If Windows(X).Caption Like YYYYPP & "*" Then
            Windows(Windows(X).Caption).Activate
            'do what I need.
       end if
    end if
next

 

[Firefly2012]

You can iterate thru all open workbooks (much better than using windows, as a single workbook can have multiple windows open, so you could get unnecesary duplication):

Dim wb As Workbook
 
For Each wb In Application.Workbooks
  If wb.Name Like "YYYYPP*" Then
     wb.Activate
     'do what you need
  End If
Next wb

 

[MikeDBMan]

That is a great tip. I didn't know you could do the wb.activate. I thought you had to use the windows in the command. Thanks a lot! That is much better.