■ Excelのテンプレートを作成して高速化する （2000,2002,2003,2007）

 

 

図 Excelのテンプレートを使用して請求書を印刷する

 

Excelのテンプレートを使用して請求書を印刷します。Accessのフォームを開いたら、[Excelのテンプレートを使用して請求書印刷]のボタンをクリックします。Excelが起動されて、テンプレートに請求書が作成されます。このサンプルは、テンプレートに請求書を作成したら、自動的に印刷プレビューモードで表示します。

 

前準備：

このサンプルを実行するには、Excelのテンプレートファイルが必要になります。作業フォルダworkにサブフォルダTemplateを作成したら、ダウンロードしたフォルダに含まれるInvoice.xlsをコピーしておいてください。

 

 

1. 新規データベース作成

Accessを起動したら、ツールバーから新規作成のアイコンをクリックして、作業フォルダworkにW182.mdbを作成します。

 

2. テーブル、クエリ、フォーム、モジュールをインポート

Windowsのエクスプローラから、ダウンロードした182.mdbをダブルクリックして開きます。データベースウィンドウのオブジェクトから[テーブル]をクリックしたら、テーブル(tblOrders)をドラッグしてW182.mdbのデータベースウィンドウにドロップします。同様の手順で、テーブル(tblOrderDetails, tblProducts)、クエリ(qryProducts)、フォーム(frmPrintInvoice)、モジュール(basExcelAutomation)をドラッグ＆ドロップします。テーブル、クエリ、フォーム、モジュールをインポートしたら、182.mdbを閉じます。

 

3. ライブラリを追加

データベースウィンドウのオブジェクトから[モジュール]をクリックしたら、[basExcelAutomation]をダブルクリックして、デザインモードで開きます。VBEにモジュールが表示されたら、メニューバーから[ツール]→[参照設定]を選択します。｢参照設定｣のダイアログが表示されたら、参照可能なライブラリファイルから[Microsoft Excel X.X Object Library]をクリックしてチェックマークを付けます。[OK]ボタンをクリックしてダイアログを閉じます。

 

Note

Access 2000, 2002を使用するときはDAO 3.6のライブラリを追加する

 

Access 2000, 2002を使用するときは、VBEのメニューバーから[ツール]→[参照設定]を選択して、Microsoft DAO 3.6 Object Libraryを追加する必要があります。

 

 

 

4. フォームを開く

VBEのツールバーから表示(Access)のアイコンをクリックして、Accessのウィンドウに切り替えます。データベースウィンドウのオブジェクトから[フォーム]を選択したら、[frmPrintInvoice]をダブルクリックして開きます。フォームが開いたら、[Excelのテンプレートを使用して請求書印刷]のボタンをクリックします。

 

Excelが起動されて、テンプレートに請求書が作成されます。ここで作成した請求書は、自動的に印刷プレビューモードで表示されます。

 

 

図 テンプレートに作成された請求書が印刷プレビューモードで表示される

 

 

●解説

 

Excelのテンプレートファイルを開くには、WorkbooksコレクションのOpenメソッドを使用します。Openメソッドの引数には、テンプレートの物理パスを指定します。テンプレート(Invoice.xls)の物理パスを取得するには、CurrentProjectオブジェクトのPathプロパティを使用します。

 

このサンプルでは、Excelのオブジェクトを作成するのにCreateObjectメソッドの代わりにNew Excel.Applicationを使用しています。

 

‘ テンプレート(Invoice.xls)の物理パスを取得

strFilename = CurrentProject.Path & "\Template\Invoice.xls"

‘ Excelのインスタンス生成

Set mxlApp = New Excel.Application

‘ テンプレート(Invoice.xls)を読み込み専用で開く

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

 

請求書の明細行を作成するには、明細行のレコードセットを作成してセルに商品名、単価、数量、金額を格納します。クエリ(qryOrderDetailsByOrderID)は、受注ID(1001)の受注明細を抽出します。明細行のセルに値を格納するには、Cellsプロパティを使用します。商品名のセルを結合してフォーマットするには、サブプロシージャFormatRangeを呼びます。

 

‘ 明細行のレコードセット作成

Set db = CurrentDb

Set rst = db.OpenRecordset("qryOrderDetailsByOrderID")

Do Until rst.EOF

  intRow = intRow + 1

  ' セルに商品名を格納します

  .Cells(intRow, 2).Value = rst("ProductName")

  ‘ 商品名のセルを結合してフォーマットします

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

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

  :::

  rst.MoveNext

Loop

rst.Close

 

