Actually you can list all worksheet names in a workbook using the object path Workbook.Worksheets(index).Name.

I needed to have the names as strings in a VBA.Collection, so I developed a little function collecting the names.

' @Author - Alexander Bolte
' @Change Date - 26.12.2013
' @Description - Creates a list of all sheet names in given workbook.
' @Param wrk - Excel workbook the sheet name collection should be created from.
' @Returns - VBA.Collection holding all sheet names from given Microsoft Excel workbook.
Public Function getSheetNamesFromWorkbook(ByRef wrk As Workbook) As VBA.Collection
Dim i As Integer
Dim sheetNames As New VBA.Collection

On Error GoTo errHandle:

For i = 1 To wrk.Worksheets.Count
sheetNames.Add wrk.Worksheets(i).Name
Next i

If Err.Number <> 0 Then
End If

Set getSheetNamesFromWorkbook = sheetNames
End Function