連載
» 2018年07月23日 05時00分 公開

働く価値を上げる“VBA/GAS”術(9):メンテナンス性や実行時間を改善――複数のExcelデータを1箇所に集約するマクロの別解とは (2/3)

[高橋宣成,プランノーツ]

テーブルを使った経費データ収集マクロ

テーブルへの変換

 Excelで作成した表は、「テーブル」に変換できます。それにより、並べ替えやオートフィルター、合計値などの集計、データの追加、スタイル選択による書式設定を簡単に行えます。

 経費精算書フォーマットは、既にテーブルに変換されているので、経費収集.xlsmの経費収集シートを例としてテーブルに変換します。

 まず、テーブル化したいセル範囲のいずれかにカーソルを置いた状態で、リボンの「挿入」から「テーブル」を選択します。

図3 リボンからテーブルを選択する

 「テーブルの作成」ダイアログが開きます。テーブル化する対象範囲はExcelが自動で判別して指定するので、そのまま「OK」をクリックします。

図4 テーブルの作成ダイアログ

 すると、以下のように対象範囲がテーブル化されます。

図5 テーブル化された表

 テーブル化はVBAに限らず、通常のExcel作業でもメリットがありますので、活用してみてください。

 さて、これから新たにデータを追加しますので、2行目以降のデータは一回削除します。これでExcel側の準備は完了しました。次はマクロを修正します。

テーブルを使った経費データ収集マクロ

 経費データ収集マクロを、テーブルを活用したものに修正しました。

Enum e
  日付 = 1
  科目
  訪問先名
  支払先名
  内容
  金額
  備考
End Enum
 
Sub 経費データ収集()
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim pass As String
pass = ThisWorkbook.Path & "\経費精算書"
Dim month As Date, department As String, fullname As String '対象月,部署,氏名
 
Dim rowsSheet1 As ListRows
Set rowsSheet1 = Sheet1.ListObjects(1).ListRows
 
Dim f As file
For Each f In fso.GetFolder(pass).Files
  With Workbooks.Open(f)
    With .Worksheets(1)
      month = .Range("G1").Value   '対象月
      department = .Range("G3").Value '部署
      fullname = .Range("G4").Value  '氏名
      
      With .ListObjects(1)
        Dim row As ListRow
        For Each row In .ListRows
 
          Dim rowSheet1 As ListRow
          Set rowSheet1 = rowsSheet1.Add
 
          Dim rng As Range
          Set rng = row.Range
          rowSheet1.Range = Array( _
            month, _
            rng(e.日付), _
            department, _
            fullname, _
            rng(e.科目), _
            rng(e.内容), _
            rng(e.金額), _
            rng(e.備考) _
          )
        Next row
      End With
    End With
    .Close
  End With
Next f
Set fso = Nothing
 
End Sub

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。