VBA -To Activate One Open workbook from many

Topic

New Reply

There are several workbooks open and I need to programmatically activate one workbook out of the many open.
The problem is that the open workbook’s name is generated by another system and can change with each occurrence except for the last 4 characters which are “FACT”
The Following attempt did not work:
[codebox]Sub GetWorkbook(str As String) As Workbook
Dim i As Integer
For i = 1 To Workbooks.Count
If Right(Workbooks(i).Name, Len(str)) = “FACT.xls” Then
Workbooks(i).Activate
Exit For
End If
Next

End Sub
[/codebox]

Reply | Quote

Replies

what is the str variable and will its length always be 8? Why not just use 8? or use str if it equals the entire comparison

If Right(Workbooks(i).Name, Len(str)) = str Then

comparison of strings in VB is case sensitive. Are all the names uppercase “FACT” and lowercase “xls”? to make it case insensitive you could use:

If Ucase(Right(Workbooks(i).Name, 8)) = “FACT.XLS” Then

OR
If Ucase(Right(Workbooks(i).Name, len(str))) = ucase(str) Then

Steve

Reply | Quote

Yes
the last 8 Characters will always be “FACT.xls”with the case as is.

Reply | Quote

Then what is the purpose of the parameter variable named str?

If the code feeds something that is not 8 chars long, you will never get a hit…

Steve

Reply | Quote