案件

研究報告書の特定のセルに研究データ保存
セル内容：１月の検体数　○個
ファイル名　１月の検体数.xlsx　
表題のように一括でデータ取得し
合計値を算出

##　コード(とりあえず動く形)

Sub main()
'ディレクトリ内読み込み
Dim dstSheet As Worksheet
Set dstSheet = ThisWorkbook.Worksheets(1)
Const Path As String = "D:\testReadVba"
Dim buf As String
buf = Dir(Path & "*.xlsx")
Dim i As Long
Do While buf <> ""
i = i + 1
Dim srcBook As Workbook
Set srcBook = Workbooks.Open(Path + buf)
Dim srcSheet As Worksheet
Set srcSheet = srcBook.Worksheets(1)
dstSheet.Cells(i, 1).Value = buf
dstSheet.Cells(i, 2).Value = srcSheet.Cells(1, 1)
srcBook.Close False
buf = Dir()
Loop
'合計値出力
Dim r As Long
r = Range("B1").End(xlDown).Row + 1
Range("A" & r) = "合計"
With Range("B" & r)
.Formula = "=SUM(B1:B" & (r - 1) & " )"
.AutoFill Destination:=.Resize(1, 2)
End With
End Sub