Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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]

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

  3. #3
    Star Lounger
    Join Date
    Nov 2003
    Location
    Tampa, Florida, USA
    Posts
    62
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yes
    the last 8 Characters will always be "FACT.xls"with the case as is.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •