e-Gov電子申請の読み込み用ファイルをVBAで作る

#e-Gov用のxmlファイルを作成する仕組みを作る
電子政府総合窓口であるe-Govの電子申請では、作成途中のファイルなどを保存するほか、外部APIを提供しているので労務管理のソフトなどからは直接申請を行うことができます。

ただ、会社で使ってるソフトに搭載される機能から連携するとシングルチェックになってしまい精度が怪しくなったり、マイナンバーの取り扱い上で問題がありました。（紙面で残すなとかね）
かといってWEB上で情報をぽちぽち入力して保存、画面を印刷してチェックなどとやっていたのでは結構な時間がかかってしまい、これはこれでよろしくない。
そこで、入力ツールを作成して読み込み用のファイルを作ってしまえば項目の入力～チェックまでが容易になるかなということで取り掛かりました。

##目指したところ（ゴール）
　・入力ツールを作ってxmlファイルを作成する
　・手入力は可能な限りなくしたい
　・行政の変更（項目の増減）がかかったとしても、素人レベルで対応できるような仕組みにしたい

##調べてわかったこと
まず、e-govの機能を使って保存されるファイルは、拡張子なしのxml形式ということ
申請書XML構造定義なるものが用意されている　e-gov電子申請外部連携API仕様
ここに、xml文書のサンプルも同梱されている
ファイルのコードはUTF-8じゃないと弾かれる

とまぁ、そこそこ分かりやすく、かつ資料もすぐ手に入ったのでいけんじゃねぐらいのノリではじめました

##とりあえずExcelで
とりあえず、ITとかよくわかんなくても修正の変更が可能なようにするなら、Excel VBAか外部データ作ってもらってを取り込むかなので、入力フォーマット作るならExcelでいいかなと思い、なんか転がってないかなと検索。
するとなんとまぁ、Excelさんがxmlを読み込んで、セル対応もしてくれて、エクスポートもできるとかいうことを発見。
Xmlをあまり知らなかったけど、エクスポートをマクロで叩いてあげればいいだけなんじゃないかと、かなり期待しながら作成したのですが・・・

##思わぬところで詰まる
そもそもxmlをよく知らなかった。
データとしてのxmlしか扱ったことがなく、xmlを定義に沿って作成したことがなかったのでXML Schemaというやつがいたことに気づけず、無理やりxml文書のサンプルを読み込んでみたものの、エクスポート時に要素が空白の場合に、空要素となってくれないため不足が生じてしまった。
対処するには必須要素とする必要があるようで、やっぱりXML Schemaを作る必要がでてきた。

xmlからXML Schema（xsd）を作成する方法はいくつかあるようだが、ここでまた問題が生じた。
「これ作っても自分しかわからんやん」というやつ。
聡いメンバーはそこそこいるものの、さすがにITリテラリーに疎いと変更に対する対処ができない。
バッチとかスクリプト用意するのはいいんだけど、それ自体に変更がかかった場合に完全に業務が止まるという状況が起こりえるのは事前に避けたい。

素人でも変更できるようにしつつ要件は満たす、エラー時の対処方法も考える、両方やらなくちゃーいけないのが社内エンジニア（¹まがい）のつらいところだな。

##結局マクロで生成
ギリギリまで「コード書きたくないでござる」と駄々をこねながら模索してみたけど、観念して当初の予定通りマクロで作成する。
Schema... Schemaさえあれば3行程度のマクロで済むのに。

設計時に面倒だと感じたのは２点
　・固定値で入力する（値が絶対にない）ものと空要素を区別する
　・親要素と子要素をどうやってまとめるか

まずは、以下のような変換表を作成

階層を作成することにより、直後の値との比較を取れば次が子要素になるかどうかわかる。
階層が「0」の場合は固定値という処理にすれば、要素との区別もつく。

次にコーディング
細かいところは省略しますが、概ね以下のような感じ

Sub xml_gen()

    Dim factor_list() As String
    Dim Now_level As Integer
    Dim Next_level As Integer
    Dim Cells_data As Variant
    Dim Cells_Value As String
    Dim MaxRow As Long
    Dim Loop_cnt As Long
    Dim Diff_Num As Integer
    Dim Diff_cnt As Integer
    Dim Writeing_row As Long

    'シートのセルデータを全取得
    MaxRow = Sheets("変換表").Cells(Rows.Count, 2).End(xlUp).Row
    Cells_data = Sheets("変換表").Range("A2:" & Cells(MaxRow, 3).Address)
    
    '初期化
    Writeing_row = 1
    ReDim factor_list(1)
    
    '出力データを作成
    For Loop_cnt = 1 To MaxRow - 1
　　　　'入力ツールから入力データを取得する

        If Now_level = 0 Then
            '固定データの書き込み
        ElseIf Now_level = Next_level Then
            '今の項目名で要素を閉じる
        ElseIf Now_level > Next_level Then
            '今の項目名で要素を閉じる
            '親の項目名で要素を閉じる
            'Now-Nextの分階層が深いのでLoop
        Else
            '要素は閉じずに項目名を保存する
        End If
        '改行
    Next Loop_cnt

    Call ファイル出力
    MsgBox "ファイルの出力が完了しました。"
End Sub


Function ファイル出力()
    Dim Filename As String
    Dim Loop_cnt As Long
    
    Filename = ActiveWorkbook.Path & "\" & Sheets("入力フォーマット").Range("A1")
    Loop_cnt = 1

    With CreateObject("ADODB.Stream")
        .Charset = "UTF-8"
        .Open

        Do While Sheets("変換後").Cells(Loop_cnt, 1) <> ""
            .WriteText Sheets("変換後").Cells(Loop_cnt, 1), 1
            Loop_cnt = Loop_cnt + 1
        Loop
        .SaveToFile Filename, 2
        .Close
    End With
End Function

実装の細かいところは組める人なら読めば大体想像つくと思います。
たぶん僕よりうまく書くよね。

アレンジが必要だったのは、親要素の項目名をどうやって保持するかです。
ここは配列をReDim Preserveで保持しつつ、スタック構造として扱えば多階層でも問題なく処理できるとふんで処理しました。

'追加時：
　ReDim Preserve 項目名保存配列(Ubound(項目名保存配列) + 1)
'削除時：
　ReDim Preserve 項目名保存配列(Ubound(項目名保存配列) - 1)

あとは階層「0」は固定値なので、判断の最初に持ってくるぐらいですね。
セルから取得したデータが空白だった場合に空要素にするなどの判断も入れています。

最後に別シートに書き出したデータをUTF-8で出力。
ここは誰が作ってもこの方法以外で実装のしようがないので、業務での成果物には当たらないだろうしそのまま記載。

できるだけ汎用的に処理できるようにしたので、これなら変換表を編集してあげれば項目の増減には対応可能かな。あとほかの申請書もこのマクロで回せるはず。
他の方法としては、親要素も実質値がないので、固定値かそうでないか（0 or 1）でも判断できそうでしたが、それはなんかちょっとしっくりこなかったのでこの形に。
あとシステムそのものがガラリと変わったら、それはそもそも手段を見直せってことで。

やっぱりこれだけ業務にシステムを利用することが前提になると、設計できる人材、コードが組める人材が社内にもっとほしいなぁと思う。ってことでおしまい。

1. 明確に社内エンジニアってわけではなく、収益確保しないといけないただの業務リーダーなんだけど、知見からツール作成をやることが多いだけ。（一応フロントエンドもバックエンドも経験済の元エンジニアだけど） ↩