概要
定型フォーマットに結果を入力して何千枚もの書類を作成することがよくあります。これをエクセルファイルをひとつひとつ開いて入力していると、入力ミス、全角半角の間違いなどがおきやすくなります。またページの挿入・削除によるページ数の変更対応に時間がかかるなど修正の面でも問題が生じてきます。
このような業務においては、定型フォーマットに入力するデータをリストで一括管理し、変更が生じたらリスト上で編集するべきです。レコードの挿入や削除も、リスト上なら簡単に行えます。
書類の作成は変更対応が終了してから、リストからフォーマットへVBAを用いてコピーします。ここではその方法を説明していきたいと思います。
リストデータの読み込み
ID、名前、生年月日を登録した名簿があります（実際にはもっといろいろな項目があるでしょう)。エクセルの一行目にレコード名、二行目からデータが入力されていると仮定します。
| ID | 名前 | 生年月日 |
|---|---|---|
| 1 | 若杉 洋 | 2014/9/16 |
| 2 | 川中 貴美子 | 2014/9/14 |
| 3 | 原 恭子 | 2015/4/8 |
| … | … | … |
まずはこのリストが保存されているエクセルファイルの標準モジュールにVBAを記述していきます。
Sub ReadList()
Dim listWorksheet As Worksheet
Set listWorksheet = ThisWorkbook.Sheets(1)
Debug.Print ( _
listWorksheet.Cells(2, 1) & "," & listWorksheet.Cells(2, 2) & ", " & listWorksheet.Cells(2, 3))
End Sub
これを実行するとイミディエイトウィンドウにコンマ区切りでデータが表示されるはずです。
1,若杉 洋, 2014/09/16
これで、リストのデータを読み込めていることが確認できました。
フォーマットへの貼り付け
罫線やセルの塗りつぶし、オートシェイプなどで装飾された独自のフォーマットがそれぞれの組織で用意されていると思います。ここでは、IDをB2セル、名前をC2セル、生年月日をD2セルに入力することになっているとします。リストファイルと同じフォルダ内に、"format.xls"の名前で空白のワークシートを用意します。(D2セルだけは事前にセルの表示形式を日付形式にして、少しだけ列の幅を広げておきます。)
Sub OpenFormatSave()
Dim copyWorkbook As Workbook
Dim inputWorksheet As Worksheet
Set copyWorkbook = Workbooks.Open("format.xls")
Set inputWorksheet = copyWorkbook.Sheets(1)
inputWorksheet.Range("B2") = "1"
inputWorksheet.Range("C2") = "若杉 洋"
inputWorksheet.Range("D2") = "2014/9/16"
copyWorkbook.SaveAs Filename:="copy_ID_1.xls"
End Sub
これを実行すると、同フォルダ内に"copy_ID_1.xls"が作成されており、ワークシートのB2,C2,D2セルに値がコピーされています。また、フォーマットのエクセルファイルを直接開いて値を入力して別名で保存しているので、フォーマットのファイル自体は残っています。
データ読み込みと貼り付けを繰り返し行う
さて、あとは上の二つのコードを組み合わせて繰り返すだけです。以下の点を考慮しながらコードを完成させていきます。
- データは2行目から始まる。
- 最終行は何行目かわからないので、データがある行まで処理を続けることにする。
- その行にデータがあるか無いかは、IDの行が空白かどうかで判断する。
- データをコピーしたファイルはIDに対応したファイル名を付けて保存する。
Sub ListToFormat()
Dim copyWorkbook As Workbook
Dim inputWorksheet As Worksheet
Dim listWorksheet As Worksheet
Set listWorksheet = ThisWorkbook.Sheets(1)
Dim i As Long
i = 2
Do While (listWorksheet.Cells(i, 1) <> "")
Set copyWorkbook = Workbooks.Open("format.xls")
Set inputWorksheet = copyWorkbook.Sheets(1)
inputWorksheet.Range("B2") = listWorksheet.Cells(i, 1)
inputWorksheet.Range("C2") = listWorksheet.Cells(i, 2)
inputWorksheet.Range("D2") = listWorksheet.Cells(i, 3)
copyWorkbook.SaveAs Filename:="copy_ID_" & listWorksheet.Cells(i, 1) & ".xls"
copyWorkbook.Close
i = i + 1
Loop
End Sub
同じフォルダ内にデータの個数分のエクセルファイルが作成されたでしょうか。
おわりに
今回はリストからフォーマットへ値をコピーすることにのみ集中しましたが、実務ではより複雑な処理を求められるでしょう。
- より多くの項目への対応
- 複数のリストファイルを参照
- 男性の場合～、女性の場合～、などの条件による処理の分岐
- 体重からBMIを求めるなどの計算、またその合否判定、など・・・
処理が複雑になっても、ワークシートやブックを適切に選択し、必要なプロパティやメソッドを調べ、If文For文で処理を制御すれば対応できるでしょう。ただし、一つのSub内でそれらすべてを記述するとコードの見通しが悪くなり、変更への対処が難しくなってきます。そこで、ユーザー定義型や列挙型、コレクション、クラスモジュールなどを用いて適切に記述を分散して簡略化することが重要になります。
参考
MSDN: Excel オブジェクト モデルの概要