'**********************************************************************************************************
' Name:                 uniqueSheetName
' Author:               mielk | 2013-04-16
'
' Comment:              Function checks if the given worksheet name would be unique in the specified
'                       Excel workbook. If not, the name is transformed to be unique by appending
'                       the number.
'
' Parameters:
'   wkb                 Excel workbook for which the uniqueness of the specified worksheet name is
'                       being checked.
'   name                The name to be checked.
'
' Returns:
'   String              If there is no worksheet with such a name in the specified Excel workbook,
'                       the original value of [name] parameter is returned, unless it is not legal
'                       Excel sheet name (in such case it is also transformed to be legal sheet name by
'                       function legalSheetName()).
'
'                       If there is already a file with such a name in the given Excel workbook, the number
'                       in brackets is appended to the original name. If the name is too long afterward,
'                       the proper part of its original value is being cut.
'
'                       Example:
'                       -----------------------------------------------------------------------------------
'                       Let's assume we want to check if the worksheet name [data] would be unique in the
'                       specified Excel workbook.
'                       * If there is no such worksheet in this Excel file, the original name [data] will
'                           be returned without any modifications.
'                       * If worksheet [data] already exists in this Excel file, the function will return
'                           the original name with the number appended - data (1).
'                       * If such worksheet also already exists, the next number is appended and the
'                           function returns data (2) etc.
'
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-04-16        mielk           Function created.
'**********************************************************************************************************
Public Function uniqueSheetName(wkb As Excel.Workbook, name As String) As String
    Const METHOD_NAME As String = "uniqueSheetName"
    Const MAX_LENGTH As Integer = 31
    '------------------------------------------------------------------------------------------------------
    Dim wks As Excel.Worksheet
    Dim strTempName As String
    Dim intIterator As Integer
    Dim intCharsCounter As Integer
    '------------------------------------------------------------------------------------------------------
    

    'First check if the given name is legal sheet name. -------------------------------------------------|
    strTempName = legalSheetName(name)                                                                  '|
    uniqueSheetName = strTempName                                                                       '|
    '----------------------------------------------------------------------------------------------------|
    
    
    'Check if the book to be checked is not closed. -----------------------------------------------------|
    If Not isBookValid(wkb) Then GoTo ObjectDisposedException                                           '|
    '----------------------------------------------------------------------------------------------------|
    
    
    'Function tries to find a worksheet with such name in the specified Excel file. If this operation ---|
    'raised an error, it means there is no such worksheet in the given file and the original name can   '|
    'be returned without any number being appended to it.                                               '|
    On Error GoTo UniqueName                                                                            '|
    Set wks = wkb.Worksheets(strTempName)                                                               '|
    On Error GoTo 0                                                                                     '|
    '----------------------------------------------------------------------------------------------------|
    
    
    'If the worksheet with such a name has been found, the original name has to be modified. ------------|
    If Not wks Is Nothing Then                                                                          '|
                                                                                                        '|
        'Repeat those operations as long as worksheet with the given name exists. -------------------|  '|
        Do                                                                                          '|  '|
            intIterator = intIterator + 1                                                           '|  '|
            uniqueSheetName = strTempName & " (" & intIterator & ")"                                '|  '|
                                                                                                    '|  '|
            'Check if the name with the number appended is not too long -------------------------|  '|  '|
            '(the maximum length is defined by constant MAX_LENGTH).                            '|  '|  '|
            intCharsCounter = VBA.Len(uniqueSheetName)                                          '|  '|  '|
            If intCharsCounter > MAX_LENGTH Then                                                '|  '|  '|
                uniqueSheetName = VBA.Left$(strTempName, _
                   VBA.Len(strTempName) - intCharsCounter + MAX_LENGTH) & _
                   " (" & intIterator & ")"                                                     '|  '|  '|
            End If                                                                              '|  '|  '|
            '------------------------------------------------------------------------------------|  '|  '|
                                                                                                    '|  '|
                                                                                                    '|  '|
            'Function checks if the modified sheet name is unique in the given Excel workbook. --|  '|  '|
            'If it is not, the name is modified again and the next number is appended instead   '|  '|  '|
            'of the current one, i.e. data (2) instead of data (1).                             '|  '|  '|
            'This procedure is being repeated as long as the name is unique in the given        '|  '|  '|
            'Excel file.                                                                        '|  '|  '|
            On Error GoTo UniqueName                                                            '|  '|  '|
            Set wks = wkb.Worksheets(uniqueSheetName)                                           '|  '|  '|
            On Error GoTo 0                                                                     '|  '|  '|
            '------------------------------------------------------------------------------------|  '|  '|
                                                                                                    '|  '|
        Loop Until wks Is Nothing                                                                   '|  '|
        '--------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|
    
    
    
