Accessから請求書をExcel経由で印刷するお手本

図6-7-1　Accessから請求書をExcel経由で印刷するサンプルデータベース

このサンプルデータベースは、Access の受注フォームから入力したデータを元に請求書をExcel経由で印刷します。請求書は、Access のレポートを使用して印刷することもできますが、Excel 経由で印刷した場合、編集してから印刷することができます。請求書をExcelに出力することにより、電子メールなどに添付して送付することもできます。受注フォームから、請求書の印刷ボタンをクリックすると、Excel が起動されて請求がプレビュー印刷されます。Excel に出力された請求書は、自由に編集してから印刷することができます。また、履歴としてファイル名を指定して保存しておくことも可能です。

サンプルデータベースで使用するテーブル、クエリ、フォームは、Accessのサンプルデータベース Northwind.mdbを流用します。

図6-7-2　請求書をExcel 2000に出力した例

サンプルデータベース(CH6-7.mdb)では、以下に示すノウハウを習得することができます。

◆　Access のデータをExcelに出力する方法

◆　Excel のテンプレートを作成して高速化する方法

◆　リンクされているテーブルを自動的に再リンクする方法

● サンプルデータベースで使用するテーブル/クエリ/フォームを準備するには

１　Access を起動したら作業フォルダに、新規データベース CH6-7.mdbを作成します。

２　ファイルメニューから外部データの取り込み、テーブルのリンクをクリックします。リンクのダイアログが表示されたら、Northwind.mdb（デフォルトで、”C:\Program Files\Microsoft Office\Office\Samples”にインストールされます。）を選択してリンクボタンをクリックします。テーブルのリンクダイアログが表示されたら、テーブルのタブをクリックしてすべて選択のボタンをクリックします。OKボタンをクリックしてリンクを完了させます。

図6-7-3　テーブルのリンクダイアログから全てのテーブルを選択してリンクさせる

３　ファイルメニューから、外部データの取り込み、インポートをクリックします。インポートのダイアログが表示されたら、Northwind.mdbを選択してインポートボタンをクリックします。オブジェクトのインポートダイアログが表示されたら、クエリのタブをクリックして、受注クエリ、受注明細金額、納品書を選択します。フォームのタブをクリックしたら、受注、受注サブフォームを選択してOKボタンをクリックしてインポートを完了させます。

図6-7-4　Northwind.mdbからクエリとフォームをインポートする

４　データベースウィンドウからクエリをクリックして、クエリの一覧を表示させたら、“納品書”を選択してマウスの右ボタンをクリックします。ショートカットメニューが表示されたら、名前の変更をクリックして“請求書”に書き替えます。

５　テーブル、クエリ、フォームの準備ができたら、Accessを終了させます。

● Excelで請求書のテンプレートを作成するには

１ Excel を起動したら、列AからBCを選択してマウスの右ボタンをクリックします。ショートカットメニューが表示されたら、列の幅をクリックして列幅を１に設定します。

２　挿入メニューから図èファイルからをクリックして、フォルダ(CH6-7)からNorthwind Tradersのロゴ(nwlogo.gif)を挿入します。ロゴをワークシートの左上に配置したら、ワードアートの挿入アイコンをクリックしてワードアートギャラリーを表示させます。スタイルの一覧から、左から５列目、上から２番目のスタイルを選択してOKボタンをクリックします。

図6-7-5　ワードアートギャラリーからスタイルを選択

３　ワードアートのテキストの編集ダイアログが表示されたら、フォントのコンボボックスからBernard MT Condensed、サイズのコンボボックスから３６を選択して、“Northwind Traders”を入力します。最後に、OKボタンをクリックして確定します。同様の手順で、“請求書”のワードアートイメージを作成して挿入します。

図6-7-6　テキストの編集ダイアログからNorthwind Tradersを入力

４　ワードアートでイメージを作成したら、図6-7-7のように配置します。

図6-7-7　ワークシートに会社のロゴとワードアートで作成したイメージを配置した例

