Excelのシートに入力されているデータをテキストファイルに出力する
従業員から特別徴収した住民税を各市町村に納付する以下のような業務を効率化したいという依頼が経理部門からありました。
- 納付する従業員毎の住民税を市町村別に合計する
- 合計した金額を市町村別に指定された振込伝票に仮記入する
- 仮記入した金額に間違いがなければ伝票を清書する
- 記入した伝票を金融機関の窓口に持参し振込を依頼する
効率化要件は
- 給与システムから出力した従業員別住民税額を納付する市町村単位に合計する
- 銀行の指定したテキストファイル定義に基づいて市町村別納付金額データを出力する
- 出力データを銀行のシステムにアップロードする
というものです。
実際のテキストファイル定義に基づけば、ヘッダーレコード、データレコード、トレーラーレコード、エンドレコードの4種類のレコードを120バイトの固定長データとして出力しなくてはなりませんでした。
ここではテキストファイルを出力する方法に絞って説明します。
Excelの操作だけでテキストデータを出力する方法
Excelに入力されているデータを行単位でテキスト出力するだけであれば、Excelの操作だけで可能です。
ファイル → 名前を付けて保存 → 保存フォルダー・ブック名を指定します。
そして、ポイントはファイルの種類の指定です。
通常はExcelブック(*.xlsx)で保存するところをテキスト(タブ区切り)(*.txt)、Unicode テキスト(*.txt)、テキスト(スペース区切り)(*.prn)のいずれかで実行すればテキストデータとして保存ができます。
但し、この方法では要件である120バイトの固定長で出力することは困難ですし、各フィールドごとの長さも保証されません。
VBAでExcelのデータをテキストファイルで出力する
実際のコーデイング
ここでは”個人情報”というシートにある名前（1列目）、生年月日（3列目 シリアル値）、年齢（4列目）の情報を名前（14文字）、年齢（3桁）、生年月日（8桁 YYYYMMDD）３つのフィールドを出力する場合の例を記載します。
Dim 住民税納付ファイル As Variant ← 出力するフォルダー＆ファイルを指定する変数
Dim データレコード(1 To 3) As Variant ← 3個のフィールドを出力する場合
Dim 出力フィールド数 As integer ← 出力フィールド数
Public output_record As Variant ← 出力するデータを格納する変数
Dim 入力件数 as integer ← 入力行数を指定する変数
Dim 出力件数 as integer ← 出力行数を指定する変数
住民税納付ファイル = “C:\住民税\個人住民税納付データ.TXT” ← 出力ファイル定義
Open 住民税納付ファイル For Output As #1 ← 出力ファイルを開く
’シートの内容を出力フィールドにセットする
For 入力件数 = 2 to Sheets(“個人情報”).UsedRange.Rows.Count
↑ ↑
タイトル行があると仮定 読込むデータが入っているシートの最終行を指定
データレコード(1) = Sheets(“個人情報”).cells(入力件数,1).value & _
space(14- len(Sheets(“個人情報”).cells(入力件数,1).value ))
↑
名前が14桁に満たない場合はその後ろにスペースを埋めて次のフィールドの開始位置を合わせる
データレコード(2) = format(Sheets(“個人情報”).cells(入力件数,4).value,”###”)
データレコード(3) = format(Sheets(“個人情報”).cells(入力件数,3).value,”yyyymmdd”)
For 出力フィールド数 = 1 To 3 ← 出力するフィールドの数
output_record = output_record & データレコード(出力フィールド数)
Next 出力フィールド数
Print #1, output_record
Next 入力件数
Close #1 ← 出力ファイルを閉じる
