今回作成したマクロは大量のファイルを台帳で管理するマクロです。
今回の想定は、
- 同じようなファイル名のファイルがたくさんあり、探すのに時間がかかる。
- ファイルを開いてみないと目的のファイルかどうか判別しづらい。
- ファイル検索をするとたくさんのファイルが出てきてしまって結局探すのに時間がかかる。
というような内容を解決するマクロです。
その都度台帳に記載して、保存してを繰り返せば上記のような問題は起こりませんが、台帳が必要だと気付いた時には１００ファイルぐらいあったりしますよね笑
そのファイルを台帳に転記するの作業を考えると心が折れそうになります汗
それをマクロを作成して自動化しようという作戦です。
想定する台帳は下記の画像ようなものです。
ファイル整理前は下記のような状態です。（絶望的すぎる笑）
ファイル整理後は下記のような状態になります。（超すっきり）
番号管理によって中身がどうなっているのかがわからなくなっていますが、台帳に必要な情報を乗せていますので、台帳を見ればファイルを開かずに確認することができます。また、ハイパーリンクも設定していますので、瞬時にファイルを開くことも可能です。
では作成の過程を説明していきます。
完成形とテストフォルダ
作成の過程の前に完成形のフォルダを下記の添付しておきますので、実際にどのような動きになるのか気になる方は実行してみてください。
説明と使い方
まずは「未処理フォルダ」を開いてみてください。
絶望的なファイルがたくさんあります。
次に「見積書」フォルダを確認してください。現在は何も入っていません。
ここにマクロを実行するとファイルが移動されて、ファイル名が変更されます。
使い方は見積書台帳を開いて、ボタンをクリックします。
・・・以上で完了です。
ボタンを押した後に見積書フォルダと未処理フォルダを確認してみてください。
今回作成したコードのすべて
下記のコードが作成したコードです。
それぞれに簡単な説明を付けていますので、読める方はこのまま自分の使用しやすいように変更してもらったら大丈夫です。
Sub 台帳作成()
'初期設定
'変数作成
Dim 台帳 As Workbook
Dim データ(5) As String 'ファイル内データ
Dim 見積書 As Workbook '作業するブック
Dim 見積書パス As String '見積書フォルダのパス
Dim 作業パス As String '作業するブックのパス
Dim 未処理フォルダパス As String
'変数設定
Set 台帳 = ThisWorkbook
未処理フォルダパス = 台帳.Path & "\未処理フォルダ\"
見積書パス = 台帳.Path & "\見積書\"
'ワイルドカードを使って見積書パスの取得
ワイルドカード = Dir(未処理フォルダパス & "*")
Do While ワイルドカード <> ""
作業パス = 未処理フォルダパス & ワイルドカード
'ファイルを開く
Workbooks.Open 作業パス
Set 見積書 = Workbooks(ワイルドカード)
'必要なデータを取得
With 見積書.Sheets(1)
データ(0) = 台帳.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row 'No
データ(1) = .Cells(3, "A").Value '見積先
データ(2) = .Cells(6, "C").Value '件名
データ(3) = .Cells(15, "D").Value '合計金額
データ(4) = .Cells(13, "M") '担当者
If 台帳.Sheets(1).Cells(2, "A").Value = "" Then
データ(0) = 1
End If
End With
'ファイルを閉じる
見積書.Close
'台帳に記入
With 台帳.Sheets(1)
data_range = "A" & データ(0) + 1 & ":E" & データ(0) + 1
.Range(data_range) = データ
'Noにハイパーリンクを設定する。
.Hyperlinks.Add _
Anchor:=.Cells(データ(0) + 1, 1), _
Address:=見積書パス & "見積書" & データ(0) & ".xlsx"
End With
'ファイル名を変更してフォルダ移動
Name 作業パス As 見積書パス & "見積書" & データ(0) & ".xlsx"
ワイルドカード = Dir()
Loop
End Sub
どうでしょうか？思っていたよりもコードが少ないなと思っていただければ嬉しいです。
思ったよりもコードが多いと思われた方は改善ポイントを教えていただけると自分の勉強になります笑
次はコードの流れを説明していきます。
コードの流れ
コードは以下のような流れになっています。
初期設定
'変数作成
'変数設定
'繰り返し
'ファイルを開く
'必要なデータを取得
'ファイルを閉じる
'台帳に記入
'Noにハイパーリンクを設定する。
'繰り返し終わり
こんな感じになっています。
それほど難しいとは思わないので、初心者の方も参考にしていただければ修正して自分の環境にあったマクロに修正できると思います。
変数の作成について
今回は以下の変数を作成しました。
Dim 台帳 As Workbook
Dim データ(5) As String 'ファイル内データ
Dim 見積書 As Workbook '作業するブック
Dim 見積書パス As String '見積書フォルダのパス
Dim 作業パス As String '作業するブックのパス
Dim 未処理フォルダパス As String変数もかっこよく英語で記述したかったんですが、日本語の変数の方がわかりやすいかな？と思って日本語で作成しました。
ここで押さえておきたいのは、作業するワークブックは変数にしておき、記述量を減らせるようにする。です。
また、各ファイルパスは文字列として保存しておくと使い勝手がよくなりますね。
あとは、台帳に入れる値を配列として持っておくことによってデータを貼り付ける際に記述量が少なくて済みます。
なんでも変数に保存しておけばいいというわけではないと思うので、下記に変数を設定した方がいいかどうかを判断する基準を示しておきます。
変数を設定した方がいいパターン
- 何度も使いことが想定される場合
- 記述量が多くなり、タイプミスが発生してしまいそうな場合
- 変数を使用せずに記述することでコードが読みづらくなりそうな場合
- 上記の項目とかぶるかもしれませんが、変数を使用することでコードが読みやすくなる場合
- 今後状況に応じて変更するかもしれない場合
です。今回であれば、台帳に転記していく内容（見積期限とか見積日等々）、データを保存するフォルダパス（見積書ではなく、請求書のファイルを台帳管理する場合）等です。
変数を使用することにより、１箇所変更するだけで同じ記述を全部変更することができるのは非常に便利ですよね。
変数設定について
変数設定については下記の部分になります。
Set 台帳 = ThisWorkbook
未処理フォルダパス = 台帳.Path & "\未処理フォルダ\"
見積書パス = 台帳.Path & "\見積書\"一つずつ説明していきます。
台帳：この変数にはthisworkbookを使って台帳のbookを入れておきました。
これは、with文と一緒に使いますが、「ここから台帳の処理です～」とわかりやすくするために設定しています。正直thisworkbookを使えば記述ミスもなく、読みづらいわけではないですが、thisworkbookを使用するよりはわかりやすいですよね。
未処理フォルダパス：これは使いまわすので設定しておきました。注意点は、台帳のデータがあるフォルダの階層に未処理フォルダを作成しておかないと今後エラーが発生します。理由は、「台帳.Path」が台帳ファイルがあるフォルダの階層を示しているからです。これをデスクトップとかにあるフォルダを指定したい場合はフルパスを指定しなければなりません。
見積書パス：これは台帳に転記したデータを番号管理するためにデータをまとめて保存しておくフォルダパスを保存しています。こちらも未処理フォルダと同じ注意点です。
僕は基本変数を宣言した後に変数を設定するようにしていますが、使用する前に変数を宣言した方が読みやすいのかな？とも思うので、個人の設定したいタイミングで設定してください。
今回のメイン処理について
メイン処理は下記の部分です。
'ワイルドカードを使って見積書パスの取得
ワイルドカード = Dir(未処理フォルダパス & "*")
Do While ワイルドカード <> ""
作業パス = 未処理フォルダパス & ワイルドカード
'ファイルを開く
Workbooks.Open 作業パス
Set 見積書 = Workbooks(ワイルドカード)
'必要なデータを取得
With 見積書.Sheets(1)
データ(0) = 台帳.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row 'No
データ(1) = .Cells(3, "A").Value '見積先
データ(2) = .Cells(6, "C").Value '件名
データ(3) = .Cells(15, "D").Value '合計金額
データ(4) = .Cells(13, "M") '担当者
If 台帳.Sheets(1).Cells(2, "A").Value = "" Then
データ(0) = 1
End If
End With
'ファイルを閉じる
見積書.Close
'台帳に記入
With 台帳.Sheets(1)
data_range = "A" & データ(0) + 1 & ":E" & データ(0) + 1
.Range(data_range) = データ
'Noにハイパーリンクを設定する。
.Hyperlinks.Add _
Anchor:=.Cells(データ(0) + 1, 1), _
Address:=見積書パス & "見積書" & データ(0) & ".xlsx"
End With
'ファイル名を変更してフォルダ移動
Name 作業パス As 見積書パス & "見積書" & データ(0) & ".xlsx"
ワイルドカード = Dir()
Loopどう分けようか悩んだので、小分けに説明していきます。
ワイルドカード「*」とDir関数について
ワイルドカードをdir関数と一緒に使うことによってフォルダパス内のデータを取得してくれます。
ですので、未処理フォルダのデータを取得してくれます。
また、dir()として再代入すると、次のファイルに移動してくれます。
ワイルドカード = Dir(未処理フォルダパス & "*")←未処理フォルダパスの一番目のファイルを指定
Do While ワイルドカード <> "" ←変数「ワイルドカード」が空白になるまで繰り返し
・
・
・
ワイルドカード = Dir() ←次のファイルを変数に代入している。
Loop変数：ワイルドカードに入っているファイル名を使ってファイルを開く
作業パス = 未処理フォルダパス & ワイルドカード
'ファイルを開く
Workbooks.Open 作業パス
Set 見積書 = Workbooks(ワイルドカード)現在 変数「ワイルドカード」に入っているのはファイル名です。
これを未処理フォルダパスとくっつけることにより、ワイルドカードに入っているファイル名のファイルパスを取得することができ、これを変数「作業パス」に保存します。
この作業パスを使ってファイルを開きます。
また、開いたブックを変数「見積書」に設定しておきます。
データの取得した後ファイルを閉じる。
With 見積書.Sheets(1)
データ(0) = 台帳.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row 'No
データ(1) = .Cells(3, "A").Value '見積先
データ(2) = .Cells(6, "C").Value '件名
データ(3) = .Cells(15, "D").Value '合計金額
データ(4) = .Cells(13, "M") '担当者
End With
'ファイルを閉じる
見積書.Closeこれはwith文を使い、その中で配列に必要な情報を代入しているだけです。
そのあと、ファイルを閉じています。
ファイルを閉じる理由は、この後、ファイルを移動したりファイル名を変更するためです。
開いていると上記の作業をすることができません。
エラー処理を書けば可能ですが、結局ファイルを閉じることになります。
台帳にデータを代入する。
'台帳に記入
With 台帳.Sheets(1)
data_range = "A" & データ(0) + 1 & ":E" & データ(0) + 1
.Range(data_range) = データ
'Noにハイパーリンクを設定する。
.Hyperlinks.Add _
Anchor:=.Cells(データ(0) + 1, 1), _
Address:=見積書パス & "見積書" & データ(0) & ".xlsx"
End Withwith文を使用して台帳にデータを一括で打ち込むコードです。
一括代入する方法は配列の長さの分だけRangeを指定して、代入するだけです。
また、「No」にハイパーリンクを設定しています。
ハイパーリンクの名前付き引数について
ここに紹介する引数以外にもありますが、必須項目だけ紹介します。
Anchor:ハイパーリンクを設定するセルの位置を指定します。
Address:ハイパーリンク先のアドレス（ファイルパスやURL）を指定します。
ファイル名を変更して、ファイル移動もやっちゃう
Name 作業パス As 見積書パス & "見積書" & データ(0) & ".xlsx"この一行でできます。
Nameステートメントはファイルの場所を変更することができるメソッドです。
このファイルの場所に変更したいファイル名で移動することにより、保存されるファイル名および保存したい場所を一括で保存することができます。
まとめ
案外難しいことはしていないので、初心者の方でも作成することができると思います。
非常に長くなってしまいました。ここまで読んで頂きありがとうございました。
もし、自分の環境に合わせて作ってほしい等希望の方がいらっしゃいましたら、Twitterのメッセージにて連絡してください。
コメント