８　イメージを配置したら、日付、請求先名、受注番号、納品日、支払期限、御請求額合計、明細行などを出力するセルに書式を設定します。書式の内容については、フォルダ(CH6-7)に格納されている、Invoice.xlsを参照してください。ここで設定した書式は、実行時に設定することができますが、事前にセルの書式を設定しておくことにより、請求書を作成する処理を高速化することができます。

図6-7-8　日付、請求先名、御請求額合計、明細行などを出力するセルに書式を設定する

９　テンプレート（請求書の雛型）をInvoice.xlsの名称で作業フォルダに保存したら、Excelを終了します。

● 受注フォームからExcel 経由で請求書を印刷するフォームを作成するには

１　Accessを起動したら、作業フォルダに作成したCH6-7.mdbを開きます。

２　データベースウィンドウが表示されたら、CH6-7.mdbを開いて、モジュールbasAutomation, basLinkedTables, basMyLib, basUtility, basWindowsCommonDialogをコピーします。（CH6-7.mdbのデータベースウィンドウからモジュールをドラッグして、作業フォルダのCH6-7.mdbのデータベースウィンドウに貼り付けると自動的にコピーされます。）

３　モジュールbasAutomationをデザインモードで開きます。VBEのツールメニューから参照設定をクリックして、参照可能なライブラリファイルからMicrosoft DAO 3.6 Object Library、Microsoft Excel X.0 Object Library、Microsoft ADO Ext. 2.x for DDL and Securityを選択して、OKボタンをクリックします。

図6-7-9　DAO 3.6 , Excel 9.0, ADO Ext 2.x for DDL and Securityを登録する

４　データベースウィンドウからフォームをクリックして、フォームの一覧を表示させたら、受注フォームをデザインモードで開きます。フォーム最下位に配置されている、“納品書の印刷”のコマンドボタンのプロパティを表示させたら、名前を“請求書印刷”、標題を“請求書の印刷(&p)”に書き替えます。メニューからコードのアイコンをクリックして、フォームモジュールを表示させたら、モジュール内の全てのコードを削除します。CH6-7.mdbを表示させたら、受注フォームのフォームモジュールをコピーして貼り付けます。フォームモジュールのソースコードは、リスト6-7-1を参照してください。

５　受注フォームをビューモードで開いたら、請求書の印刷ボタンをクリックします。Excel が起動されて、受注フォームに表示されているデータがExcelのワークシート上に出力されてプレビュー印刷されます。Excelから閉じるのボタンをクリックするとワークシートが表示されますので、自由に編集して印刷することができます。ファイルメニューから名前を付けて保存をクリックして、任意の名称で保存したら、Excelを終了させます。

図6-7-10　受注フォームに表示されているデータが、Excelのワークシート上に請求書として出力された例

６　Accessにフォーカスが移動すると、“請求書搬出完了！”のメッセージが表示されていますのでOKボタンをクリックして閉じます。受注フォームを保存して、Accessを終了させます。

Form_Open()イベントの処理

このイベントは、フォームが開かれたときに発生します。このイベントでは、VerifyLinks_FS()関数を呼び出して、リンクテーブルのリンクが壊れていないか調べます。壊れているときは、Windowsのコモンダイアログが表示されますので、Northwind.mdbを選択して再リンクすることができます。VerifyLinks_FS()は、basLinkedTablesに登録されています。Windowsのコモンダイアログを表示するOpenFile_FS()関数は、basWindowsCommonDialogに登録されています。

請求書印刷_Click()イベントの処理

このイベントは、受注フォームから請求書の印刷ボタンをクリックしたときに発生します。このイベントでは、プロシージャPrintInvoiceWithExcelを呼び出してExcel上に請求書を作成します。このプロシージャには、引数としてフォームを指定します。PrintInvoiceWithExcelは、リスト6-7-2を参照してください。

リスト6-7-1 受注フォームのフォームモジュール

Option Compare Database

Option Explicit

Private Sub Form_Open(Cancel As Integer)

If Not VerifyLinks_FS("Northwind.mdb", "得意先") Then

MsgBox "テーブルの再リンクに失敗しました！" & vbCrLf & _

"Accessのリンクテーブルマネージャから” & _

“ Northwind.mdb を再リンクしてください.", _

