フォルダ内のエクセルファイルを一つのシートに統合する

Visual Basic for Applications
Sub Macro()
  Dim ObjNetWork As Object
  Dim GetUserName As String
  Dim FilePath As String
  Dim Sheet As Integer
  Dim FolderNumber As Integer
  Dim StartFolderNumber As Integer
  Dim EndFolderNumber As Integer
  Dim FileName(100) '固定長配列の場合。インデックス番号は目的に合わせる。
  Dim File
  Dim OpenFile As String
  Dim nnn As Integer
  Dim iii As Integer
  Dim rrr As Integer
  Dim ccc As Integer
  Dim EndRow As Integer
  Dim EndCol As Integer
  Set ObjNetWork = CreateObject("WScript.Network")
    GetUserName = ObjNetWork.UserName
  Set ObjNetWork = Nothing
  FilePath = "C:\Users\" & GetUserName & "\Desktop\ExampleFolder" '一例。
  Sheet = 1 '一例。インポートするファイルのシートインデックスが全て1の場合。
  StartFolderNumber = 1
  EndFolderNumber = 2
  rrr = 1
  ccc = 1
  ThisWorkbook.Activate
  Worksheets(Sheet).Activate
  Cells.Select
  Selection.ClearContents
  Erase FileName
  Application.DisplayAlerts = False
    For nnn = StartFolderNumber To EndFolderNumber
      iii = 1
      Erase FileName
      File = Dir(FilePath & nnn & "\*.*", vbNormal)
      Do While File <> ""
        FileName(iii) = File
        OpenFile = FilePath & nnn & "\" & FileName(iii)
        Workbooks.Open OpenFile
        EndRow = Worksheets(Sheet).Cells(1, 1).End(xlDown).Row
        EndCol = Worksheets(Sheet).Cells(1, 1).End(xlToRight).Column
        Range(Cells(1, 1), Cells(EndRow, EndCol)).Select
        Selection.Copy
        ThisWorkbook.Activate
        Worksheets(Sheet).Cells(rrr, 1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Workbooks(FileName(iii)).Close
        rrr = rrr + EndRow
        File = Dir()
        iii = iii + 1
        DoEvents
      Loop
    Next nnn
  Application.DisplayAlerts = True
  ThisWorkbook.Save
  ActiveWindow.Close
End Sub