商品在庫画面の棚卸集計表出力 [VBA在庫管理システムVer02#18]

前回、商品在庫画面の発注リスト出力部分を作成しました。

この記事では、棚卸集計表の出力を解説します。

VBA在庫管理システムVer02

棚卸集計表の出力

VBA在庫管理システムVer01

Ver01では、帳票を出力するプロシージャをサブルーチンとして、標準モジュール 棚卸集計表に作成しました。

Sub ShowItemInventoryReport()

'インプットボックスを設定し、棚卸日を取得
Dim myMsg As String, myTitle As String, 棚卸日 As String
    myMsg = "棚卸日を入力してください" & vbCr & "(例:2023/01/01）"
    myTitle = "棚卸日入力"
    棚卸日 = Application.InputBox(prompt:=myMsg, Title:=myTitle, Default:=Format(Date, "yyyy/mm/dd"), Type:=2)

'インプットボックスの入力値を判定
If 棚卸日 = "False" Then
    Exit Sub
End If

    Application.ScreenUpdating = False

'棚卸集計データの更新---------------------------------------------------------------------------------------------------

'作業ブックを開く
Workbooks.Open データ位置 & "在庫管理DATA.xlsx"
    
'オブジェクト変数の取得
Dim wsItemMaster As Worksheet, wsItemInOut As Worksheet, wsStockExtraction As Worksheet
Set wsItemMaster = Workbooks("在庫管理DATA.xlsx").Sheets("M_商品")
Set wsItemInOut = Workbooks("在庫管理DATA.xlsx").Sheets("T_入出庫")
Set wsStockExtraction = Workbooks("在庫管理DATA.xlsx").Sheets("受払抽出")

'在庫更新
Dim Buf() As Variant
Dim i As Long

'最終行の取得
Dim wsItemMasterRow As Long
wsItemMasterRow = wsItemMaster.Cells(Rows.Count, 1).End(xlUp).Row

'変数の設定
ReDim Buf(wsItemMasterRow - 1, 4) As Variant

With wsStockExtraction
    For i = 0 To wsItemMasterRow - 2
    
        '抽出条件の設定
        .Activate
        .Cells(2, 1).Value = wsItemMaster.Cells(i + 2, 1).Value
        .Cells(2, 2).Value = ">" & wsItemMaster.Cells(i + 2, 11).Value
        
        '受払抽出
        wsItemInOut.Columns("A:H").AdvancedFilter _
         Action:=xlFilterCopy, _
         CriteriaRange:=.Range("A1:C2"), _
         CopyToRange:=.Range("F1:I1")
        
        '出庫済み数
        Buf(i, 0) = WorksheetFunction.SumIfs(Range("I:I"), .Range("H:H"), "　出庫", .Range("G:G"), "<" & 棚卸日)
        
         '出庫予定数
        Buf(i, 1) = WorksheetFunction.SumIfs(Range("I:I"), .Range("H:H"), "　出庫", .Range("G:G"), ">=" & 棚卸日)
    
        '入庫済み数
        Buf(i, 2) = WorksheetFunction.SumIfs(Range("I:I"), .Range("H:H"), "入庫", .Range("G:G"), "<=" & 棚卸日)
    
         '出庫予定数
        Buf(i, 3) = WorksheetFunction.SumIfs(Range("I:I"), .Range("H:H"), "入庫", .Range("G:G"), ">" & 棚卸日)
    
         '現在在庫
        Buf(i, 4) = wsItemMaster.Cells(i + 2, wsItemMasterColumns.M棚卸数) - Buf(i, 0) + Buf(i, 2)
     
    Next
End With

'製品マスタへの書き込み
wsItemMaster.Range("M2").Resize(UBound(Buf, 1) + 1, UBound(Buf, 2) + 1).Value = Buf


'棚卸集計データの取得---------------------------------------------------------------------------------------------------

'棚卸集計データの取り込み
Dim aryInventory() As Variant
Dim j As Long

'商品名称/棚位置を昇順で並べ替え
wsItemMaster.Range("A1").Sort key1:=wsItemMaster.Range("B1"), order1:=xlAscending, Header:=xlYes
wsItemMaster.Range("A1").Sort key1:=wsItemMaster.Range("J1"), order1:=xlAscending, Header:=xlYes

'発注リストがある時のみデータ取り込み
If wsItemMasterRow >= 2 Then

    '変数の設定
    ReDim aryInventory(wsItemMasterRow - 2, 6) As Variant
    j = 0
    
    '変数への取り込み
    For i = 2 To wsItemMasterRow
        If wsItemMaster.Cells(i, wsItemMasterColumns.M削除) = 0 Then
            aryInventory(j, 0) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品ID)
            aryInventory(j, 1) = wsItemMaster.Cells(i, wsItemMasterColumns.M商品名称)
            aryInventory(j, 2) = wsItemMaster.Cells(i, wsItemMasterColumns.M棚位置)
            aryInventory(j, 3) = wsItemMaster.Cells(i, wsItemMasterColumns.M棚卸数)
            aryInventory(j, 4) = "+" & wsItemMaster.Cells(i, wsItemMasterColumns.M入庫済み)
            aryInventory(j, 5) = "－" & wsItemMaster.Cells(i, wsItemMasterColumns.M出庫済み)
            aryInventory(j, 6) = wsItemMaster.Cells(i, wsItemMasterColumns.M現在在庫)
            j = j + 1
        End If
    Next