vbCritical + vbOKOnly

End If

End Sub

Private Sub 請求書印刷_Click()

Call PrintInvoiceWithExcel(Me)

End Sub

PrintInvoiceWithExcel()

このプロシージャは、引数で指定されたフォームに表示されている受注データを元に請求書をExcel上に作成します。New Excel.ApplicationでExcelのインスタンスを生成したら、WorkbooksコレクションのOpenメソッドで請求書の添付ファイル(Invoice.xls)を読み込み専用で開きます。

Set mxlApp = New Excel.Application

Set xlWrkbk = mxlApp.Workbooks.Open(Filename:=strFilename, ReadOnly:=True)

Rangeプロパティにセルのアドレスを指定して、日付、請求先名、受注番号、納品日、支払期限などの情報を格納します。

.Range("AM8").Value = Date

.Range("B10").Value = frm.出荷先名 & "　御中"

.Range("B12").Value = "下記の通りご請求申し上げます"

.Range("B15").Value = "受注番号："

.Range("K15").Value = frm.受注コード

.Range("B16").Value = "納品日："

.Range("K16").Value = frm.出荷日

.Range("B17").Value = "支払期限："

.Range("K17").Value = "納品月末締め翌月末支払い"

strSQLに請求書の明細行を読み込むためのSQLを格納したら、RecordsetオブジェクトのOpenメソッドで明細行を開きます。

Set rs = New ADODB.Recordset

rs.Open strSQL, CurrentProject.Connection

Do Until rs.EOF…Loopのループでは、レコードセットから商品名、単価、数量を取得して対応するセルに格納します。金額のセルには、（単価＊数量）の計算式を埋め込んでExcel上で計算させます。商品名、単価、数量などを格納するセルのアドレスを取得するのに、CellsのAddressプロパティを使用しています。例えば、Cells(1,1).Addressのように指定すると、”$A＄1”のようにセルの絶対アドレスが取得できます。セルの相対アドレスを取得するには、Cells(1,1).Address(False,False)のように指定します。この場合、”A1”のように、相対アドレスが返されます。strRangeにセルの範囲（“X99:X99”）を設定したら、FormatRangeを呼び出して、セルを結合して書式を設定します。FormatRangeには、引数としてセルの範囲、横位置、縦位置を指定します。

.Cells(intRow, 2).Value = rs("商品名")

strRange = .Cells(intRow, 2).Address & ":" & .Cells(intRow, 27).Address

FormatRange strRange, intHorizontalAlignment:=xlLeft, intVerticalAlignment:=xlBottom

発注書の明細行をワークシート上に出力したら、小計、消費税、合計の計算式を埋め込みます。DrawChartを呼び出して、明細行と小計、消費税、合計のセルに罫線を引きます。DrawChartの引数には、セルの範囲を指定します。

御請求額合計のセルに計算式を埋め込んだら、Excelの可視(Visible)プロパティをTrueに設定して、PrintPreviewメソッドで請求書をプレビュー印刷します。

FormatRange()

このプロシージャは、引数で指定された情報を元にセルを結合して同時に書式を設定します。引数には、セルの範囲(strRange)、横位置(intHorizontalAlignment)、縦位置(intVerticalAlignment)を指定します。.Range(strRange).Selectで、対象となるセルを選択したら、HorizontalAlignment, VerticalAlignmentを設定します。最後に、MergeCellsをTrueに設定してセルを結合します。

DrawChart()

このプロシージャは、引数で指定されたセルの範囲にオートフォーマット機能を使用して罫線を引きます。引数strRangeには、セルの範囲を(“A1:C1”)の形式で指定します。

.Range(strRange).Selectで、引数で指定した範囲を選択したら、SelectionのAutoFormatメソッドを使用して表３（xlRangeAutoFormatLocalFormat3）のスタイルの罫線を引きます。書式は、罫線(Border)のみ設定します。表示形式(Number)、フォント(Font)、配置(Alignment)、パターン(Pattern)、幅と高さ(Width)の書式は設定しません。

図6-7-11 オートフォーマットから表３のスタイルを選択して罫線を引く

リスト6-7-2　basAutomation

