フォルダを指定して、別ブックのシートを取り込む

example(VBA)

Sub main()

    Dim sourcebook As Workbook
    Dim xlSheet As Worksheet
    Dim fso As Object
    Dim dirObj As Object
    Dim fileObj As Object
    Set fso = CreateObject("Scripting.FileSystemObject")


    Set dirObj = fso.GetFolder(GetDirName())
    For Each fileObj In dirObj.Files
        
        Set sourcebook = Workbooks.Open(fileObj.Path)
        For Each xlSheet In sourcebook.Worksheets
            xlSheet.Name = fileObj.Name & "_" & xlSheet.Name
            Call xlSheet.Copy(after:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count))
        Next
        Call sourcebook.Close(savechanges:=False)
        
    Next
End Sub

Private Function GetDirName() As String
  With Application.FileDialog(msoFileDialogFolderPicker)
    If .Show = True Then
      GetDirName = .SelectedItems(1)
    Else
      GetDirName = ""
    End If
  End With
End Function

この例ではフォルダを指定し、その中にあるエクセルファイルのすべてのシートを末尾に追加します。

サンプルなので、フォルダ内にワードのファイルなどが混ざっている場合は考慮されていないので、それを回避する処理が必要です。