【エクセルVBA入門】バラバラの経費精算書をデータにまとめる

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

エクセルVBAを事務作業に活用する場合、代表的な二つパターンがあります。

1つ目はデータベースからルールに則って特定のデータを抽出するという使い方で、このパターンは以下の記事から始まるデータ一覧から請求書を作成するシリーズで紹介しました。

【初心者向けエクセルVBA】データ一覧から請求書を自動で作る

初心者向けVBAの実用的な使い方について、請求データ一覧から請求書を自動で作成するを目標に進めます。初回はWorksheetオブジェクト、Rangeオブジェクトと、セルの値の操作についてお伝えします。

tonari-it.com

2つ目のパターンは、いわばその逆のパターンなのですがバラバラのデータを一つのデータベースに集約・蓄積するというものです。

この２つ目のパターンについて経費精算書をテーマに何回かに分けて解説をしていきたいと思います。

イメージとしては、ファイルの中に各人から集めた経費精算書を放り込んで、VBAを走らせたら一つのデータベースに自動でその情報が取り込める、というイメージです。

実際のお仕事でも活用できますでしょ？

とその前に、VBAが全くの初心者という方は以下２つの記事をさらっていただけると良いです。

エクセルVBA超入門！たった10分でスタート地点に立つための方法

エクセルVBA…まず何をしたら良いかわからない…！という声をよく耳にします。今回はVBAプログラミングをいざ書き始める直前の状態を目標に、エクセルVBAの最低限のセッティングの仕方についてお伝えします。

tonari-it.com

429 Too Many Requests

tonari-it.com

では、張り切っていきましょう！

経費精算書のひな形と経費データシートを用意する

まず、各担当者にひな形として使っていただく経費精算書のひな形はこちらのものを使っていただくものとします。

一般的な作りですね。例として経費太郎さんの7月分ですね。

会社によっては、交通費精算は別のひな形を使われている場合があるかも知れませんが、今回は交通費も他の経費と同じひな形で、一緒くたに処理しちゃいます。

これと同じひな形を使って、みなさんが経費精算書を作成するものとします。

そして、これらのデータを蓄積する先のフォーマットはこちらです。

こちらのシートに太郎さんのデータをはじめ、どんどん蓄積していくようなVBAを目標にしていきます。

ちなみにこちらのシートにはオブジェクト名を「wsData」と変更しているものとします。

シートのオブジェクト名について、その変更の仕方については、以下の記事をご覧くださいませ。

【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法

初心者向けエクセルVBAで請求書を作成するシリーズ。今回は、エクセルVBAで頻繁に取り扱うワークシートを「オブジェクト名」を使って指定する方法です。オブジェクト名とは何か、またその編集方法もお伝えします。

tonari-it.com

2017.02.12

経費精算書のデータを一行だけ経費データに転記するVBAプログラム

まずは、太郎さんの経費精算書から一行だけデータを転記することを目指してみましょう。

ではVBEを開いて、以下のSubプロシージャを作成してみてください。

Sub 経費精算データ取り込み()

Dim ws As Worksheet
Set ws = Workbooks("201807経費精算書_経費_3001.xlsx").Worksheets(1)

wsData.Cells(2, 1).Value = ws.Range("G4").Value '1 対象月
wsData.Cells(2, 2).Value = ws.Cells(12, 1).Value '2 日付
wsData.Cells(2, 3).Value = ""  '3 部署No
wsData.Cells(2, 4).Value = ws.Range("G6").Value '4 部署
wsData.Cells(2, 5).Value = ws.Range("G8").Value '5 社員No
wsData.Cells(2, 6).Value = ws.Range("G7").Value '6 氏名
wsData.Cells(2, 7).Value = ws.Cells(12, 2).Value '7 科目
wsData.Cells(2, 8).Value = ws.Cells(12, 5).Value '8 摘要
wsData.Cells(2, 9).Value = ws.Cells(12, 6).Value '9 金額
wsData.Cells(2, 10).Value = ws.Cells(12, 7).Value '10 備考

End Sub

太郎さんの経費精算書のExcelファイルは「201807経費精算書_経費_3001.xlsx」というファイル名で、現在エクセルで開いている状態とします。

その状態で、上記Subプロシージャ「経費精算データ取り込み」を実行してみましょう。

結果として、こちらのように経費データシートに一行転記されればOKです。

こちらのVBAプログラムですが、やっていることは

• シートを操作する準備
• セルの値の転記

の２つだけです。

オブジェクト変数の宣言とセット

データ一覧から請求書を作成するシリーズをすでにご覧頂いている場合は、おさらいになりますが、オブジェクト変数についてお伝えしておきます。

エクセルVBAで特定のシートに値を書き込んだり、逆に値を取得したりなどの操作をする場合は、操作をするシートをオブジェクト変数にセットをしておくと、扱いやすくなります。

エクセルVBAでは変数を使うためには宣言が必要です。