Option Compare Database

Option Explicit

Private mxlApp As Excel.Application

Private Const conTemplate = "\Invoice.xls"

Sub PrintInvoiceWithExcel(frm As Form_受注)

Dim xlWrkbk As Excel.Workbook

Dim xlWrksh As Excel.Worksheet

Dim rs As ADODB.Recordset

Dim strSQL As String

Dim strFilename As String

Dim intRow As Integer

Dim strRange As String

Dim intStartRow As Integer

Dim intEndRow As Integer

Dim strSum As String

On Error GoTo ErrHandler

DoCmd.Hourglass True

' ﾃﾝﾌﾟﾚｰﾄのﾌﾙﾊﾟｽ名を取得する (X:\Work\Invoice.xls)

strFilename = CurrentProject.Path & "\" & conTemplate

Set mxlApp = New Excel.Application

Set xlWrkbk = mxlApp.Workbooks.Open(Filename:=strFilename, ReadOnly:=True)

With mxlApp

.Range("AM8").Value = Date

.Range("B10").Value = frm.出荷先名 & "　御中"

.Range("B12").Value = "下記の通りご請求申し上げます"

.Range("B15").Value = "受注番号："

.Range("K15").Value = frm.受注コード

.Range("B16").Value = "納品日："

.Range("K16").Value = frm.出荷日

.Range("B17").Value = "支払期限："

.Range("K17").Value = "納品月末締め翌月末支払い"

' 請求書の明細行を読み込むSQLを作成します

' SELECT DISTINCTROW 商品名, 単価, 数量, 明細金額

' FROM 請求書

' WHERE 受注コード=1001;

strSQL = "SELECT DISTINCTROW 商品名, 単価, 数量, 明細金額" _

& " FROM 請求書" _

& " WHERE 受注コード=" & frm.受注コード

' 明細行のﾚｺｰﾄﾞｾｯﾄを開きます

intRow = 21

intStartRow = intRow + 1 ' 22

Set rs = New ADODB.Recordset

rs.Open strSQL, CurrentProject.Connection

Do Until rs.EOF

intRow = intRow + 1

' セルに商品名をセットして結合します

.Cells(intRow, 2).Value = rs("商品名")

strRange = .Cells(intRow, 2).Address & ":" & .Cells(intRow, 27).Address

FormatRange strRange, _

intHorizontalAlignment:=xlLeft, intVerticalAlignment:=xlBottom

' セルに単価をセットして結合します

.Cells(intRow, 28).Value = rs("単価")

strRange = .Cells(intRow, 28).Address & ":" & .Cells(intRow, 35).Address

.Range(strRange).Select

.Selection.NumberFormatLocal = "#,##0"

FormatRange strRange, _

intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

' セルに数量をセットして結合します

.Cells(intRow, 36).Value = rs("数量")

strRange = .Cells(intRow, 36).Address & ":" & .Cells(intRow, 41).Address

.Range(strRange).Select

.Selection.NumberFormatLocal = "#,##0_ "

FormatRange strRange, _

intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

' セルに金額をセットして結合します

.Cells(intRow, 42).Value = "=" & .Cells(intRow, 28).Address(False, False) _

& "*" & .Cells(intRow, 36).Address(False, False) ' =AB99*AJ99

strRange = .Cells(intRow, 42).Address & ":" & .Cells(intRow, 53).Address

.Range(strRange).Select

.Selection.NumberFormatLocal = "#,##0"

FormatRange strRange, _

intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

rs.MoveNext

Loop

rs.Close

intEndRow = intRow ' Save last row

' セルに小計をセットして結合します

intRow = intRow + 1

.Cells(intRow, 36).Value = "小　計"

strRange = .Cells(intRow, 36).Address & ":" & .Cells(intRow, 41).Address

FormatRange strRange, _

intHorizontalAlignment:=xlCenter, intVerticalAlignment:=xlBottom

strSum = "=SUM(" & .Cells(intStartRow, 42).Address(False, False) & ":" & _

.Cells(intEndRow, 42).Address(False, False) & ")" ' = SUM(AP99:AP99)

