Getting all worksheet names from an Excel File

In one of my projects I had to read all worksheet names from several Excel files.

Here is the code for getting all worksheet names from a closed or already opened Excel file.

' @Author - Alexander Bolte
' @Change Date - 26.12.2013
' @Description - Creates a list of all sheet names in workbook available under given file path.
' @Param filePath - Path to Microsoft Excel file the sheet name collection should be created from.
' @Returns - VBA.Collection holding all sheet names from given Microsoft Excel file.
Public Function getSheetNames(ByVal filePath As String) As VBA.Collection
    Dim wrk As Workbook
    Dim sheetNames As New VBA.Collection
    
    On Error GoTo errHandle:
    
    Set wrk = getOpenedWorkbook(filePath)
    If wrk Is Nothing Then
        Set wrk = Application.Workbooks.Open(filePath)
    End If
    ' Get the sheet names for one file.
    Set sheetNames = getSheetNamesFromWorkbook(wrk)
    ' Close workbook without saving.
    wrk.Close False
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
    End If
    Set wrk = Nothing
    
    Set getSheetNames = sheetNames
End Function

Referenced Functions can be found below.

getSheetNamesFromWorkbook(wrk)
Getting all sheet names from a workbook

getOpenedWorkbook(filePath)
Getting an opened Excel workbook

: Written by Alexander Bolte; Last Updated: 25 July 2015; Hits: 3918

Bolte IT Consulting

Getting all worksheet names from an Excel File