スポンサーリンク

マクロ(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月_給料」シート

に出力します。

こんな感じになります ↓

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

以下については、リンク先よりお願い致します。----------------------------------------------------------------
◆メルマガ登録
◆Youtube:税理士 松井元のチャンネル

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

全 VBA コード

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

VBA コード解説

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

従業員の情報

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

Image(3)

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

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

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

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

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

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

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

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

Image(4)

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

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

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

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