.Cells(intRow, 42).Value = strSum

strRange = .Cells(intRow, 42).Address & ":" & .Cells(intRow, 53).Address

.Range(strRange).Select

.Selection.NumberFormatLocal = "#,##0"

FormatRange strRange, _

intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

' セルに消費税をセットして結合します

intRow = intRow + 1

.Cells(intRow, 36).Value = "消費税"

strRange = .Cells(intRow, 36).Address & ":" & .Cells(intRow, 41).Address

FormatRange strRange, _

intHorizontalAlignment:=xlCenter, intVerticalAlignment:=xlBottom

.Cells(intRow, 42).Value = "=" _

& .Cells(intRow - 1, 42).Address(False, False) & "*0.05" ' =AP99*0.05

strRange = .Cells(intRow, 42).Address & ":" & .Cells(intRow, 53).Address

.Range(strRange).Select

.Selection.NumberFormatLocal = "#,##0"

FormatRange strRange, _

intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

' セルに合計をセットして結合します

intRow = intRow + 1

.Cells(intRow, 36).Value = "合　計"

strRange = .Cells(intRow, 36).Address & ":" & .Cells(intRow, 41).Address

.Range(strRange).Select

FormatRange strRange, _

intHorizontalAlignment:=xlCenter, intVerticalAlignment:=xlBottom

strSum = "=SUM(" & .Cells(intRow - 2, 42).Address(False, False) & ":" & _

.Cells(intRow - 1, 42).Address(False, False) & ")" ' = SUM(AP99:AP99)

.Cells(intRow, 42).Value = strSum

strRange = .Cells(intRow, 42).Address & ":" & .Cells(intRow, 53).Address

.Range(strRange).Select

.Selection.NumberFormatLocal = "#,##0"

FormatRange strRange, _

intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

' 表の罫線を引く

strRange = .Cells(intStartRow - 1, 2).Address & ":" _

& .Cells(intEndRow, 53).Address

DrawChart strRange

' 合計欄の罫線を引く

strRange = .Cells(intEndRow + 1, 36).Address & ":" _

& .Cells(intEndRow + 3, 42).Address

DrawChart strRange

' セルに御請求額合計をセット

.Range("B19").Value = "=+" _

& .Cells(intEndRow + 3, 42).Address(False, False) ' =+AP99

.Range("A1").Select

.Visible = True

.ActiveWindow.SelectedSheets.PrintPreview

End With

MsgBox "請求書搬出完了！"

ExitHere:

On Error Resume Next

DoCmd.Hourglass False

If Not rs Is Nothing Then

rs.Close

Set rs = Nothing

End If

If Not mxlApp Is Nothing Then

xlWrkbk.Close SaveChanges:=False

mxlApp.Quit

Set xlWrkbk = Nothing

Set mxlApp = Nothing

End If

Exit Sub

ErrHandler:

MsgBox Err.Number & ":" & Err.Description, vbCritical

Resume ExitHere

End Sub

Sub FormatRange(strRange As String, _

intHorizontalAlignment As Integer, _

intVerticalAlignment As Integer)

With mxlApp

.Range(strRange).Select

With .Selection

.HorizontalAlignment = intHorizontalAlignment

.VerticalAlignment = intVerticalAlignment

.WrapText = False

.Orientation = 0

.AddIndent = False

.ShrinkToFit = False

.MergeCells = True

End With

End Sub

Sub DrawChart(strRange As String)

With mxlApp

.Range(strRange).Select

.Selection.AutoFormat _

Format:=xlRangeAutoFormatLocalFormat3, _

Number:=False, _

Font:=False, _

Alignment:=False, _

Border:=True, _

Pattern:=False, _

Width:=False

End With

End Sub

Tips

PrintInvoiceWithExcel()では、フォームの引数を定義するとき

Sub PrintInvoiceWithExcel(frm As Form)

の替わりに

Sub PrintInvoiceWithExcel(frm As Form_受注)

のように直接受注フォームを指定しています。この場合、frmのオブジェクトを参照するとき、処理速度が高速化されます。

ダウンロード (CH6-7.lzh)

Accessお手本データベースのホームへ戻る