LoginSignup
0

More than 3 years have passed since last update.

VBA ディレクトリ内のデータ一括読み込み→セル出力 集計値作成

Last updated at Posted at 2019-06-02

案件

研究報告書の特定のセルに研究データ保存
セル内容:1月の検体数 ○個
ファイル名 1月の検体数.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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0