Worksheetオブジェクトを入れるためのオブジェクト変数の宣言は

と書きます。

また、宣言したオブジェクト変数に特定のシートをオブジェクト変数にセットする場合

と記述します。

オブジェクト変数の場合は、数値や文字列の変数と異なりSetと記述する必要がありましたね。

Workbooksコレクションからブック名でWorkbookオブジェクトを取得する

それで、そのWorksheetオブジェクトをどのように指定するか…ですが、まずはその親となるWorkbookオブジェクトから取得してあげる必要があります。

太郎さんの経費精算書ファイルは、現在マクロを記述しているWorkbookオブジェクトとは異なりますので、ThisWorkbookプロパティでは取得できません。

なので、現在開いているWorkbooksコレクションの中から、ブック名を使って指定してあげる方法をとります。

以下の書式となります。

これでWorkbookオブジェクトを取得できます。

WorksheetsコレクションからインデックスでWorkseetオブジェクトを取得する

さらにその子オブジェクトである、Worksheetオブジェクトを取得していきます。

経費精算書ひな形のブックには、1つしかシートがないとすると、インデックスで指定するのが簡単です。

インデックスは1からはじまる整数で、シートの並び順で左から1,2,…と振られていきます。

シートが1枚しかなければ、その唯一のシートのインデックスは1と決まっているのです。

したがって、以下のコードは「開いているブックのうち「201807経費精算書_経費_3001.xlsx」のブックのワークシートの1番目のシートを、オブジェクト変数wsにセットしてね」という意味になります。

Set ws = Workbooks("201807経費精算書_経費_3001.xlsx").Worksheets(1)

セルの値を転記～CellsプロパティとRangeプロパティ

特定のセルから値を取得し、それを別のセルに転記をします。いわゆるコピペですね。

特定のセルの値の指定方法は２種類あります。

行数と列数を数値で指定するCellsプロパティを使う方法と

「A1」などといったセルのアドレスで指定するRangeプロパティを使う方法です。

ケースバイケースで都合の良い指定の仕方を採用するようにしましょう。

例えば、以下のコードであれば、「経費データ」シートの2行目1列目のセルに、「経費精算書」シートのG4セルの値を転記する、という命令になります。

wsData.Cells(2, 1).Value = ws.Range("G4").Value '1 対象月

また、以下のコードであれば、「経費データ」シートの2行目2列目のセルに、「経費精算書」シートの12行目1列目の値を転記する、という命令になります。

wsData.Cells(2, 2).Value = ws.Cells(12, 1).Value '2 日付

まとめ

今回はここまでで、バラバラのデータを一つのデータベースに集約・蓄積するVBAマクロの第一歩として

• オブジェクト変数の宣言とSet
• セルの値を転記

について解説をしてきました。

まだ一行を転記できただけですので、第2回で全部きちんと転記できるようにしていきます。

【エクセルVBA入門】Do While～Loop文で条件を満たす間繰り返し

エクセルVBAを使ってバラバラの経費精算書のデータを集約するシリーズです。今回はDo While～Loop文を使って条件を満たす間、任意の処理を繰り返すプログラムの書き方をマスターしていきます。

tonari-it.com

どうぞお楽しみにっ！

連載目次：エクセルVBAで経費データをデータベースに集約する

請求書シリーズと逆のパターンですが、バラバラの帳票からデータ一覧つまりデータベースに情報を集めて蓄積していく、というお仕事も多いと思います。ここでは各担当者から提出された経費精算書をデータベースに蓄積するプログラムを目標にして進めていきます。

1. 【エクセルVBA入門】バラバラの経費精算書をデータにまとめる
2. 【エクセルVBA入門】Do While～Loop文で条件を満たす間繰り返し
3. 【エクセルVBA入門】繰り返しを使ってデータの転記をするときの２つのポイント
4. 【エクセルVBA入門】With文でプログラムをスッキリわかりやすく書く
5. 【エクセルVBA入門】他のワークブックをWithで開く＆保存せずに閉じる
6. 【エクセルVBA入門】フォルダやファイルを操作するFileSystemオブジェクトとその使い方
7. 【エクセルVBA入門】For Each～Next文でフォルダ内のブック全てを開く方法
8. 【エクセルVBA入門】シートのデータがある最終行番号を求めるステートメントを徹底解説
9. 【エクセルVBA入門】開いたブック名から文字列を抽出して人為的なミスを回避する方法
10. 【エクセルVBA入門】マクロを作るときに知っておきたいマスタデータのこと
11. 【エクセルVBA入門】開いたブックのファイル名から番号を取り出して数値に変換する
12. 【エクセルVBA入門】マクロでVlookupを使ってデータを検索する方法
13. 【エクセルVBA入門】Vlookupメソッドを使ったときに発生するエラーを回避する方法
14. 【エクセルVBA入門】エラーが発生したときに分岐処理を追加する方法
15. 【エクセルVBA入門】オートフィルタや行の非表示で隠れている行を全て表示する