ous">

小さな在庫管理

在庫管理導入からシステム作成まで詳細解説！

トップ > システムVer02 > 商品在庫画面の発注リスト出力　[VBA在庫管理システムVer02#17]

商品在庫画面の発注リスト出力　[VBA在庫管理システムVer02#17]

システムVer02 VBA Access

前回、商品在庫画面の受払履歴の表示部分を作成しました。

この記事では、発注リストの出力部分を作成していきます。


VBA在庫管理システムVer01

発注リストの出力

VBA在庫管理システムVer01

Ver01では、発注リストを出力するプロシージャをサブルーチンとして、標準モジュール 発注リストに作成しました。

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

クエリの作成

発注リスト出力用クエリを作成していきます。
クエリの名称は、Q_発注リスト とします。

クエリデザイン画面のクエリデザインウィンドウに、M_商品テーブル および Q_0105_在庫数クエリ のフィールドリストを表示させます。

デザイングリッドでクエリを設定します。
デザイングリッド

＊ Q_発注リスト　デザインビュー
デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_発注リスト　データシートビュー
データシートビュー

Ver01では、商品在庫データを配列変数に取り込む際に、商品在庫データに以下の処理を加えました。これは、クエリで実行します。
　① 有効在庫数を昇順で並べ替え
　② 発注Flag＝◯ のみ取り込み
　③ 削除フラグ＝0 のみ取り込み

①の処理を設定します。有効在庫数の [並べ替え] を昇順に設定します。
なお、②③の処理を [抽出条件] に設定します。それぞれのフィールドは条件フィールドのため、[表示] のチェックを外して、データシートビューには非表示とします。

デザイングリッドでクエリを設定します。
デザイングリッド

＊ Q_発注リスト　デザインビュー
デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_発注リスト　データシートビュー
データシートビュー
　

発注リストの出力

発注リストを出力させるプロシージャー：ShowItemOrderReportを修正します。

Sub ShowItemOrderReport()

'発注リストデータの取得---------------------------------------------------------------------------------------------------

'データベース接続
Call DB接続

'''''''''' レコード抽出 ''''''''''

'レコードセットオブジェクトの作成
Dim adoRs As Object
Set adoRs = CreateObject("ADODB.Recordset")

'SQL文の変数への取り込み
Dim strSQL As String
strSQL = "SELECT*FROM Q_発注リスト"

'レコードセットの取得
adoRs.Open strSQL, adoCn

Dim aryOrder() As Variant
Dim i As Long

'データ取り込み
If adoRs.RecordCount >= 1 Then    '発注リストがある時

    '変数の設定
    ReDim aryOrder(adoRs.RecordCount - 1, 9) As Variant
    i = 0
    
    '変数への取り込み
    Do Until adoRs.EOF
        aryOrder(i, 0) = adoRs!商品ID
        aryOrder(i, 1) = adoRs!商品名称
        aryOrder(i, 2) = adoRs!最大在庫
        aryOrder(i, 3) = adoRs!現在在庫数
        aryOrder(i, 4) = adoRs!有効在庫数
        aryOrder(i, 5) = adoRs!発注点
        aryOrder(i, 6) = adoRs!発注単位
        aryOrder(i, 7) = adoRs!入庫予定数
        aryOrder(i, 8) = Int((aryOrder(i, 2) - aryOrder(i, 4) - aryOrder(i, 7)) / aryOrder(i, 6)) * aryOrder(i, 6)
        aryOrder(i, 9) = Format(DateAdd("d", 7, Date), "yy/mm/dd")
        i = i + 1                    'カウンターを加算

        adoRs.MoveNext       'カーソルを1行下へ
    Loop

Else    '発注リストがない時

    '変数の設定
    ReDim aryOrder(1, 1) As Variant

    '変数への取り込み
    aryOrder(0, 1) = "＊発注なし"

End If

'レコードセットオブジェクトの破棄
adoRs.Close
Set adoRs = Nothing

''''''''''''''''''''''''''''''''''''''''

'データベース切断
Call DB切断


'発注リストデータの書込---------------------------------------------------------------------------------------------------
    
    Application.ScreenUpdating = False

'作業ブックを開く
Workbooks.Open データ位置 & "在庫管理REPORT.xlsx"
    
'オブジェクト変数の取得
Dim wsOrderList As Worksheet
Set wsOrderList = Workbooks("在庫管理REPORT.xlsx").Sheets("発注リスト")

'最終行の取得

wsOrderList.Activate
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

'発注リストへの書き込み
wsOrderList.Cells(5, 1).Resize(UBound(aryOrder, 1) + 1, UBound(aryOrder, 2) + 1).Value = aryOrder

'最終行の再取得
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 + 1).Address

'印刷プレビューの表示
Application.CommandBars.ExecuteMso "PrintPreviewAndPrint"

    Application.ScreenUpdating = True

'出力フォームの表示
frmItemOrderReport.Show

End Sub

@1. データベース接続
サブプロシージャ：DB接続を実行し、アクセスDBと接続します。

@2. レコードセットオブジェクトの作成
adoRsオブジェクトを作成します。

@3. SQL文の変数への取り込み
データ抽出用のSQL文を作成し、文字列変数に格納します。
今回は、Q_発注リスト の、全てのフィールドを抽出します。

@4. レコードセットの取得
adoRsオブジェクトのOpenメソッドを使用し、 SQLを実行して、抽出結果をレコードセットへ取り込みます。

@5. 配列変数の設定
データ格納用に、配列変数を宣言し、要素数を設定します。
adoRs.RecordCountで、レコードセットのレコード数を取得できます。

@6. 配列変数への取り込み
Do_Until_Loop構文でadoRsオブジェクト内をループしながら、変数に取り込んでいきます。
① Q_発注リストの全てのフィールドを、配列変数に取り込みます。
② 推奨発注量を計算し、配列変数に取り込みます。
③ １週間後を希望納期として、配列変数に取り込みます。
また、リストデータがないときは、発注なしを表示するための変数を作成します。

@7. レコードセットオブジェクトの破棄
レコードセットのデータは、配列変数に取り込みましたので、adoRsオブジェクトを破棄します。
adoRsオブジェクトのCloseメソッドを使用し、閉じます。
adoRsオブジェクトを破棄します。

@8. データベース切断
サブプロシージャ：DB切断を実行し、アクセスDBと切断します。

@9. シートの描画を止める
Application.ScreenUpdatingをFalsに設定に設定します。

@10. 作業ブックを開く
レポートブックを開き、発注リストシートを変数に格納します。
このとき、作業シートに前回のデータが残っているので、削除用に最終行も取得します。

@11. 発注リストのリセット
発注リストの前回データを削除します。

@12. 発注リストへの書き込み
データを取り込んだ配列変数 aryOrderをResizeで発注リストに書き込みます。

@13. 書式設定
罫線は、線の種類と太さを設定しながら引きます。
標題を設定し、発注リストが完成です。

@14. 印刷設定
印刷範囲を指定し、プレビュー画面を表示させます。印刷範囲は、発注なしを表示させるため、wsOrderListRow + 1 とします。
また、Application.ScreenUpdatingもTrue設定に戻します。

@15. 終了フラグ
出力フォームを表示して、処理は終了です。

　

商品在庫画面の発注リスト出力部分ができました。
次回は商品在庫画面の棚卸集計表の出力部分を作成していきます。

MIFDUP

コメントを書く