作ってみた経緯
とある依頼書は各部から依頼内容が別紙でついてきたりして、1件の枚数が決まっていない。ひと通り作業が終わって関係者からのハンコを貰い終わったら1件分を1つのpdfファイルとして保存するが、あとでファイル名を見るだけで大体の内容が分かるようにタイトルをファイル名にしたい。けどタイトルをリネームするのがめんどくさい。スキャンしたら勝手に名前がタイトルになってて保存先に入ってくれたらいいのになー。というズボラ精神から自動化にチャレンジしてみました。計画的怠惰主義者なので。
ちなみにスキャナはEPSONのDS-780Nを使っています。
名刺サイズから読み取り可能で読み取り速度、読み込み機能のバリエーションなど何かと使い勝手がいいやつです。
手始めにOCRからタイトルを読み取る方法を試してみたもの、結局正しく認識されることが少なく、逆に手なおしの手間が掛かるという始末でした。。。さらには、申請書フォームがいじられた状態で提出されることもあったりしてOCR範囲がずれて読めなかったということもありました。
そこで目を付けたのがQRコード。DS-780Nであれば、バーコードが認識された用紙を境に別ファイルに仕訳する機能、更に、読み込んだバーコードの文字列をファイル名に使える機能もあります。これなら別紙が何枚あっても1枚目の依頼書本紙には必ずバーコードがある、という法則さえ守れば枚数にばらつきがある書類も一気にスキャンして1件ごとにファイルを仕分けできて、タイトルも自動で付いてくれるので、保存先さえ設定してしまえば、あとはスキャンを走らせるだけで仕事が終わる。
ああ、素敵。
ってことでこの作戦でいきたいと思います。
バーコードについて簡単に
一番よく見る、いろんな商品についているバーコードは1次元バーコードというものです。もっとも一般的な「JANコード」は数字しか表現できません。昔からある仕様なので致し方なしでしょう。
1次元バーコードで英数を混ぜるには別の仕様を使います。CODE39かCODE128が主流かと思います。
今回使用するQRコードは半角英数の他、全角文字にも対応しています。文字コードは「JIS X 0208」。SHIFT-JISでも行けるけど機種依存文字(環境依存文字)、一部記号は使用できません。人の名前や地名などを直接使うときは注意が必要かもしれません。
QRコードを生成する方法はいろいろある
Excelコントロール
MSOfficeに標準で装備されている機能にバーコードを生成するコントロールがあります。「Excel ORコード」で検索すると、結構な数の解説サイトが出てきます。但し、このコントロール、結構使用条件が厳しいです。
使用条件
- MicrosoftAccessが入ってないと使用できない
- 全角文字は使えない（全角が入ると表示が消える）
元々、Accessでバーコードを作る為のコントロールで、Excelでも流用できるよ！という代物なのでこれはしょうがないです。また、全角文字が表示されないのは仕様のようでMicrosoftからは対策案も出ていません。アメリカ生まれなので細かいところはアメリカ仕様のままなのでしょう。
更に、このコントロールをコピーして使うと、サイズが勝手に変わったり内容が更新されなかったり位置がずれたり挙動が怪しくなります。しかも挙動不審の発生がランダムなので、こうすればうまくいく、というのが提示できません。この辺りもMicrosoftからの回答はないので、こんなもんだとあきらめるしかないようです。
MicrosoftBarCode Control 16.0の使い方
- Excelの開発タブから「挿入」→「ActiveXコントロール」→コントロールの選択を選ぶ。
- 「MicrosoftBarCode Control 16.0」を選んでOKを押す
- 表示させたいところに四角の図形を書くようにドラッグアンドドロップすると1次元バーコードが表示される
- 1次元バーコード上で右クリック、「MicrosoftBarCode Control 16.0 オブジェクト」→「プロパティ」を選択
- スタイルを「QRコード」に変更する。
- 再度右クリックして「プロパティ」を選択
- 直接文字を埋め込むなら「Value」に埋め込む文字を打つ
- セルを参照させるなら「LinkedCell」に対象のセル番地を入力
開発タブの出し方はこちらを参考にしてください。
デンソー他API
QRコードはデンソーウェーブ（開発当時はデンソー）開発部門に所属していた原昌宏さんが昼休憩の時間中に社内で打っていた囲碁をヒントに開発したバーコードです。そのお膝元デンソーウェーブではもちろん作成用のSDKを販売しています。
しかし、何かのソフトウェアに組み込んで新システムを構築、とかで使用するものなので、一個人の作業時短に使うような代物ではないですのでここでは割愛します。
デンソー以外でもQRコード生成用のAPIやアドインを販売している企業はありますが、軒並み結構なお値段です。
Python
「VBAでだめならPythonで組めばいいじゃない！」ということで探してみたらありました。
PythonにはExcelをPython上でアレコレできる"openpyxl"という外部モジュールがあります。
VBが下火の今、Pythonをマクロの替わりに使おう!という動きも一部であり、結構いろんなコントロールが効くようです。
その他にも、QRコードを生成できる"QRCODE"という直球な外部モジュールもあります。これらと画像処理外部モジュール"Pillow"を組み合わせてQRコードを生成する方法が紹介されていました。
これで任意のQRコードの画像ファイルが作れましたが、今回の目的は「Excelシート内に貼る」だったので今回は不採用。
もちろんPythonの実行環境も準備する必要があるので、その辺りもハードルが高いです。
QRImage(.NETクラスライブラリ)+PowerShell
こちらのサイト様で学んだ方法です。
DLLを置いておけば、あとはPowerShellでスクリプトを走らせるだけなので環境の準備は比較的楽です。
但し、当然ですがPowerShellを使える必要はあります。コマンドプロンプトと違い、人によっては馴染みのない方法です。実行ポリシーをあらかじめ変更しておく必要があったりと、実行前の儀式もあったりします。そして多少なりスクリプトの内容を理解していないと応用が利かない等、敷居が高いと思います。
それと、Pythonと同じですが、今回の目的は「Excelシート内に貼る」だったので今回は不採用。
Excelシートだけで再現
Excelの条件付き書式と関数のみで作ったExcelシートをQiitaで公開している凄い人がいました。
これなら環境の準備が全くいりませんし、入力セルに入れたい文字が入れば即更新されます。正にこの挙動が狙っていたものでした。
が、スキャニングしてみたところ文字化けしてしまいました。
このExcelシートは文字コードをUTF-8を想定して作成されたようなので、JIS X 0208が想定されたバーコードリーダーでは文字化けしてしまうのです。QRコードの仕様書を見てJISに直そうと思いましたが、、、こんなの私のお頭じゃ無理です。
VBAのみでQRコードを生成
同じサイトでVBAで生成するソースコードも提示があって中身を見てみましたが、、、これをメンテナンスするとなるとかなりきっついです。更にこのプログラムの使用方法は、アドインを任意の場所に配置して参照設定で呼び出すことで他のExcelファイルでも使用できるような仕様になっていますが
- アドイン用の関数、書式を新たに覚える必要がある
- アドインを準備、設定しないと使用できない
ということで、ぱぱっとやるには敷居が高い内容でした。
Google Chart API
今般インターネットサイトでは多種多様なQRコード作成サービスサイトがありますが、組込で使い勝手が良いのが“Google Chart API”です。
GoogleのQRコード作成サービスAPIなのですが、URLでパラメータを送信すると生成された画像のみが表示されるというシンプルなサービスです。
このURLの画像をコピーして貼り付ければシート上に画像を表示できます。クリップボードに直接コピーして貼り付けるので、一度画像ファイルにする手間が省けますし、余計なファイルも生成されません。画像挿入のコードも".Pictures.Insert"の一文で済みますので組むのに楽です。
こちらのサイト様が今回の元ネタです。
デメリットとしては、インターネット接続が必要なことと、「Googleに情報を流すのはイヤ」という人にはおススメできないというところです。
私は情報を持っていかれることに抵抗はあるものの「どーせエシュロン状態になる」と思っているので必要最小限でGoogleさんとお付き合いしています。
ということで、Google Chart APIを使ったQRコード作成マクロを作ってみました。
以下のような簡単な表で、B列にQRコードにしたい文字列を入力すれば、上から順番にD列にQRコードを生成していく、という仕様です。画像のサイズは100×100固定にしていますが、"Size=100”の値を変更することで自由に大きさを変えられます。
Sub QRコード作成()
Dim i As Integer
Dim size As String
Dim URL0 As String
Dim URL1 As String
Dim URL2 As String
Dim URL3 As String
Dim URL4 As String
size = 100
i = 2
Do Until Cells(i, 1) = ""
'APIのURL
URL1 = "https://chart.apis.google.com/chart?cht=qr&chs="
'画像サイズの設定
URL2 = size & "x" & size
'文字コードを設定（SHIFT-JIS)
URL3 = "&choe=Shift_JIS&chl="
'変換する文字列を指定
URL4 = Cells(i, 2)
URL0 = URL1 & URL2 & URL3 & URL4
Cells(i, 3) = URL0
Cells(i, 4).Select
Sheets("Sheet1").Pictures.Insert URL0
Rows(i).RowHeight = 80
i = i + 1
Loop
End Sub私は申請書の仕訳スキャンとファイルの自動ネーミングに使用しましたが「好きなExcel上の文字列をQRコードでデータ化できる」という大雑把な機能なのでいろいろ応用が利くと思います。
URL4の指定先を複数セルにすればいろいろな組み合わせも作れますし、カンマ区切りで複数のデータを連結すればCSVのレコードとしても使えます。
