Trying to import data from a range of closed excel files

Hi

I have been trying to get this code to import data from a range of closed excel files in a folder and can't get it to work. I have modified codes form the web to pull in the data from one file - see below. But can't get it to loop through to grab it from a range of files. The data is in the same place in each file. There are approximately 50 files. The data is in a table that is 4 columns wide and 5 rows down, I need all the data to display in row 1 for the first set of data, then row 2 for the second and so on.

Can you help???

Option Explicit


Sub GetDataDemo()

      Dim FilePath$, Row&, Column&, Address$
      
      'change constants & FilePath below to suit
      '***************************************
      Const FileName$ = "V5 iReport.xls"
      Const SheetName$ = "iTarget"
      Const NumRows& = 1
      Const NumColumns& = 4
      FilePath = ActiveWorkbook.Path & "\"
      '***************************************
      
      DoEvents
      Application.ScreenUpdating = False
      If Dir(FilePath & FileName) = Empty Then
            MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist"
            Exit Sub
      End If
      Row = 1
            For Column = 1 To NumColumns
                  Address = Cells(Row, Column).Address
                  Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address)
                  Columns.AutoFit
            Next Column
       Row = 2
            For Column = 1 To NumColumns
                  Address = Cells(Row, Column).Address
                  Cells(Row - 1, Column + 4) = GetData(FilePath, FileName, SheetName, Address)
                  Columns.AutoFit
            Next Column
         Row = 3
            For Column = 1 To NumColumns
                  Address = Cells(Row, Column).Address
                  Cells(Row - 2, Column + 8) = GetData(FilePath, FileName, SheetName, Address)
                  Columns.AutoFit
            Next Column
         Row = 4
            For Column = 1 To NumColumns
                  Address = Cells(Row, Column).Address
                  Cells(Row - 3, Column + 12) = GetData(FilePath, FileName, SheetName, Address)
                  Columns.AutoFit
            Next Column
         Row = 5
            For Column = 1 To NumColumns
                  Address = Cells(Row, Column).Address
                  Cells(Row - 4, Column + 16) = GetData(FilePath, FileName, SheetName, Address)
                  Columns.AutoFit
            Next Column
      ActiveWindow.DisplayZeros = False
End Sub

Private Function GetData(Path, File, Sheet, Address)
      Dim Data$
      Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
            Range(Address).Range("A1").Address(, , xlR1C1)
      GetData = ExecuteExcel4Macro(Data)
End Function