End If

'作業ブックを閉じる
Workbooks("在庫管理DATA.xlsx").Close savechanges:=False


'棚卸集計データの書込---------------------------------------------------------------------------------------------------
    
'作業ブックを開く
Workbooks.Open データ位置 & "在庫管理REPORT.xlsx"
    
'オブジェクト変数の取得
Dim wsInventoryList As Worksheet
Set wsInventoryList = Workbooks("在庫管理REPORT.xlsx").Sheets("棚卸集計表")

'最終行の取得
Dim wsInventoryListRow As Long
wsInventoryListRow = wsInventoryList.Cells(Rows.Count, 1).End(xlUp).Row

'棚卸集計表のリセット
If wsInventoryListRow > 4 Then
    wsInventoryList.Rows("5:" & wsInventoryListRow).Delete shift:=xlUp
End If

'棚卸集計表への書き込み
If wsItemMasterRow >= 2 Then
    wsInventoryList.Cells(5, 1).Resize(UBound(aryInventory, 1) + 1, UBound(aryInventory, 2) + 1).Value = aryInventory
End If

'棚卸日の書き込み
wsInventoryList.Cells(3, 8).Value = Format(棚卸日, "yyyy/mm/dd")

'最終行の再取得
wsInventoryListRow = wsInventoryList.Cells(Rows.Count, 1).End(xlUp).Row

'罫線の設定
With wsInventoryList.Range("D4:I" & wsInventoryListRow).Borders(xlInsideVertical)
    .LineStyle = xlDot
    .Weight = xlHairline
End With
With wsInventoryList.Range("D4:D" & wsInventoryListRow).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
End With
With wsInventoryList.Range("A4:I" & wsInventoryListRow).Borders(xlInsideHorizontal)
    .LineStyle = xlDot
    .Weight = xlHairline
End With

'ブックの最大化
ActiveWindow.WindowState = xlMaximized

'表題の作成
wsInventoryList.Cells(2, 1).Value = "● " & Format(Date, "yyyy/mm/dd") & "　棚卸集計表"

'印刷範囲の設定
wsInventoryList.PageSetup.PrintArea = wsInventoryList.Range("A1:I" & wsInventoryListRow).Address

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

    Application.ScreenUpdating = True

'出力フォームの表示
frmItemInventoryReport.show

End Sub

クエリの作成

棚卸集計表出力用クエリを作成していきます。
クエリの名称は、Q_棚卸集計表 とします。

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

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

＊ Q_棚卸集計表　デザインビュー

データシートビューを表示して、集計データを確認します。
＊ Q_棚卸集計表　データシートビュー

　　

棚卸集計表の出力

在庫リストを商品在庫リストボックスに表示させるプロシージャー：ShowItemInventoryReportを修正します。

Sub ShowItemInventoryReport()

'インプットボックスを設定し、棚卸日を取得
Dim myMsg As String, myTitle As String, 棚卸日 As String
    myMsg = "棚卸日を入力してください" & vbCr & "(例:2023/01/01）"
    myTitle = "棚卸日入力"
    棚卸日 = Application.InputBox(prompt:=myMsg, Title:=myTitle, Default:=Format(Date, "yyyy/mm/dd"), Type:=2)

'インプットボックスの入力値を判定
If 棚卸日 = "False" Then
    Exit Sub
End If

'棚卸集計データの取得---------------------------------------------------------------------------------------------------

