お待たせしました！

このシリーズもパート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書いてエクスポートさせる方法もあります。

今回、敢えてややこしい方法を紹介したのは、色んなレポート出力で使える汎用性の高い考え方だからです。

指定されたエクセルフォーマットへ出力するのって、本当に至難の技。

出来れば一行目がカラムで二行目からデータが始まっていて、セル結合とかも無い方が嬉しいものです。

でも現実はそうではありません。

クエリからエクセル出力までの流れを自分なりにデザインできれば、どんな形式でも対応できるので是非頑張って会得してくださいね。