以前パワークエリで複数のExcelブックのデータを集計する方法を記事にしたのですが、その際に少し触れた「マクロで同じような作業をするならどういうマクロになるのか」を改めて考えてみたので具体的なやり方を記事にしておきます。
以前の記事はこちら↓
dir関数というものを使っていくのですが、この関数の意味、使い方が理解できれば処理はできると思います。
と言ってみたものの、自分もぼんやりとしか理解していませんが。
目次
dir関数の使い方のポイント２つ
dir関数を使うとファイルがあるかどうかを調べることができる。
dir関数を使うとファイルがあるかどうかを調べることができます。
そして、もしファイルがあればその「ファイル名」を戻り値？として返します。
例えば下記のように、「元データ」フォルダと、「集計」というExcelブックがあるとします。
ちなみに「集計」というExcelブックはこんな感じです↓シート名を「転記先」としています。
で、さらに「元データ」フォルダの中には下記の２つのExcelブックを保存しています↓
この前提の状態で、「集計」というExcelブックに対して下記のマクロを作成して実行すると「元データ」フォルダにあるすべてのファイルの名前をメッセージボックスに表示します。
Sub filename_show()
Dim filename As String
Dim ws As Worksheet
Set ws = Workbooks("集計").Sheets("転記先")
filename = Dir(ThisWorkbook.Path & "\元データ\*")
'■エラー処理（Excelブックがない場合）
If filename = "" Then
MsgBox "ファイルがありません"
Exit Sub
End If
'■処理実行部分
MsgBox filename
'■他のExcelブックに関しても同様の処理を行う。
Do
'変数filenameに別のExcelブックを取得させる。
filename = Dir()
If filename = "" Then
Exit Do
Else
MsgBox filename
End If
Loop
End Sub
実行するとこんな感じでメッセージボックスが表示されます。
「元データ」フォルダに保存したExcelブックのファイル名と一致していることが確認できます↓
OKボタンをクリックすると次のファイル名が表示されます↓
ざっくりですが、これがdir関数の使い方になります。
dir()とすることで直前に取得したファイルとは「別の」ファイル名を取得することができる。
dir関数には少し特徴的な使い方があります。
それはdir()として括弧のなかを空欄にした状態で変数を定義すると直前に指定したフォルダ内にある別のファイル名を取得して戻り値？として返すということです。
29行目のハイライト行の部分でdir()としているのはそのためです。
8行目の部分で変数filenameには「出納帳１.xlsx」が格納されていますが、29行目で再度、変数filenameを上のやり方でdir()として定義しなおすことで変数filenameに新しく「出納帳2.xlsx」が格納されることになります。
具体的にはこの部分で 変数filenameには「出納帳１.xlsx」が格納されて ↓
この部分で 変数filenameに「 出納帳2.xlsx 」が格納されていることになります↓
ここまでのdir関数の使い方がわかればあとはさほど難しくありません。
※注意点※OneDrive上にファイルを作るとdir関数がエラーになるのでローカルにフォルダ作る
OneDrive上に今回のフォルダを作成すると、マクロを実行した際にdir関数がエラーになってしまうので、今回のマクロを実行する場合には「元データ」フォルダや「集計」というExcelブックに関してはクラウド上ではなくローカルに作成するようにしましょう。
なぜエラーになるのかよくわかっていないのですが、ローカルに保存するとうまくいきます。
おそらくOneDrive以外のクラウドサービスに関しても同様にエラーになると思われます。
特定のフォルダに保存されたExcelブックをすべて開くマクロ dir関数を使用
次のマクロは「元データ」というフォルダに保存されたExcelブックをすべて開くというマクロになります。
実務上というか実際には税理士業務においてはExcelを開くだけで終わる仕事はなく、開いてそこから開いたExcelブックに対して何かしらの処理をするということになろうかと思います。
例えば特定のセルの数値をコピーしてそれを集計用のExcelブックへ転記するといったように様々な処理が考えられます。
なので下記のマクロは雛形・たたき台のような感じで参考にしていただければと思います↓
Option Explicit
Sub data_set()
'■変数宣言
Dim filename As String
Dim ws As Worksheet
Set ws = Workbooks("集計").Sheets("転記先")
filename = Dir(ThisWorkbook.Path & "\元データ\*")
'■エラー処理（Excelブックがない場合）
If filename = "" Then
MsgBox "ファイルがありません"
Exit Sub
End If
'■処理実行部分
Workbooks.Open filename:=ThisWorkbook.Path & "\元データ\" & filename
'ここから追加で処理するマクロを記載する↓
'ここまでマクロを記載する↑
'■他のExcelブックに関しても同様の処理を行う。
Do
'変数filenameに別のExcelブックを取得させる。
filename = Dir()
If filename = "" Then
Exit Do
Else
Workbooks.Open filename:=ThisWorkbook.Path & "\元データ\" & filename
'ここから追加で処理するマクロを記載する↓
'ここまでマクロを記載する↑
End If
Loop
MsgBox "全てのファイルを開きました"
ws.Activate
End Sub
特定のフォルダに保存された複数のExcelブックのデータを集計するマクロ
前置きがだいぶ長くなってしまいました。
上のブロックで記載したマクロのたたき台を書き換えることで以前記事にしたパワークエリでデータを引っこ抜く処理と同様の処理が可能になります。
いろんなやり方があると思いますが自分の場合はこんな感じでマクロを書き換えました↓
Sub data_set()
'■特定のフォルダにあるExcelファイルを参照して特定の作業をするマクロ
'■「元データ」フォルダ内にあるExcelファイルをすべて開いて集計ブックに転記する
'■変数宣言
Dim filename As String
Dim ws As Worksheet
Dim target As Range
Dim lastrow As Long
filename = Dir(ThisWorkbook.Path & "\元データ\*")
Set ws = Workbooks("集計").Sheets("転記先")
lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
'■エラー処理 dir関数はファイルが存在しない場合に””（空白）を返す
If filename = "" Then
MsgBox "ファイルがありません"
Exit Sub
End If
'■処理実行部分
Workbooks.Open filename:=ThisWorkbook.Path & "\元データ\" & filename
Set target = ActiveWorkbook.ActiveSheet.Cells.Find(what:="日付")
If target Is Nothing Then
MsgBox "日付というセルはありません"
Else
target.CurrentRegion.Offset(1, 0).Copy ws.Range("a" & lastrow)
ActiveWorkbook.Close savechanges:=False
End If
'■処理実行部分の繰りかえし処理
Do
'dir関数の（）内に空白（ブランク）を指定して別のファイルを取得させる
filename = Dir()
If filename = "" Then
MsgBox "すべてのファイルを開きました"
ws.Activate
Exit Do
End If
Workbooks.Open filename:=ThisWorkbook.Path & "\元データ\" & filename
lastrow = ws.Cells(Rows.Count, 1).End(xlUp).Row + 1
Set target = ActiveWorkbook.ActiveSheet.Cells.Find(what:="日付")
If target Is Nothing Then
MsgBox "日付というセルはありません"
Else
target.CurrentRegion.Offset(1, 0).Copy ws.Range("a" & lastrow)
ActiveWorkbook.Close savechanges:=False
End If
Loop
ws.Activate
End Sub
このマクロを実行すると実行結果はパワークエリで行った時と同じ結果になります。
マクロ実行するとこんな感じの結果になります↓
dir関数を使ってフォルダに保存したExcelブックからデータを引っこ抜くときの難点２つ。
マクロの処理実行時間が長くなりがち。
処理するExcelのファイル数にもよりますが、マクロの実行時間が長くなることが多いです。
これは「Excelファイルを開く」という処理が他の作業と比べて処理に時間がかかるためと思われます。
ファイルを開かずにデータのみを取得する方法もあるようなのですが、自分のスキルではできません。
なので、少しでも処理を早くするためには「Application.ScreenUpdating プロパティ」で画面の更新をストップさせるなどのマクロを追加するなどして対応する必要があります。
これをやったからといって処理速度が劇的に早くなるわけではありませんが、気持ち、いくぶんか早くなりますし、やらないよりはマシかなと。
個人的な感覚にはなりますが、処理するExcelブックの数が10を超える場合にはこういった手当を検討するほうがいいと思います。
どのファイルを取得するか順番を指定することができない。（多分）
dir()として括弧のなかを空白にして再度ファイルを取得させる際にはどのExcelファイルを取得するかをこちらで選ぶことができません。
ランダムで取得されます。
なので指定した順番にExcelファイルを開いてデータを転記したりすることができません。
配列をつかうとデータの並べ替えができるようなのですがどうやればできるのか自分にはわかりません。
まとめ
税理士業務におけるdir関数の具体的な使いどころを記事にしました。
便利な関数なので業務に落とし込んで使ってもらえればと思います。
ちなみにdir関数を知ったのはこちらの書籍のおかげです↓
【本日の近況報告】
ZOOMのリモート操作で顧問先で使っているソフトのトラブル解決。
無事に解決できて安心しました。
遠隔でこういった作業ができるようになったのも新型コロナウイルスがきっかけだったりするので少し複雑な気持ちになりますが。。。
【本日の１曲】
new navy/zimbabwe
あ～、久しぶりにあの曲聴きたいな～、バンド名と曲名何だったっけなぁ～・・・・
となって思い出すまでにしばらくかかったバンド。new navyのジンバブエという１曲。
次に聴きたくなって思い出せなくなると困るのでここに書いておくことにします。2011年リリースのようです。
テレキャスのクリーンなギターの音色。一度聴くと耳に残るリフ。2分過ぎたあたりからのドラムブレイク。カッコいいです。
この曲聴くと三味線で有名な吉田兄弟とモンキーマジックが一緒に演った「change」という曲を思い出してしまいます。こちらも良曲。
文字にするとわかりにくいですね。。。。