前回、商品在庫画面の追加機能を作成しました。
この記事では、発注リストを例に帳票の出力方法を解説します。
VBA在庫管理システムVer01
帳票出力の準備
出力帳票の作成
レポートブック（在庫管理REPORT.xlsx）に発注リスト用のシートを作成します。
帳票はヘッダー部のみ作成します。標題はVBAで書き込みますが、サンプルとして入力しています。
書式は、左から在庫情報・発注情報の順に作成します。
出力ボタンの作成
商品在庫画面に発注リストボタンを追加します。
ツールボックスでコマンドボタンを選択し、フッター部でドラッグして、コントロールを追加します。
プロパティウィンドウで詳細設定します。
※プロパティ設定後のユーザーフォーム
標準モジュールの作成
標準モジュールを追加し、オブジェクト名を発注リストとします。
帳票を出力するプロシージャはユーザーフォームの動作とは異なりますので、標準モジュールに作成していきます。
発注リストの出力
変数の設定
商品マスタデータの取り込み用変数の設定をしていきます。
VBEで標準モジュール 発注リストを選択し、宣言セクションに列挙型変数をPrivateで宣言します。
Option Explicit 'wsItemMasterの項目列設定 Private Enum wsItemMasterColumns M商品ID = 1 M商品名称 M登録日 M更新日 M発注先 M発注単位 M梱包数 M最大在庫 M発注点 M棚位置 M棚卸日 M棚卸数 M出庫済み M出庫予定 M入庫済み M入庫予定 M現在在庫 M有効在庫 M発注Flag M削除 End Enum
@1.列挙型変数の宣言
標準モジュール 発注リストでは、商品マスタシートの全ての列を列挙型変数で宣言します。
発注リスト
帳票を出力するプロシージャをサブルーチンとして、標準モジュール 発注リストに作成します。
Sub ShowItemOrderReport() Application.ScreenUpdating = False '発注リストデータの取得--------------------------------------------------------------------------------------------------- '作業ブックを開く Workbooks.Open データ位置 & "在庫管理DATA.xlsx" 'オブジェクト変数の取得 Dim wsItemMaster As Worksheet Set wsItemMaster = Workbooks("在庫管理DATA.xlsx").Sheets("M_商品") '商品発注データの取り込み Dim aryOrder() As Variant Dim i As Long, j As Long '有効在庫/発注フラグを昇順で並べ替え wsItemMaster.Range("A1").Sort key1:=wsItemMaster.Range("R1"), order1:=xlAscending, Header:=xlYes wsItemMaster.Range("A1").Sort key1:=wsItemMaster.Range("S1"), order1:=xlAscending, Header:=xlYes '最終行の取得 Dim wsItemMasterRow As Long wsItemMasterRow = wsItemMaster.Cells(Rows.Count, wsItemMasterColumns. M発注Flag).End(xlUp).Row '発注リストがある時のみデータ取り込み If wsItemMasterRow >= 2 Then '変数の設定 ReDim aryOrder(wsItemMasterRow - 2, 9) As Variant j = 0 '変数への取り込み For i = 2 To wsItemMasterRow If wsItemMaster.Cells(i, wsItemMasterColumns.M削除) = 0 Then aryOrder(j, 0) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品ID) aryOrder(j, 1) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品名称) aryOrder(j, 2) = wsItemMaster.Cells(i, wsItemMasterColumns.M最大在庫) aryOrder(j, 3) = wsItemMaster.Cells(i, wsItemMasterColumns.M現在在庫) aryOrder(j, 4) = wsItemMaster.Cells(i, wsItemMasterColumns.M有効在庫) aryOrder(j, 5) = wsItemMaster.Cells(i, wsItemMasterColumns.M発注点) aryOrder(j, 6) = wsItemMaster.Cells(i, wsItemMasterColumns.M発注単位) aryOrder(j, 7) = wsItemMaster.Cells(i, wsItemMasterColumns.M入庫予定) aryOrder(j, 8) = Int((aryOrder(j, 2) - aryOrder(j, 4) - aryOrder(j, 7)) / aryOrder(j, 6)) * aryOrder(j, 6) aryOrder(j, 9) = Format(DateAdd("d", 7, Date), "yy/mm/dd") j = j + 1 End If Next End If '作業ブックを閉じる Workbooks("在庫管理DATA.xlsx").Close savechanges:=False '発注リストデータの書込--------------------------------------------------------------------------------------------------- '作業ブックを開く Workbooks.Open データ位置 & "在庫管理REPORT.xlsx" 'オブジェクト変数の取得 Dim wsOrderList As Worksheet Set wsOrderList = Workbooks("在庫管理REPORT.xlsx").Sheets("発注リスト") '最終行の取得 Dim wsOrderListRow As Long wsOrderListRow = wsOrderList.Cells(Rows.Count, 1).End(xlUp).Row '発注リストのリセット If wsOrderListRow > 4 Then wsOrderList.Rows("5:" & wsOrderListRow).Delete shift:=xlUp End If '発注リストへの書き込み If wsItemMasterRow >= 2 Then wsOrderList.Cells(5, 1).Resize(UBound(aryOrder, 1) + 1, UBound(aryOrder, 2) + 1).Value = aryOrder End If '最終行の再取得 wsOrderListRow = wsOrderList.Cells(Rows.Count, 1).End(xlUp).Row '罫線の設定 With wsOrderList.Range("A4:K" & wsOrderListRow).Borders(xlInsideVertical) .LineStyle = xlDot .Weight = xlHairline End With With wsOrderList.Range("C4:C" & wsOrderListRow).Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin End With With wsOrderList.Range("F4:F" & wsOrderListRow).Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin End With With wsOrderList.Range("I4:I" & wsOrderListRow).Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin End With With wsOrderList.Range("A4:M" & wsOrderListRow).Borders(xlInsideHorizontal) .LineStyle = xlDot .Weight = xlHairline End With 'ブックの最大化 ActiveWindow.WindowState = xlMaximized '表題の作成 wsOrderList.Cells(2, 1).Value = "● " & Format(Date, "yyyy/mm/dd") & " 発注リスト" '印刷範囲の設定 wsOrderList.PageSetup.PrintArea = wsOrderList.Range("A1:M" & wsOrderListRow).Address '印刷プレビューの表示 Application.CommandBars.ExecuteMso "PrintPreviewAndPrint" Application.ScreenUpdating = True '出力フォームの表示 frmItemOrderReport.show End Sub
@1. 作業ブックを開く
Application.ScreenUpdatingをFalsに設定に設定しエクセルの描画を止め、データブックを開き、M_商品シートを変数に格納します。
@2.シートの並び替え
有効在庫と発注フラグを昇順に並び替え、リストを成形します。
@3.最終行の取得
発注フラグ行で最終行を取得することにより、取り込みに必要な発注データ行を取得します。
@4.配列変数への取り込み
発注データが有るときのみ、データを取り込みます。
配列変数の要素数を確定させたあと、For〜Next構文でデータを取り込みます。
データは論理削除の構造なので、削除フラグが立っているものは取り込みません。
@5.ブックの立ち下げ
シートを操作してますので、初期化するため、ブックは保存せずに閉じます。
@6.作業ブックを開く
レポートブックを開き、発注リストシートを変数に格納します。
このとき、作業シートに前回のデータが残っているので、削除用に最終行も取得します。
@7.発注リストのリセット
発注リストの前回データを削除します。
@8.発注リストへの書き込み
発注データが有るときのみ、データを書き込みます。
データを取り込んだ配列変数 aryOrderをResizeで発注リストに書き込みます。
@9.書式設定
罫線は、線の種類と太さを設定しながら引きます。
標題を設定し、発注リストが完成です。
@10.印刷設定
印刷範囲を指定し、プレビュー画面を表示させます。
また、Application.ScreenUpdatingもTrue設定に戻します。
@11.終了フラグ
出力フォームを表示して、処理は終了です。
出力フォームの作成
帳票出力完了後の終了フラグ用に、新たにユーザーフォームを作成します。
今回はユーザーフォームをコピーして、作成していきます。
VBEを開き、frmAcceptInspectionReport を選択します。
新たにエクセルブックを開きます。
プロジェクトエクスプローラーに VBAProject (Book 1) が追加表示されます。
VBAProject (在庫管理 System.xlsm) の frmAcceptInspectionReport を VBAProject (Book 1) へドラッグすると、ユーザーフォームがコピーされます。
コピーしたユーザーフォームの、オブジェクト名を frmItemOrderReport に、ラベルを''発注リストを作成しました。''に変更します。
VBAProject (Book 1) の frmItemOrderReport を VBAProject (在庫管理 System.xlsm) へドラッグし、ユーザーフォームをコピーした後、Book 1を閉じます。
※VBA実行時の出力画面
閉じるボタンのオブジェクト名やクリック時の動作コードは、コピーされていますので確認してみてください。
発注リストボタンの設定
発注リストボタンクリック時の動作を作成します。サブルーチンを実行させます。
Private Sub btnItemOrderReport_Click() '発注リストの出力 Call ShowItemOrderReport End Sub
@1.サブルーチンの実行
発注リスト出力のプロシージャを実行します。
発注リストの出力
発注リストボタンをクリックし、動作を確認します。
画面上では、商品在庫画面の上に、発注リスト。更にその上に、出力フォームが表示されている状態です。
発注リストは、在庫情報を表示することにより、帳票のみで発注検討ができるようにしています。また、発注量は発注単位で換算し、希望納期は発注期間を１週間として表示する発注勧奨を出力しています。
出力時は、出力フォームがアクティブな状態なので、印刷するには、エクセルシートをクリックしアクティブにしてから、印刷ボタンをクリックします。２回、クリックが必要になります。
商品在庫画面の帳票出力部分ができました。
次回は商品在庫画面の棚卸集計表の出力部分を作成していきます。
www.minizaiko.com