'==========================================================================================================
ExitPoint:
    Exit Function

'----------------------------------------------------------------------------------------------------------
ObjectDisposedException:
    '(...)
    'Put your own error handling here for a case if the given workbook has been closed and it is
    'impossible to check its worksheets' names).
    
    GoTo ExitPoint


'----------------------------------------------------------------------------------------------------------
UniqueName:
    'The worksheet name is unique and can be returned.
    
End Function






'**********************************************************************************************************
' Name:                 legalSheetName
' Author:               mielk | 2012-03-26
'
' Comment:              Function checks if a given name follows the rules for naming Excel worksheets.
'                       Legal Excel sheet name:
'                           *   cannot be empty,
'                           *   must be at most 31 characters long
'                           *   must not contain any of the illegal characters: : ? / \ * [ ]
'                       If the given name doesn't meet these requirements it is being adjusted and this
'                       adjusted value is being returned by the function.
'
' Parameters:
'   name                The name to be checked if it is a legal Excel worksheet name.
'
' Returns:
'   String              The given name after being adjusted to be a proper Excel sheet name.
'                       * If the original name itself is a legal Excel sheet name it is returned without
'                           any changes.
'                       * If the original name is empty, underline character is returned (_).
'                       * If the original name is longer than 31 characters, it is cut off.
'                       * All the illegal characters : ? / \ * [ ] are removed from the original name.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2012-03-26        mielk           Function created.
'**********************************************************************************************************
Public Function legalSheetName(name As String) As String
    Const METHOD_NAME As String = "legalSheetName"
    '------------------------------------------------------------------------------------------------------
    Const ILLEGAL_CHARS As String = ":?/\*[]"
    Dim intChar As Integer
    Dim strChar As String
    Dim strIllegalChars As String
    '------------------------------------------------------------------------------------------------------

    
    'Function iterates through all characters in the original name and removes the illegal ones. --------|
    For intChar = 1 To VBA.Len(name)                                                                    '|
        strChar = VBA.Mid$(name, intChar, 1)                                                            '|
                                                                                                        '|
        '--------------------------------------------------------------------------------------------|  '|
        If VBA.InStr(1, strIllegalChars, strChar) = 0 Then                                          '|  '|
            legalSheetName = legalSheetName & strChar                                               '|  '|
        End If                                                                                      '|  '|
        '--------------------------------------------------------------------------------------------|  '|
                                                                                                        '|
    Next intChar '---------------------------------------------------------------------------------------|
    
    
    'Checks the length of the sheet name after all the illegal characters have been removed. ------------|
    'If it is too long, the excessive characters are cut off.                                           '|
    Select Case Len(legalSheetName)                                                                     '|
        Case Is > 31                                                                                    '|
            legalSheetName = Left$(legalSheetName, 31)                                                  '|
        Case 0                                                                                          '|
            legalSheetName = "_"                                                                        '|
    End Select '-----------------------------------------------------------------------------------------|

End Function




'**********************************************************************************************************
' Name:                 isBookValid
' Author:               mielk | 2013-04-25
'
' Comment:              Function to check if the given Excel workbook is valid and you can refer to its
'                       properties and methods without errors.
'
'                       Using this function is very helpful since it lets you avoid
'                       Run-time error '-2147221080 (800401a8)': Automation error.
'                       This error is generated, when the code tries to refer to a property or a method
'                       of an Excel workbook that had been already closed.
'
' Parameters:
'   wkb                 The Excel workbook to be checked.
'
' Returns:
'   Boolean             True - if the given workbook is valid and you can refer to it without any errors.
'                       False - if the given workbook is invalid, that means it is corrupted or had been
'                               closed or deleted before this method has been called and referring to it
'                               will raise an error.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2013-04-25        mielk       Function created.
'**********************************************************************************************************
Public Function isBookValid(wkb As Excel.Workbook) As Boolean
    Const METHOD_NAME As String = "isBookValid"
    '------------------------------------------------------------------------------------------------------
    Dim strBookName As String
    '------------------------------------------------------------------------------------------------------
    
    On Error Resume Next
    strBookName = wkb.name
    
    'Check method is very easy - if the name of the given workbook has been assigned to the variable
    '[strBookName], this workbook is valid and you can refer to it. Otherwise, error would be generated
    'and step [strBookName = wkb.name] would be skipped because of [On Error Resume Next] statement above.
    isBookValid = VBA.Len(strBookName)
    
End Function