Liest alle Sheetnamen einer Exceldatei aus

' @param    iFilePath   Pfad der Exceldatei
' @return               Array mit den Sheetnamen der Exceldatei
Public Function readExcelSheetNames(ByVal iFilePath As String) As String()
On Error GoTo Err_Handler
 
	Dim myExcel As New excel.Application
	Dim myWorkbook As workbook
	Dim names() As String
	Dim i
 
	'Excel verstecken
	myExcel.Visible = False
 
	Set myWorkbook = myExcel.Workbooks.Open(iFilePath)
	ReDim names(myWorkbook.Sheets.Count - 1)
 
	For i = 0 To myWorkbook.Sheets.Count - 1
		names(i) = myWorkbook.Sheets(i + 1).name
	Next i
 
	readExcelSheetNames = names
 
Exit_Handler:
On Error Resume Next
	'Alle Objekte schliessen zerstören
	Call myWorkbook.Close
	Set myWorkbook = Nothing
	Call myExcel.Quit
	Set myExcel = Nothing
 
	Exit Function
 
Err_Handler:
	MsgBox Err.Description
	GoSub Exit_Handler
End Function

Ein Anwendungsbeispiel

Public Sub testReadExcelSheetNames()
	Dim names() As String
	Dim i
 
	names = readExcelSheetNames("C:\temp\test.xls")
 
	For i = 0 To UBound(names)
		Debug.Print names(i)
	Next i
End Sub