当Webサイトではたびたびバーコードネタを扱っておりますが、ついにその集大成を完成させるときがやってきました。バーコードを用いた在庫管理システムです。
言うまでもなくVBAにはそんな力はないので、PythonがメインでExcelとVBAはオマケです。Pythonをインストールしていない方は今すぐしてください。
システム概要
基本形は以前のネタで使用した、PythonでPCカメラを使ってバーコードを読み取り、結果をテキストへ出力し、それをVBAでExcelシートに取り込むとというものです。
概要図は次のようになります。
詳しくはこちらをご覧ください。
世にはバーコードリーダーという、バーコードの読み取りを専業とする機材があり、PCにつないで使用することもできますが、あまりオススメはしません。何故かというと、読み取りデータがキーボード入力として送出されるので使い勝手がすこぶる悪いからです。
たとえば、Excelシートにデータを取り込みたくても読み取った瞬間に別のウィンドウがアクティブになってしまうと、そっちにデータが持っていかれます。Pythonでやれば、PCにカメラが付いていれば特別な機材を用意することなく読み取れます。データの取り回しも簡単です。
で、そのPythonのシステムの動作にはopencv-pythonとpyzbarモジュールが必要です。インストールしましょう。
pip install opencv-python
pip install pyzbarExcelシートは次のような構成にします。
モードは入庫と出庫があり、読み取ったバーコードからJANコードを特定して在庫数に足すまたは引きます。下限はこれ以下に在庫数が減るとアラートを出すための閾値です。
VBA解説
VBAのコードは先に紹介した別記事をベースに、少し改良して次のようにしました。
Option Explicit
Private Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
Private Declare PtrSafe Function BeepAPI Lib "kernel32" Alias "Beep" (ByVal dwFreq As Long, ByVal dwDuration As Long) As Long
Const BUF_FILE_PATH As String = "C:\expy\buf.txt"
Const PY_SCRIPT_PATH As String = "C:\expy\jan_code_reader.py"
Public tm As Date
Sub importText()
Dim buf As String
Dim barcodes() As Double
If Not Dir(BUF_FILE_PATH) = "" Then
Open BUF_FILE_PATH For Input As #1
Dim i As Long
i = 0
Do Until EOF(1)
Line Input #1, buf
ReDim Preserve barcodes(i)
barcodes(i) = buf
i = i + 1
Loop
Close #1
Call stockCounter(barcodes)
Kill BUF_FILE_PATH
End If
tm = Now + TimeValue("00:00:05")
Application.OnTime tm, "importText"
End Sub
Sub stopImportText()
On Error Resume Next
Application.OnTime tm, "importText", Schedule:=False
End Sub
Sub executePy()
Dim wss As Object
Set wss = CreateObject("WScript.Shell")
Dim cmd As String
cmd = "python " & PY_SCRIPT_PATH
wss.Run cmd, 0, False
End Sub
Sub stockCounter(barcodes() As Double)
Dim mode As String
mode = Range("e2").Value
Dim stock As Long
Dim lower As Long
Dim pName As String
Dim jan
Dim tb As ListObject
Set tb = Worksheets(1).ListObjects(1)
For Each jan In barcodes
On Error GoTo errhandle
Dim row As Long
row = WorksheetFunction.Match(jan, tb.ListColumns("JANコード").Range, 0)
lower = tb.ListColumns("下限").Range(row).Value
stock = tb.ListColumns("在庫").Range(row).Value
pName = tb.ListColumns("商品名").Range(row).Value
With tb.ListColumns("在庫").Range(row)
If mode = "出庫" Then
.Value = stock - 1
If stock - 1 <= lower Then
Call lineNotify(pName & "がなくなるぞ！")
End If
Else
.Value = stock + 1
End If
.Interior.ColorIndex = 6
Sleep 300
.Interior.ColorIndex = 0
End With
Next
Exit Sub
errhandle:
BeepAPI 200, 300
End Sub
Sub lineNotify(message As String)
Const TOKEN As String = "*************************************"
Dim http As Object
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "POST", "https://notify-api.line.me/api/notify", False
http.setRequestHeader "Authorization", "Bearer " & TOKEN
http.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
http.Send "message=" & message
End Sub
Sub main()
Call importText
Call executePy
End SubimportTextプロシージャは、Pythonがカメラから読み取ったバーコードのデータを書き出したテキストファイルを読み取ります。Application.OnTimeで再帰的に自身を呼ぶことで周期的に実行されます。インターバルは5秒にしています。読み込みが終わったテキストファイルは削除します。
このプロシージャによりテキストファイルから読み込まれたバーコードデータはstockCounterプロシージャへ渡されます。
stockCounterはまず受け取ったバーコードデータをシートへ展開します。シートへの展開はWorksheetFunction.Matchでバーコードを検索して対象行を特定し、その行データを取得してから在庫列をモードに応じて増減させます。
sleepはどこの在庫数が変更されたのかをわかりやすくするためセルの色を変えて、すぐ戻すという処理で使っています。
私はいつもCellsでセルを指定するのですが、今回はテーブルを使ってListObjectsから指定してみました。が、ワークシート関数ではテーブルはめちゃ便利ですが、VBAからだと微妙ですね。列名がハードコーディングになっちゃうのがイケてない感じです。もっといいやり方があるのかな。
続いて、取得した行データを使って在庫数が下限を下回った場合、lineNotifyプロシージャに商品名を渡してコールします。lineNotifyではLINEにメッセージを送るAPIをコールします。次の記事に詳しくあります。
これで在庫切れしそうな商品を補充するようにアラートを出します。
WorksheetFunction.Matchを使っているので、テーブルにないバーコードデータを読むとコケるのでエラーハンドラーで回避します。WindowsAPIでBeep音を鳴らせるらしいので、ググって出てきたページから拝借したコードをハンドラーとして入れておきます。
executePyでPythonスクリプトを非同期実行し、カメラ制御とバーコードのデコードをした結果をテキストファイルにします。詳しくは次節で。
stopImportTextでimportTextの再帰処理を止めます。このプロシージャはブックのクローズイベントから呼んでいます。
mainプロシージャをシートのボタンに設定して完了です。
Python解説
Pythonスクリプトは次のようになります。 jan_code_reader.pyという名前でVBAで指定したパスへ配置します。
import threading
import cv2
import winsound
from time import sleep
from pyzbar.pyzbar import decode
def is_jan(code):
return (len(code) == 13 or len(code) == 8) and (code[:2] == '49' or code[:2] == '45')
def scan_wait():
global can_scan
can_scan = False
winsound.Beep(2500, 200)
sleep(2)
can_scan = True
BUF_FILE_PATH = r'C:\expy\buf.txt'
WIN_NAME = 'JAN CODE READER'
can_scan = True
cap = cv2.VideoCapture(0)
cap.set(cv2.CAP_PROP_FRAME_WIDTH, 640)
cap.set(cv2.CAP_PROP_FRAME_HEIGHT, 480)
cv2.namedWindow(WIN_NAME, cv2.WINDOW_AUTOSIZE)
while cap.isOpened():
ret, frame = cap.read()
if ret:
d = decode(frame)
if d and can_scan:
for barcode in d:
barcode_data = barcode.data.decode('utf-8')
if is_jan(barcode_data):
threading.Thread(target=scan_wait).start()
with open(BUF_FILE_PATH, mode='a',
encoding='shift-jis',
newline='') as buf:
buf.write(barcode_data + '\r\n')
cv2.imshow(WIN_NAME, frame)
if cv2.waitKey(1) & 0xFF == ord('q'):
break
if not cv2.getWindowProperty(WIN_NAME, cv2.WND_PROP_VISIBLE):
break
cap.release()基本は別記事と同じで、OpenCV-Pythonを使ったカメラ制御の基本形にバーコード読み取りとファイル書き出しを追加したものです。
scan_waitはpyzbarが超優秀なためカメラに写っている間バーコードを連続して読み取ってしまうので、一度読み取ったあとにしばらく読み取り無効時間を作るための関数です。
バーコードを読み取るとスレッドとしてscan_waitが走ります。can_scanは読み取り可否のフラグで、これをまずFalseにします。ビープ音をならしたらsleepで2秒まったあとTrueへ戻します。
カメラキャプチャのループではcan_scanがTrueでバーコード読み取り処理をさせることで連続読み込みを防止します。
どこかでスレッドからグローバルな変数をさわるのは良くないみたいなことを聞いた気がするのですが、問題なくうごいているので気にしない。
あとPythonのthreadingは厳密には並列処理ではなく並行処理だとあとから知ったけど、特に問題なくうごいているので気にしない。
is_janは読み取った値がJANコードであるかを超簡易にチェックしています。
can_scanとis_janがTrueの場合に読み取った値をテキストファイルに書き込みします。追記モードで開くので、もしVBA側でファイルが消してあれば新たに作成されます。VBA側で読み込み削除、Python側で書き込み作成することでテキストファイルを介してデータ連携を実現しています。
うごかしてみる
余談ですが最近手に入れたノートPCのVAIO最高スペック30万円の内蔵カメラは控えめに言っても「おもちゃ」なのでバーコードリーダーとしては使い物になりません（いくらインカメラとはいっても、これで顔認証していいのかよってレベルです･･･）。
なので、これまた最近手に入れたSurfaceGo3でうごかしてみます。価格はVAIOの1/3ですがカメラ性能はVAIOと比較にならない高画質です。これだったらSurfaceの2in1で最強スペックを買えば良かったと思い始めております･･･。
メイン画面です。
リーダー起動ボタンでカメラが起動します。
バーコードを写すと、該当商品の在庫数が減ります。
在庫数が下限と同じになったのでLINEにアラートが飛びます。
これで食料を買い忘れて干し芋をかじって飢えをしのぐことがなくなり安心です。
モードを入庫に切り換えるとバーコード読み取りで在庫を増やしていきます。
デモ動画
実際のうごきを動画にしました。音がでます。
反映までに時間がかかっているのは5秒インターバルで読みにいっているので、バーコードを写すタイミングで最大5秒は待つからです。試していませんが、あまり速くしすぎると安定しなくなると思います。
まとめ
実際にはいちいち食った後にバーコードを読んで在庫管理なんかしてられねーので、こんな使い方は完全にネタですが、リアルなシチュエーションとしては備品を使う部署と発注する部署が離れていて、その間の情報共有とかでしょうか。
最近スマートホーム化にはまっていて、Googleアシスタントの便利さ（と世界を支配するGoogleの力）を知ったので、アラートをLINEに送るかわりにGoogleアシスタントにしゃべらせられないか模索しております。
もはやこのサイトのお約束ですが、これをExcel-VBAでやる意義はありません。ていうかむしろデメリットしかありません。ですがExcelの呪縛に囚われ続ける我らVBAerは、やらずにはいられません。
次はPythonだけで超絶スマートに同じことをやりたいと思います。
おわり。