明細行を作成したら、請求書の小計、消費税、合計を作成します。表に罫線を引くには、サブプロシージャDrawChartを呼びます。DrawChartの引数には、罫線を引く範囲を指定します。請求書を印刷プレビューで表示するには、Excelを可視状態に切り替えてから、PrintPreviewメソッドを実行します。

 

‘ Excelを可視状態にする

.Visible = True

‘ 印刷プレビューで表示

.ActiveWindow.SelectedSheets.PrintPreview

 

‘ テンプレートに請求書を作成してプレビューモードで表示する

Public Sub PrintInvoice()

  DoCmd.Hourglass True

 

  ' テンプレートの物理パスを取得する

  strFilename = CurrentProject.Path & "\Template\Invoice.xls"

 

  ‘ Excelのインスタンス生成

  Set mxlApp = New Excel.Application

  ‘ テンプレート(Invoice.xls)を読み込み専用で開く

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

  With mxlApp

    ' 明細行のレコードセットを開く

    intRow = 2

    intStartRow = intRow + 1

    Set db = CurrentDb

    Set rst = db.OpenRecordset("qryOrderDetailsByOrderID")

    Do Until rst.EOF

      intRow = intRow + 1

      ' セルに商品名を格納します

      .Cells(intRow, 2).Value = rst("ProductName")

      ‘ 商品名のセルを結合してフォーマットします

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

      FormatRange strRange, _

        intHorizontalAlignment:=xlLeft, intVerticalAlignment:=xlBottom

     

      ' セルに単価を格納して結合します

      .Cells(intRow, 18).Value = rst("UnitPrice")

      strRange = .Cells(intRow, 18).Address & ":" & .Cells(intRow, 21).Address

      .Range(strRange).Select

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

      FormatRange strRange, _

        intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

     

      ' セルに数量を格納して結合します

      .Cells(intRow, 22).Value = rst("Quantity")

      strRange = .Cells(intRow, 22).Address & ":" & .Cells(intRow, 25).Address

      .Range(strRange).Select

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

      FormatRange strRange, _

        intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

     

      ' セルに金額を格納して結合します

      .Cells(intRow, 26).Value = "=" & .Cells(intRow, 18).Address(False, False) _

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

      strRange = .Cells(intRow, 26).Address & ":" & .Cells(intRow, 31).Address

      .Range(strRange).Select

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

      FormatRange strRange, _

        intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

     

      rst.MoveNext

    Loop

    rst.Close

    intEndRow = intRow

   

    ' セルに小計を格納して結合します

    intRow = intRow + 1

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

    strRange = .Cells(intRow, 22).Address & ":" & .Cells(intRow, 25).Address

    FormatRange strRange, _

      intHorizontalAlignment:=xlCenter, intVerticalAlignment:=xlBottom

 

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

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

    .Cells(intRow, 26).Value = strSum

    strRange = .Cells(intRow, 26).Address & ":" & .Cells(intRow, 31).Address

    .Range(strRange).Select

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

    FormatRange strRange, _

      intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

   

    ' セルに消費税を格納して結合します

    intRow = intRow + 1

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

    strRange = .Cells(intRow, 22).Address & ":" & .Cells(intRow, 25).Address

    FormatRange strRange, _

      intHorizontalAlignment:=xlCenter, intVerticalAlignment:=xlBottom

 

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

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

    strRange = .Cells(intRow, 26).Address & ":" & .Cells(intRow, 31).Address

    .Range(strRange).Select

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

    FormatRange strRange, _

      intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

   

    ' セルに合計を格納して結合します

    intRow = intRow + 1

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

    strRange = .Cells(intRow, 22).Address & ":" & .Cells(intRow, 25).Address

    .Range(strRange).Select

    FormatRange strRange, _

      intHorizontalAlignment:=xlCenter, intVerticalAlignment:=xlBottom

 

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

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

    .Cells(intRow, 26).Value = strSum

    strRange = .Cells(intRow, 26).Address & ":" & .Cells(intRow, 31).Address

    .Range(strRange).Select

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

    FormatRange strRange, _

      intHorizontalAlignment:=xlGeneral, intVerticalAlignment:=xlBottom

 

    ' 表の罫線を引く

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

      & .Cells(intEndRow, 31).Address

    DrawChart strRange

 

    ' 合計欄の罫線を引く

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

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

    DrawChart strRange

 

    ‘ Excelを可視状態にする

    .Visible = True

    ‘ ワークシートを印刷プレビューモードで表示

    .ActiveWindow.SelectedSheets.PrintPreview

  End With

 

  DoCmd.Hourglass False

End Sub