こんにちは、デスクワークラボの吉井良平です。
とある会社から、エクセルでリスト形式で作ってある振込データを、全銀フォーマットに変換するお仕事をご依頼いただきました。
ネット上で、全銀フォーマットに変換する無料ツールもありましたが、やっぱり「自社で作っているリストから、そのまま全銀フォーマットで振込データを作りたい」という希望もあると思いますので、VBAで作る方法を紹介しますね。
使用するサンプルファイル
作り方を説明する際にサンプルが必要なので、サンプルのエクセルファイルを掲示しておきます。
※ここでダウンロードできるファイルは、プログラムのついていないただのフォーマットです。完成版をダウンロードしたい方は、一番最後までスクロールした場所にあるダウンロードボタンからお願いします。
3,000円でダウンロードできます。
メニューシート
振込依頼主の情報を記入するシートです。
ここに、依頼主（ほとんどは自社）の委託者コード、口座情報を入力しておくためのシートです。
大体の銀行のシステムでは、コードからマスター参照されるはずなので、カナの部分は間違ってても問題ないはずですが、自分で間違えてしまわないよう、やっぱりきちんと入力しておきましょう＾＾
一番下には、金額を入力するシートを作成するためのボタン（図形）を配置しています。
口座一覧シート
振込口座の一覧を事前に入力しておくためのシートです。
支払先マスタ、という感じです。
事前に、ここに振込先の口座番号等を入力しておきましょう。
ここも、番号さえ合っていればカナ名称は銀行側のシステムで上書きされるはずなので、番号以外は厳密に入力しておかなくても大丈夫なはずですが、念のために正式な名称で入力しておきましょう。
出力用シート
これは中身は空ですが、全銀フォーマットをテキスト出力する前の、仮置き用のシートです。
まぁ無くても作れるのですが、あった方がプログラムが簡単になるので、今回は仮置き用のシートを作っています。
では、さっそくVBAのプログラミングをやっていきましょう！
金額の入力シートを作成する
まずは、口座一覧のシートをコピーして、振込金額を入力できる欄を作ります。
これだけでも用は足せますが、見栄えの調整や、シートが増えた時に判別できるようシートの名前を変更してみましょう。
ちょっと長くなりましたが、普段使っていくためには使い勝手が大事なので、面倒くさがらずにこのぐらいはやっておいた方が良いです。
ここまでできたら、メニューシートの「入力用シート作成」ボタンに、今作ったマクロを貼り付けておきます。
これでボタンを押したら、入力用シートができる部分が出来上がりました。
全銀フォーマットの作成
ここからは、全銀フォーマットを作っていく部分です。
全銀フォーマットとは、という説明は、この記事にたどり着いた方へは不要かとは思いますが、日本全国の金融機関のシステムで共通して使えるようにした、データ形式のことです。
テキスト形式で、120桁の固定長データです。
| ヘッダーレコード | 振込依頼の情報をセットする部分 |
| データレコード | 振込先の情報をセットする部分 |
| トレーラレコード | 振込の合計をセットする部分 |
| エンドレコード | 終了を宣言する部分 |
に分かれています。
では、さっそく作っていきましょう。
ヘッダーレコードの作成
全銀フォーマットでデータを作っていく際、シート間でデータをやり取りするので、いちいちシート名をプログラムに書くのは面倒なので、まずはシートを変数に格納しています。
出力用のシートは、データを作る前にまっさらになっている必要があるので、
WS3.Cells.Clear
を最初に書くようにします。（画像に追加し忘れ・・・最後にダウンロードしたファイルに今回作ったVBAが入っているので、そちらで確認していただけたら。）
あとは、データ区分ごとに変数を作って、変数に入れていくだけです。
※固定の値もあるので、変数にわざわざ入れなくても良いものもあったり、変数の使いまわしができるものもありますが、分かりやすい方が良いかと思い、ほとんど変数にしています。
一応書いておきますが、依頼人コードは事前に金融機関に申請して、取得しておく必要があります。
Dim MyAccountKana As String
MyAccountKana = StrConv(WS2.Range(“D5”), vbNarrow)
スクショ忘れなので後から付け足していますが、全銀データは半角しか受け付けないので、全角で入力される可能性もあって、vbNarrowで半角に変換する工程を入れています。
ここが少し難しいですが、依頼人名は40桁と決まっています。40桁ある会社名はほぼ無いと思いますので、40桁になるまでDo～Loopでスペースを埋めていきます。
全銀フォーマットに変換する際は、頻繁に出てくる処理です。
この要領で、振込日や金融機関番号、金融機関名も変数に入れていきます。
そういえば、いくつかVariant型で変数を指定していますが、数字の頭に0を埋めたりして、型を考えるのが面倒なのでVariant型にしています。
実際のところはそんなものです。。
最後に、出力用シートにすべての変数を組み合わせたものを貼り付けます。
これでヘッダーレコードが完成しました。
データレコードの作成
続いて、データレコードを作っていきます。
一覧の最初から最後までを一つずつチェックして、金額が入っていたら全銀フォーマット形式で書き出し、というような処理になります。
あとは、ヘッダーで行ったような処理と同じことをやっていきます。（変数の宣言は、長くなるので画像を省略しています）
最初は面倒ですが、フォーマットに従ってデータを変換していくだけですので、根気強くやっていきましょう。
次のトレーラレコードで、合計件数と合計金額を出力する必要があるため、ここでTtlAmount、TtlCountという変数で、累計の件数、金額を取得しています。
これでデータレコードは完成したので、トレーラレコードを作っていきましょう。
トレーラレコードの作成
トレーラレコードで注意するのは、エクセルの性質上、「数字＋スペース」は数字として認識されてしまうので、いったん書式設定を文字列にしておく部分です。
その他は、これまで使ったテクニックで対応可能です。
エンドレコードの作成
エンドレコードは、データ区分が9で、あとは119桁空白です。
エンドレコードも、データの書式設定を文字列にしておく必要があります。
テキストデータの出力
全銀フォーマットは、固定長のテキストファイルです。
「出力用」シートに仮置きしてある全銀フォーマットのデータを、テキストファイルに書き出していきます。
これで、完成です。
完成した全銀フォーマット変換ファイル
この記事の趣旨は作り方の説明ということで、「作り方は良いから完成版が欲しい」という方には、完成した全銀ファイルシステムは、以下からご購入ください。
販売価格は、3,000円です。
Paypalでのお支払いになりますので、振込をご希望の場合は、お問合せフォームから社名をご記入のうえ「全銀ファイル購入希望」とご連絡ください。請求書を送付させていただきます。
ということで、全銀ファイルの出力ができるようになりました。
それぞれの会社の実務に合わせて、活用していただければと思います。
