You will have to build code to iterate all of your excel files. The following (untested) code should grab a hardcoded path, open a workbook, and display the worksheet name in a msgbox.
The following code uses early binding. You will need to reference the Microsoft Excel XX.0 Object Library.
Code:
Dim xlApp As New Excel.Application
Dim myBook As Workbook
Dim mySheet As Worksheet
Set myBook = xlApp.Workbooks.Open(C:\Test\ExcelFiles\TestFile.xlsx)
Set mySheet = myBook.Worksheets(1)
Dim strSheetName as String
strSheetName = mySheet.Name
msgbox strSheetName
set mySheet = nothing
myBook.close
set myBook = nothing
set xlApp = nothing