Here is the sample code that will allow you to process each file. Please read the comments in the code for details.
'Allows you to execute code when the workbook first opens.
Private Sub Workbook_Open()
'Subroutine that actually processes the files. This subroutine makes a list of each spreadsheet's filename along with all of its worksheets. A conditional statement is also included to display how copying and pasting of cell range can be achieved.
Public Sub GetFiles()
'Create all variables used in this subroutine
Dim directory As String, fileName As String, currentWorkbook As Workbook, sheet As Worksheet, i As Integer, j As Integer
'Stop screen updating, events, and alerts until this subroutine has finished execution.
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False
'Pull the directory and file extension data from the "Config Data" worksheet.
directory = Sheets("Config Data").Cells(1, 2)
fileExt = Sheets("Config Data").Cells(2, 2)
'Find the first file in the specified directory with the specified file extension.
' If the Dir function finds multiple files matching the file extention, the function can be called again without any arguments to return the next file.
fileName = Dir(directory & "*." & fileExt)
' Start a loop control structure to process each file.
Do While fileName <> ""
i = i + 1
j = 2
' Store filename of each spreadsheet in its own row
Sheets("Sheet1").Cells(i, 1) = fileName
Set currentWorkbook = Workbooks.Open(fileName:=directory & fileName)
If fileName = "StockChart.ods" Then
'Copying a range from the current workbook that is being processed
'Pasting a range to the current workbook that is running the macro
' For each worksheet in the current workbook, the worksheet name is being stored in the column next to its workbook filename.
For Each sheet In currentWorkbook.Worksheets
Sheets("Sheet1").Cells(i, j) = sheet.Name
j = j + 1
' Close current workbook
' Check if more files exist that match the file extension provided.
fileName = Dir()
'Enable screen updating, events, and alerts
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.DisplayAlerts = True