Macro For Overall Transfer Zengin Record Format
はじめに
みなさんは「総合振込」というものをご存知ですか？経理をなさったことがある方ならご存知かもしれません。総合振込は一般に「総振（そうふり）」と呼ばれています。ふつうはATMから1件ずつ振込をしますが、総振は会社のパソコンなどから何件かの振込をまとめて処理します。つまり、インターネットバンキングを利用した企業向け振込サービスです。
「総合振込（全銀形式）レコードフォーマット準拠テキストファイル作成マクロ」は、その名のとおり、総合振込（全銀形式）レコードフォーマットに規定されたルールにしたがって、エクセル上の振込データをテキストファイルに変換するマクロです。
具体的には、1レコードあたり120バイトのデータで、ヘッダ・レコード、データレコード、トレーラー・レコード、エンド・レコードの4種類の異なるフォーマットのレコードを1ファイルにしたものです。
このマクロでは、各銀行独自のCSVフォーマットではなく、本来の全銀形式のテキストフォーマットを、エクセル上のデータから作成します。
説明のために、マクロファイルをアップロードしてあります。ここからダウンロードしてください。
総合振込（全銀形式）レコードフォーマット準拠テキストファイル作成マクロ
おことわり
・動作は無保証です。
・全銀形式のテキストフォーマットは共通でも、各銀行ごとにそれぞれ異なる固有の値を入力する部分がありますので、ご自身のお使いになっている銀行の総合振込（全銀形式）レコードフォーマットをかならずお調べください。本マクロは「三菱東京UFJ銀行のBizStation」準拠の値を入力している部分があります。
・本マクロで使用しているレコードフォーマットは、「三菱東京UFJ銀行のBizStationのヘルプ」に記載されているフォーマットです。
・本マクロには説明のための仮データがはいっております。仮データを用いて振込を実行してもエラーになりますので、仮データでの振込実行は絶対におやめください。
・本マクロで振込をする前に、十分テストをして正確なデータであることを確認したうえでご利用ください。
・各銀行のインターネットバンキングシステムに本テキストファイルを登録する方法は、ご自身でお調べください。
・本マクロは振込先を三菱東京UFJ銀行に限定したマクロです。
・本マクロは社員の経費振込に使用されることを想定しています。
ワークシートの解説
ワークシート全体の概要
「総合振込（全銀形式）レコードフォーマット」ワークシートはレコードフォーマットの内容を説明しています。ワークシートの設計・マクロの設計はすべてこのレコードフォーマットに従っています。常に手元に置いて確認することをおススメします。「支店コード」ワークシートには三菱東京UFJ銀行の全支店の支店のデータが掲載されています。「社員コード」ワークシートには振込先となる社員のデータが掲載されています。「部門コード」ワークシートには社員の所属部門のデータが掲載されています。「支払データ」ワークシートには支払の各明細のデータが掲載されています。最も重要なワークシートです。「全銀形式（データ)」ワークシートには上述の各ワークシートから集計されたデータがレコードフォーマットに従った書式で主に関数で掲載されています。「全銀形式（ソート)」ワークシートはマクロで使用します。主に集計されたデータの「値」が掲載されています。このシートを元にテキストファイルを作成します。
「総合振込（全銀形式）レコードフォーマット」ワークシート
レコードフォーマットの内容をファイル上で確認できるように作成しました。
ここにあるフォーマットに従ってファイルを作成しますので、熟読してください。
各項目の具体的な文字・数字の種類、文字数を確認しながら作業してください。
「支店コード」ワークシート
三菱東京UFJ銀行の各支店の店番・店名・店名（カナ）・カナ文字数の各項目があります。レコードフォーマットにしたがって店名（カナ）の文字数を30文字にしています。このシートの各項目を次の「社員データ」のワークシート上で処理する場合は、このシートは不要です。各銀行の全支店のデータを集めようとすると、膨大になりますので、ご注意ください。
「社員データ」ワークシート
社員番号・社員氏名・社員氏名（カナ）・銀行コード・銀行名（カナ）・支店番号・支店名（カナ）・預金種目・口座番号・社員名（カナ）の各項目があります。レコードフォーマットにしたがって社員氏名（カナ）の文字数を30文字にしています。その他の項目で文字数の調整が必要なものは別のワークシート上で調整しています。本マクロの仮データでは、振込先の口座は「三菱東京UFJ銀行」の口座に限定していますが、この社員データ上で各銀行の口座を登録しておけば、さまざまな銀行の口座に振り込むことができます。
「部門コード」ワークシート
部門コード・部門名称の各項目があります。経営管理上、各部門ごとに経費を集計する必要がある企業が多いと思います。そのための部門コードと部門名です。インターネット・バンキングのシステムからフィードバックされるデータに部門コードを登録しておくことができます。
「支払データ」ワークシート
「（データ）受付日」「振込（実行）日」「部門コード」「部門名」「社員番号」「社員名」「支払金額」「合計件数」「合計金額」の各項目があります。このワークシートは、簡易版の振込明細表になります。印刷して保存しておくなどの利用方法が考えられます。この中で実際に本マクロで利用するのは、「振込（実行）日」「部門コード」「社員番号」「支払金額」「合計件数」「合計金額」です。
「全銀形式（データ)」ワークシート
各項目（の文字数など）をレコードフォーマットに合わせて調整するワークシートです。基本的にこのシートがテキストファイルの大元になります。さまざなな関数を用いて調整しています。
「全銀形式（ソート)」ワークシート
「全銀形式（データ)」ワークシートのデータの「値」だけをコピペします。この「値」を元にテキストファイルを作成します。
レコードフォーマットの解説
できれば「総合振込（全銀形式）レコードフォーマット」を印刷して手元に置き、「全銀形式（データ)」を開いておくことをおススメします。
ヘッダレコードの解説
データ区分
数字1文字です。ヘッダレコードを意味する「1」をセルA1にセットします。
種別コード
数字2文字です。総合振込を意味する「21」をセルB1にセットします。
コード区分
数字1文字です。1.使用文字コード「JIS」を意味する「0」か、2.使用文字コード「EBCDIC」を意味する「1」か、3.「スペース」をセルC1にセットします。通常は「スペース」1文字でかまいません。
振込依頼人コード
数字10文字です。1.（BizStationの契約者ならば）契約者番号か、2.（企業コードを利用している場合は）企業コードか、3.「スペース」10文字をセルD1にセットします。
振込依頼人名
文字列40文字です。振込依頼人名を左詰めでセルE1にセット（残りはスペース）します。本マクロでは仮データとして、「=”ﾅｲｱｶﾞﾗ.ｴｷｽﾌﾟﾚｽ.ﾄﾚｰﾄﾞ(ｶ”&REPT(” “,18)」をセットしています。「”ﾅｲｱｶﾞﾗ.ｴｷｽﾌﾟﾚｽ.ﾄﾚｰﾄﾞ(ｶ”」が社名で「REPT(” “,18)」が残りのスペース18文字分です。
取組日
数字4文字です。振込指定日をセルF1にセットします。本マクロでは仮データして「=TEXT(支払データ!$B$2,”mmdd”)」をセットしています。「支払データ」ワークシートのセルB2の値「2016/10/05」をTEXT関数で「mmdd」という書式に変換しています。
仕向銀行番号
数字4文字です。ご自分のお使いになっているインターネットバンキングの銀行の銀行番号をセルG1にセットします。「BizStation」の場合は「三菱東京UFJ銀行」ですので、「0005」になります。
仕向銀行名
文字列15文字です。ご自分のお使いになっているインターネットバンキングの銀行の銀行名をセルH1にセットします。本マクロでは仮データとして「=”ﾐﾂﾋﾞｼﾄｳｷﾖｳUFJ”&REPT(” “,2)」をセットしています。
仕向支店番号
数字3文字です。ご自分のお使いになっているインターネットバンキングの銀行の支店番号をセルI1にセットします。本マクロでは仮データとして「001」をセットしています。
仕向支店名
文字列15文字です。ご自分のお使いになっているインターネットバンキングの銀行の支店名をセルJ1にセットします。本マクロでは仮データとして「=”ﾎﾝﾃﾝ”&REPT(” “, 11)」をセットしています。
預金種目（依頼人）
数字1文字です。1.普通預金を意味する「1」か、2.当座預金を意味する「2」をセルK1にセットします。本マクロでは仮データとして「1」をセットしています。
口座番号（依頼人）
数字7文字です。ご自分のお使いになっているインターネットバンキングの銀行の口座番号をセルL1にセットします。本マクロでは仮データとして「1234567」をセットしています。
ダミー
文字列7文字です。本マクロでは仮データとして「=REPT(” “,17)」をセットしています。
データレコードの解説
本マクロではデータ件数を20件としています。設定を変更すれば、好きな件数をデータ件数にすることができます。1行分のデータをコピーして残りの行に貼り付けます。
データ区分
数字1文字です。データレコードを意味する「2」をセルA4にセットします。
被仕向銀行番号
数字4文字です。振込先の銀行番号をセルB4にセットします。本マクロでは仮データとして「三菱東京UFJ銀行」の「0005」をセットしています。
被仕向銀行名
文字列15文字です。振込先の銀行名をセルC4にセットします。本マクロでは仮データとして「”ﾐﾂﾋﾞｼﾄｳｷﾖｳUFJ”&REPT(” “, 2)」をセットしています。
被仕向支店番号
数字3文字です。振込先の支店番頭をセルD4にセットします。本マクロでは仮データとして「INDEX(社員データ!$A$2:$I$11,MATCH($R4,社員データ!$A$2:$A$11,0),6)」をセットしています。「社員データ」ワークシートから該当の番号を抽出しています。
被仕向支店名
文字列15文字です。振込先の支店名をセルE4にセットします。本マクロでは仮データとして「INDEX(社員データ!$A$2:$I$11,MATCH($R4,社員データ!$A$2:$A$11,0),7)」をセットしています。「社員データ」ワークシートから該当の支店名を抽出しています。
手形交換所番号
数字4文字です。通常は「スペース」4文字をセルF4にセットします。本マクロでは仮データとして「REPT(” “,4)」をセットしています。
預金種目
数字1文字です。振込先の口座の預金種目をセルG4にセットします。本マクロでは仮データとして「INDEX(社員データ!$A$2:$I$11,MATCH($R4,社員データ!$A$2:$A$11,0),8)」をセットしています。「社員データ」ワークシートから該当の預金種目を抽出しています。
口座番号
数字7文字です。振込先の口座番号をセルH4にセットします。本マクロでは仮データ「TEXT(INDEX(社員データ!$A$2:$I$11,MATCH($R4,社員データ!$A$2:$A$11,0),9),”0000000″)」をセットしています。「社員データ」ワークシートから該当の口座番号を抽出しています。
受取人名
文字列30文字です。振込の受取人名をセルI4にセットします。本マクロでは仮データ「INDEX(社員データ!$A$2:$I$11,MATCH($R4,社員データ!$A$2:$A$11,0),3)」をセットしています。「社員データ」ワークシートから該当の受取人名（社員名（カナ））を抽出しています。
振込金額
数字10文字です。振込金額をセルJ4にセットします。本マクロでは仮データ「TEXT(支払データ!$D5,”0000000000″)」をセットしています。「支払データ」ワークシートから該当の金額を抽出しています。
新規コード
数字1文字です。「BizStation」では「0」固定です。仮データではその「0」をセルK4にセットしています。
顧客コード1
文字列10文字です。依頼人（このマクロを使おうとしている方）が定めた受取人識別のための顧客コードをセルL4にセットします。本マクロでは仮データとして「TEXT(支払データ!$B5,”0000000000″)」をセットしています。「支払データ」ワークシートから該当の顧客コード（社員番号）を抽出しています。また、作業用列のR列に「支払データ!$B5」をセットしています。
顧客コード2
文字列10文字です。依頼人（このマクロを使おうとしている方）が定めた受取人識別のための顧客コードをセルM4にセットします。本マクロでは仮データとして「TEXT(支払データ!$C$2,”0000000000″)」をセットしています。「支払データ」ワークシートから該当の顧客コード（部門コード）を抽出しています。この「顧客コード2（数字のみ）」をセットする場合は、後述する「識別表示」は「X」をセットします。
振込指定区分
数字1文字です。「BizStation」ではテレ振込を意味する「7」をセルN4にセットします。
識別表示
文字列1文字です。「BizStation」では前述した顧客コード2をセットする場合は「X」を、セットしない場合は「スペース」をセルO4にセットします。また、説明は省略しますが、「EDI情報」をセットする場合は「Y」をセットします。
ダミー
文字列7文字です。「BizStation」では「スペース」をセルP4にセットします。本マクロでは「REPT(” “,7)」セットしています。
数式のコピー
セルA4～セルR4の数式をセルA5～セルR23にコピーして本マクロを利用する前準備とします。
トレーラーレコードの解説
データ区分
数字1文字です。トレーラーレコードを意味する「8」をセルA2にセットします。
合計件数
数字6文字です。合計件数をセルB2にセットします。本マクロでは「TEXT(支払データ!D26,”000000″)」をセットしています。
合計金額
数字12文字です。合計金額をセルC2にセットします。本マクロでは「TEXT(支払データ!D27,”000000000000″)」をセットしています。
ダミー
文字列101文字です。「BizStation」では「スペース」をセルD2にセットします。本マクロでは「REPT(” “,101)」をセットしています。
エンドレコードの解説
データ区分
数字1文字です。エンドレコードを意味する「9」をセルA3にセットします。
ダミー
文字列119文字です。「BizStation」では「スペース」をセルB3にセットします。本マクロでは「REPT(” “,119)」をセットしています。
マクロの解説
ソースコード
Option Explicit
'総合振込（全銀形式）レコードフォーマット
'テキストファイル作成マクロ（BizStation準拠）
Sub MakeZenginTXT()
'合計件数の取得
Dim cnt As Long, Payment As String
Payment = "支払データ"
cnt = Worksheets(Payment).Range("D26").Value '合計件数
'データを別シートに転記
Dim Datasheet As String
Dim Datasort As String
Datasheet = "全銀形式（データ)"
Datasort = "全銀形式（ソート)"
Call S_TranscribeData(Datasheet, Datasort, cnt)
'データを並べ替え
Call S_SortData(Datasort, cnt)
'セルのデータを配列に格納
Dim vData As Variant
Call S_StoreData(vData, Datasort, cnt)
'配列の要素を文字列変数に格納
Dim tData As String
tData = F_StoreTXT(vData)
'TXTファイルの書き出し
Dim dt As String, amt As Long
With Worksheets(Payment)
dt = Format(.Range("B2").Value, "mmdd") '振込日
amt = .Range("D27").Value '合計金額
End With
Call S_PutTXT(tData, dt, cnt, amt)
End Sub
Sub S_TranscribeData _
(ByVal wsCopy, ByVal wsPaste, ByVal c As Long)
With Worksheets(wsPaste)
.Range(.Cells(1, 1), .Cells(c + 3, 16)).ClearContents
End With
With Worksheets(wsCopy)
.Range(.Cells(1, 1), .Cells(c + 3, 16)).Copy
End With
With Worksheets(wsPaste)
.Range(.Cells(1, 1), .Cells(c + 3, 16)) _
.PasteSpecial Paste:=xlPasteValues
End With
End Sub
Sub S_SortData(ByVal ws As String, ByVal r As Long)
With Worksheets(ws)
.Cells(1, 17).Value = 1
.Cells(1, 17).AutoFill _
Destination:=.Range(.Cells(1, 17), Cells(r + 3, 17)), _
Type:=xlLinearTrend
With .Sort
With .SortFields
.Clear
.Add Key:=Range(Cells(1, 1), Cells(r + 3, 1)), _
Order:=xlAscending
.Add Key:=Range(Cells(1, 17), Cells(r + 3, 17)), _
Order:=xlAscending
End With
.SetRange Range(Cells(1, 1), Cells(r + 3, 17))
.Header = xlNo
.Apply
End With
End With
End Sub
Sub S_StoreData _
(ByRef vData As Variant, ByVal ws As String, _
ByVal c As Long)
With Worksheets(ws)
vData = .Range(.Cells(1, 1), Cells(c + 3, 16))
End With
End Sub
Function F_StoreTXT(ByVal vData As Variant) As String
Dim i As Long, j As Long, s As String
For i = LBound(vData, 1) To UBound(vData, 1)
For j = LBound(vData, 2) To UBound(vData, 2)
s = s & vData(i, j)
Next j
Next i
F_StoreTXT = s
End Function
Sub S_PutTXT _
(ByVal s As String, ByVal d As String, _
ByVal c As Long, ByVal a As Long)
Dim t As String
t = ThisWorkbook.Path & "\" & "全銀形式" & "_" & _
d & "_" & c & "_" & a & "_" & _
Format(Now, "YYYY-MM-DD_HH-mm-SS") & ".txt"
Open t For Output As #1
Print #1, s;
Close #1
End Sub
ソースコードの概要
まず合計件数を取得します。何件分の処理をするのかを確定します。「全銀形式（データ)」ワークシートをコピーして、「全銀形式（ソート)」ワークシートに値を貼り付けます。この貼り付けた値がテキストファイルにセットされるデータになります。この値のデータを、エクセルの機能をVBAから利用して並べ替えます。並べ替えた値のデータは高速に扱うためにいったん配列に格納します。その配列に格納された値のデータを二重の「For ～ Next」を回しながら連結し、文字列変数に格納します。最後に文字列変数をテキストファイルに書き出して終了です。
メイン処理の解説
メイン処理は、変数宣言とサブルーチンの呼び出しのみに特化しています。3つのサブルーチンと1つの関数の呼び出しをおこない、全体の処理を終了しています。
各サブルーチンの解説
S_TranscribeDataの解説
これは「全銀形式（データ)」ワークシートのデータをコピーして、「全銀形式（ソート)」ワークシートに値の貼り付けをおこなうサブルーチンです。 「TranscribeData」とは「データの転記」という意味です。転記する前に「全銀形式（ソート)」ワークシートの値をすべてクリアしています。このマクロが何回も利用されることを想定してこの処理を追加しています。この処理は「全銀形式（データ)」ワークシートが作業の要であることから、このデータそのものを並べ替えては次の利用時に影響が出るので、いったん「値専用」のシートに転記することを考えました。
S_SortDataの解説
これは「値専用」のシートのデータをエクセルの機能を利用して並べ替えるサブルーチンです。セルQ1に「1」を代入して、オートフィルを使ってすべてのデータに番号を振っています。オートフィルのオプションで「Type:=xlLinearTrend」を使っていますが、これは「連続データ」を付与するときに使うオプションです。A列のデータ区分の数値とQ列の連続データの数値を利用して並べ替えます。
今回はエクセルの機能を利用して並べ替えていますが、並べ替え（ソート）は奥が深いです。はじめはいわゆる「バブルソート」や「クイックソート」などを利用しようとしましたが、大量のデータを扱うときはやはりエクセルの機能を利用した方が高速化できるので、そちらにしました。
S_StoreDataの解説
これはセルのデータを配列に格納（Store）するサブルーチンです。バリアント型変数のvDataに「値専用」シートのデータを一括で代入します。これはマクロを高速化する有力な手段のひとつです。
F_StoreTXTの解説
これは配列の要素を文字列変数に格納する関数です。「For ～ Next」を二重に回して、配列の各要素を連結しながら文字列変数に代入していきます。
S_PutTXTの解説
これは文字列変数に格納された「値」データをテキストファイルに書き込むサブルーチンです。テキストファイル名は「全銀形式」＋「振込日」＋「合計件数」＋「合計金額」＋「年月日」＋「時分秒」という形式になっています。「Print #1, s;」というふうに「Print」コマンドの最後に「セミコロン（;）」が付いていますが、「セミコロン」があると、ファイルの最後尾に改行マーク（Enter）を付与しません。
おわりに
このファイルを活用してくださると大変うれしいです。
コメントを残す