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
errHandle:
If Err.Number <> 0 Then
Err.Clear
End If
Set getSheetNamesFromWorkbook = sheetNames
End Function