Option Explicit
Public Sub prcReadInData()
Const SHEET_NAME As String = "Auswertung"
Const NUMBEROF_DATA As Long = 59
Dim objFso As Object
Dim objFolder As Object
Dim objFile As Object
Dim avntArray As Variant
Dim ialngIndex As Long, ialngCount As Long
Dim strPath As String
strPath = "C:\Users\MyUser\Documents\Excel\testOrdner"
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFso.GetFolder(strPath)
With objFolder
ReDim avntArray(.Files.Count - 1, NUMBEROF_DATA - 1) As Variant
For Each objFile In .Files
ialngCount = ialngCount + 1
For ialngIndex = 1 To UBound(avntArray, 2) + 1
avntArray(ialngCount - 1, ialngIndex - 1) = ExecuteExcel4Macro( _
"'" & strPath & "\[" & objFile.Name & "]" & SHEET_NAME & "'!" & _
Cells(3, 1 + ialngIndex).Address(ReferenceStyle:=xlR1C1))
Next
Next
End With
ActiveSheet.Cells(3, 2).Resize(UBound(avntArray, 1) + 1, UBound(avntArray, 2) + 1) = avntArray
Set objFso = Nothing
Set objFolder = Nothing
Set objFile = Nothing
End Sub