【VBAマクロ】AccessからExcel出力して請求書を作成する方法その4（VBA編）
お待たせしました！
このシリーズもパート4までやってきて、やっとVBAの内容です。
本当は、クエリの部分をもうちょっと書きたかったんですが、あまりにも長いとクレームが来そうなのでライトに進めます。笑
クエリの件については、別記事でアップしますね。
まずはレコードセットの存在を知る
これ系の機能を実装するにあたって、必ず知っておきたいのがレコードセットの存在についてです。
分かりやすく言えばテーブルのことなんですが、詳しく表現するとテーブル内にある1行が1レコードになります。
それが集まってレコードセットって集合体になる感じですね。
テーブルだけでなく、選択クエリの結果でもレコードセットが確認できますよね。
選択クエリの結果は、そこだけ見ればテーブルとなんら変わりはありません。
このレコードセットの中を1行ずつ見て、その情報を取得したり出力したりするのがレコードセットの操作。
今回は請求書エクセルへの出力になるので、クエリの結果をレコードセットとして取得して、1行ずつエクセルへ吐き出す処理をVBAで書きます。
ごちゃごちゃ言わんと、とりあえず書きます！
もう能書きたれてもしゃーないので、レコードセット操作のサンプルを書いていきます。
イメージしやすいように、下記の請求書を想定して書いていきましょう。
このフォーマットから分かることは、固定的な項目と流動的な項目が存在する点です。
〇〇御中とか請求書NOとか、下記の備考欄は1ページに一つしかないので固定になります。
一方で請求内訳は、その時々で数が違ってくるのでレコードを読み取ってエクセルへ書き込みの流れ。
この点に注意しながら作っていきます。
Private Sub payment(from As Date, to As Date)
Const conStartRow = 10
Const conStartCol = 2
const conEndCol = 4
Dim rs(1) As DAO.Recordset
Dim qd As DAO.QueryDef
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim iRow, iCol
With DoCmd //請求情報をテーブルへ格納
.SetWarnings False
.OpenQuery("q_delete_paymentItem") //t_paymentItemを一旦削除
.OpenQuery("q_insert_paymentItem") //t_paymentItemへ追加
.SetWarnings True
End With
Set rs(0) = CurrentDB.OpenRecordset("q_paymentInfo") //基本情報をグループ化
With rs(0)
If .EOF Then
MsgBox "該当データなし"
Else
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True //可視化
Do Until .EOF
Set xlBook = xlApp.Workbooks.Open("[パス名]")
Set xlSheet = xlBook.Worksheets("[シート名]")
//請求基本情報
xlSheet.Range("[顧客名のセル]").Value = !顧客名.Value
xlSheet.Range("[住所のセル]").Value = !住所.Value
xlSheet.Range("[連絡先のセル]").Value = !連絡先.Value
//請求内訳
Set qd = .QueryDefs("q_paymentItem")
qd.Parameters("[顧客名?]").Value = !顧客名.Value
Set rs(1) = qd.OpenRecordset
With rs(1)
iRow = conStartRow
Do Until .EOF
For iCol = conStartCol To conEndCol
xlSheet.Cells(iRow, iCol).Value = .Fields(iCol + 1).Value
Next iCol
iRow = iRow + 1
.MoveNext
Loop
.Close
End With
xlBook.SaveAs "C:¥請求書" & !顧客名.Value & ".xlsx"
//要注意
xlApp.Workbooks("請求書" & !顧客名.Value & ".xlsx").Close False
.MoveNext
Loop
With xlApp
.Visible = False
.Quit
End With
Set qd = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
End If
.Close
End With
Erase rs
End Sub思ったより複雑になりました（笑）
できるだけ簡単に書こうと思いましたが、思いのほか長いコードになってしまいました。
前提として、この請求書フォーマットは請求内訳が12項目までしか表示できません。
したがって、13以上の請求内訳には対応していないことをご了承ください。
13以上の項目にも対応したコードを紹介しようと思っていたんですが、あまりにも長ったらしいコードになってしまうので、今回は12項目までを想定した内容になっております。
今回はレコードセットを請求基本情報と請求内訳に分割して、それらをエクセルファイルへ書き込む内容にしました。
まずはクエリ。
基本情報クエリをループで回すことを最初にデザインします。
次に、基本情報をパラメータとした請求内訳のループをデザインしましょう。
今回は1顧客1ファイルなので、Loopの最後にBookを保存して閉じる工程が入ってきます。
この時に陥りやすいのが、コメントで「要注意」と記載した構文。
普通に考えればxlApp.Workbooks(C:¥[Boook名])と書いてしまいそうですが、これだとエラーになります。（何回もハマりました）
ここはファイル名だけを記載して、xlApp.Workbooks([Boook名])とするのが正解。
地味ですが、結構大事なポイントです。
請求書出力にここまで大変なんか！と思った方
すみません。大変なんです。
ただエクセル関数を駆使すれば、一行だけVBA書いてエクスポートさせる方法もあります。
今回、敢えてややこしい方法を紹介したのは、色んなレポート出力で使える汎用性の高い考え方だからです。
指定されたエクセルフォーマットへ出力するのって、本当に至難の技。
出来れば一行目がカラムで二行目からデータが始まっていて、セル結合とかも無い方が嬉しいものです。
でも現実はそうではありません。
クエリからエクセル出力までの流れを自分なりにデザインできれば、どんな形式でも対応できるので是非頑張って会得してくださいね。
