スポンサーリンク

マクロ(VBA) を使って税務ソフトの CSV データを Excel の表にする方法、JDL 年末調整システムの場合


当たり前のことですが、会計・税務ソフトは仕様が決まっています。

UI がもっと見やすくならないかな? と思うこともありますが、自分で作るものではどうしようもありません。

与えられたものを使う他ありません。

会計・税務ソフトのここはどうなのかなぁ? と思う点を補うために Excel が使えます。

会計・税務ソフトのデータを CSV出力して、それを Excel ファイルに取り込むというやり方です。

そして、Excel ファイル上でいじったデータを CSV出力して会計・税務ソフトに戻すこともできます。

Image

さて、今日は、JDL の年末調整システムから出力した CSVデータを Excel に取り込んだ後に、データの加工するマクロ(Excel VBA)を紹介します。

スポンサーリンク

1、JDL 年末調整システムとExcel でデータを行き来する仕組み

まずは使っている仕組みの全体像をざっくりお話しします。

私がいる事務所では、顧問先の年末調整の計算でのために JDL 年末調整システムを使っています。

そして、7月と1月の源泉所得税の納期特例の時期に、顧問先の月々の給与の合計額を計算して納付書を作成しています(1月は年末調整の対応)。

さて、月々の給与は Excel の表にしてプリントアウトして保管しています。

(紙で保管する必要があるかどうかという議論がありますが、まぁここは昔からのやり方で現状はそのようにしているということです。)

年末調整システムと Excel シートの両方に給料等の同じ入力を2回行うのは面倒です。

ですので、給料等のデータ入力はどちらか一方だけで行って、無理やりデータを連動しています。

マクロ(Excel VBA と UWSC)を使って、そのような仕組みを作っています。

Image[1]

UWSCというのは RPA のように画面操作をすることができるツールです。

どのような仕組みか動画を作ってお話ししましたのでどうぞ!

Excel シート内でのデータの加工は VBA で行い

年末調整システムでの CSV データの入出力の操作は UWSC を使っています。

2、CSVデータを Excel に取り込んだ後に、データの並び替えをするマクロ(Excel VBA)

さて、今日のメインに行きますね!

上記のような仕組みを使っているわけですが、今日詳しくお話しするのは、その中の一部です。

年末調整システムから CSV 出力したデータは、Excel の「JDLからの読み込み」シートに取り込みます。

Image(1)

そして、この Excel ファイルに取り込んだデータを加工して

●「1〜6月_給料」シート
●「7〜12月_給料」シート

に出力します。

こんな感じになります ↓

年末調整システムから出力したままのデータでは、こちらが見やすい状態にはなっていないので加工して、他のシートに移す必要があるんです。

以下については、リンク先にある個別のフォームよりお願い致します。-----------------------
◆メルマガ登録

今日は、この取り込んだデータを加工して並び替える VBAコードを紹介します。

全 VBA コード

全 VBA コードは以下のようになります。ちょい長めです。

VBA コード解説

さて、コードの主な部分を解説をしていきます。

従業員の情報

Excel シート上で表示できる従業員の人数は最大10人としています。

Image(3)

1~12月の各月ごとに、各従業員の以下のデータを、

「JDLからの読み込み」シートから読み出して、配列(データ数10個)に入れています。

・総支給額
・社会保険料
・源泉所得税

年末調整システムのデータ項目の列番号取得

年末調整システムから出力したままのデータは各項目が横一列に並べられいます。

例えば以下のような項目です。

・給料1月 支給年月日
・給料1月 総支給額
・給料1月 社会保険
・給料1月 源泉税額

そして、項目ごとの従業員のデータが行の上から順番に並べられています。

Image(4)

必要なデータがどの列にあるか、For 文を使って列番号を検索しています。

さて、なぜ検索するようにしているかと言うと、年末調整システムの仕様変更でデータの並びが変わることがあるので、

列番号を数字で指定すると、仕様変更の度にコードを変更する必要が出てきてしまうからです。

それでは汎用性がないので、コード変更の必要が無いようにしました。

従業員数の確認

For  文を使って従業員数をカウントしています。

1列目の上から順番に従業員の番号を読んで行き、〇〇という文字が出てきたらその行より下はいないことになるので、それを使って人数を把握しています。

従業員数ごとのデータを配列に入れ込む

For 文を使って、支給日と各従業員ごとの以下のデータを配列に入れ込んでいます。

・総支給額
・社会保険料
・源泉所得税

ポイントは列番号を変数で指定いていることです。