[dba-Tech] Excel sheet tabs & reference to it

[Gustav Brock]

Hi John

It's not completely clear to me what you try to do, but I guess you can use these functions:

<code>
' Searches in the collection of worksheets of a workbook for
' a worksheet named Name or with a name starting with Name.
' Returns the index of the worksheet if found.
' Returns zero (0) if the worksheet name is empty or not found.
'
' To lookup the index of a worksheet with an exact name, use:
'
'   Index = ThisWorkbook.Worksheets("Exact Name").Index
'
' 2017-09-08. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function WorksheetIndex( _
    ByRef Workbook As Workbook, _
    ByVal Name As String) _
    As Integer

    Dim Worksheet   As Excel.Worksheet
    Dim Index       As Integer

    If Workbook Is Nothing Then
        ' Nothing to do.
        Exit Function
    ElseIf Name <> "" Then
        ' Loop worksheets.
        For Each Worksheet In Workbook.Worksheets
            If InStr(1, Worksheet.Name, Name, vbTextCompare) = 1 Then
                Index = Worksheet.Index
                Exit For
            End If
        Next
    End If

    Set Worksheet = Nothing

    WorksheetIndex = Index

End Function


' Renames a worksheet in this workbook.
'
' 2017-02-26. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub RenameWorksheet( _
    ByVal Index As Variant, _
    ByVal Name As String)

    CleanWorksheetName Name
    ThisWorkbook.Worksheets(Index).Name = Name
    
End Sub


' Replaces characters in Name that are not allowed in a worksheet name.
' Truncates length of Name to MaxWorksheetNameLength.
' Returns the cleaned name by reference.
'
' 2017-02-26. Gustav Brock, Cactus Data ApS, CPH.
'
Public Sub CleanWorksheetName(ByRef Name As String)

    ' No special error handling.
    On Error Resume Next
    
    ' Maximum length of a worksheet name in Excel.
    Const MaxWorksheetNameLength    As Long = 31
    ' String containing all not allowed characters.
    Const InvalidCharacters         As String = "\/:*?<>[]"
    ' Character to replace not allowed characters.
    Const ReplaceCharacter          As String * 1 = "-"
    ' Character ellipsis: ….
    Const Ellipsis                  As String * 1 = "…"
    
    Dim Length        As Integer
    Dim Position      As Integer
    Dim Character     As String
    Dim TrimmedName   As String
    
    ' Strip doubled spaces.
    While InStr(Name, Space(2)) > 0
        Name = Replace(Name, Space(2), Space(1))
    Wend
    ' Strip leading and trailing spaces.
    TrimmedName = Trim(Name)
    ' Limit length of name.
    If Len(TrimmedName) > MaxWorksheetNameLength Then
        TrimmedName = Left(TrimmedName, MaxWorksheetNameLength - 1) & Ellipsis
    End If
    Length = Len(TrimmedName)
    For Position = 1 To Length Step 1
        Character = Mid(TrimmedName, Position, 1)
        If InStr(InvalidCharacters, Character) > 0 Then
            Mid(TrimmedName, Position) = ReplaceCharacter
        End If
    Next
    
    ' Return cleaned name.
    Name = TrimmedName

End Sub
</code>

/gustav

________________________________________
Fra: dba-Tech <dba-tech-bounces at databaseadvisors.com> på vegne af John Bartow <jbartow at winhaven.net>
Sendt: 10. maj 2018 07:19:25
Til: DBA-Tech (dba-tech at databaseadvisors.com); DBA Office       (dba-office at databaseadvisors.com)
Emne: [dba-Tech] Excel sheet tabs & reference to it

I have a baseball roster, player stats and team stats. Each player gets a sheet with all of their stats per game. That sheet has the players name as the sheet tab name.

At the end of each season all I have to do is type in the new player roster and everything works. Except the Tab names and because the sheets are references for the team totals, the tab names are in the totals function's reference.

Is there some method of finding and replacing a word in the entire workbook - including the name of the sheet tabs?

Or is there a method to reference the tab without using the tab's name?

Thanks,
John B