'データベース接続
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 aryInventory() As Variant
Dim i As Long

'データ取り込み

'変数の設定
ReDim aryInventory(adoRs.RecordCount - 1, 9) As Variant
i = 0

'変数への取り込み
Do Until adoRs.EOF
    aryInventory(i, 0) = adoRs!商品ID
    aryInventory(i, 1) = adoRs!商品名称
    aryInventory(i, 2) = adoRs!棚位置
    aryInventory(i, 3) = adoRs!棚卸数
    aryInventory(i, 4) = "+" & adoRs!入庫済み数
    aryInventory(i, 5) = "－" & adoRs!出庫済み数
    aryInventory(i, 6) = adoRs!現在在庫数
    i = i + 1                    'カウンターを加算

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

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

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

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


'棚卸集計データの書込---------------------------------------------------------------------------------------------------
    
    Application.ScreenUpdating = False

'作業ブックを開く
Workbooks.Open データ位置 & "在庫管理REPORT.xlsx"
    
'オブジェクト変数の取得
Dim wsInventoryList As Worksheet
Set wsInventoryList = Workbooks("在庫管理REPORT.xlsx").Sheets("棚卸集計表")

'最終行の取得
Dim wsInventoryListRow As Long
wsInventoryListRow = wsInventoryList.Cells(Rows.Count, 1).End(xlUp).Row

'棚卸集計表のリセット
If wsInventoryListRow > 4 Then
    wsInventoryList.Rows("5:" & wsInventoryListRow).Delete shift:=xlUp
End If

'棚卸集計表への書き込み
With wsInventoryList
    .Cells(5, 1).Resize(UBound(aryInventory, 1) + 1, UBound(aryInventory, 2) + 1).Value = aryInventory
    .Cells(3, 8).Value = Format(棚卸日, "yyyy/mm/dd")
End With

'最終行の再取得
wsInventoryListRow = wsInventoryList.Cells(Rows.Count, 1).End(xlUp).Row

'罫線の設定
With wsInventoryList.Range("D4:I" & wsInventoryListRow).Borders(xlInsideVertical)
    .LineStyle = xlDot
    .Weight = xlHairline
End With
With wsInventoryList.Range("D4:D" & wsInventoryListRow).Borders(xlEdgeLeft)
    .LineStyle = xlContinuous
    .Weight = xlThin
End With
With wsInventoryList.Range("A4:I" & wsInventoryListRow).Borders(xlInsideHorizontal)
    .LineStyle = xlDot
    .Weight = xlHairline
End With

'ブックの最大化
ActiveWindow.WindowState = xlMaximized

'表題の作成
wsInventoryList.Activate
wsInventoryList.Cells(2, 1).Value = "● " & Format(Date, "yyyy/mm/dd") & "　棚卸集計表"

'印刷範囲の設定
wsInventoryList.PageSetup.PrintArea = wsInventoryList.Range("A1:I" & wsInventoryListRow).Address

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

    Application.ScreenUpdating = True

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

End Sub

@1. 棚卸日の取得
インプットボックスの機能を利用し、ユーザーに棚卸日を入力して貰います。

@2. 入力値の判定
インプットボックスのキャンセルが押された場合は、プロシージャーを抜けます。

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

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

@5. SQL文の変数への取り込み
データ抽出用のSQL文を作成し、文字列変数に格納します。
今回は、Q_棚卸集計表 の、全てのフィールドを抽出します。

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

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

@8. 配列変数への取り込み
Do_Until_Loop構文でadoRsオブジェクト内をループしながら、変数に取り込んでいきます。
Q_棚卸集計表の全てのフィールドを、配列変数に取り込みます。
入庫済み・出庫済み は、それぞれ±の表示にします。

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

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

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

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

@13. 棚卸集計表のリセット
棚卸集計表の前回データを削除します。

@14. 棚卸集計表への書き込み
データを取り込んだ配列変数 aryOrderをResizeで棚卸集計表に書き込みます。
また、棚卸日も書き込みます。

@15. 書式設定
罫線は、線の種類と太さを設定しながら引きます。
標題を設定し、棚卸集計表が完成です。

@16. 印刷設定
印刷範囲を指定し、プレビュー画面を表示させます。
また、Application.ScreenUpdatingもTrue設定に戻します。

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

商品在庫画面の棚卸集計表出力部分ができました。
次回は棚卸実行部分を作成します。