何かを一括登録するためのCSVをVBAで作成する

概要

タイトルの通り、何かのシステムにCSVで一括登録したいとき、エクセルに入力したデータをCSVに変換するVBAのサンプルコードを紹介しています。

この記事の経緯

システムへ何かデータを一括登録するときに、フォーマットに沿ったCSVを作成する必要がありますが、
以下のようなケースにおいて、エンドユーザーだけでは正しいCSVを作成するのには難しい場合があります。

• バリデーションが複雑
  • ユーザーがシステムを利用する際はシステム側がいい感じにエラーを出してくれるので、一定のルールに沿った正しい入力値になるよう誘導してくれるるはず
  • しかしそれをCSVで人力で作成しようとするのは難しい
• 選択肢はなるべくマジックナンバーで扱いたい
  • 例えばシステム上で「スマホ」「タブレット」「PC」などの選択肢がある場合、内部的にはそれぞれ「1」「2」「3」などの区分値で扱っている場合などが当てはまる
  • ユーザーに『CSVでは「スマホ」という文字列ではなく数値で「1」として作成してね』というルールを徹底してもらうのは難しい
  • 上記だけであれば頑張ってもらえば良いが、複数あると覚えきれず、選択肢と番号の対応表を別途用意する必要がある→それは面倒！

私が実際の案件で上記のような課題を感じたので、ある程度入力チェックされたデータがCSVになっていて欲しいと思い、以下の方法でユーザー側が簡単にCSV作成できる方法を考えました。

1. エクセルで入力用シートを用意して、入力規制や関数などで正しく入力してもらえる様にする
2. (ある程度正しく入力された)入力用シートの情報からCSV出力用シートに関数でデータを整形する
3. 整形されたCSV出力用シートの情報からCSVを作成するVBAを実行する

特に3.のVBAのサンプルコードは軽く探した感じあまり出てこなかったのでこの記事を書くことにしました。

制約

• 動作環境としてはWindowsのみ
  • 以下の設定に必要な ActiveX Data Objects が Mac 版の Excel では参照設定できないため
• 文字コードはUTF-8
• BOMなし

サンプルコード

• {CSV出力用のシート名}にCSV出力したいシートの名称を入れてください
• 最終列、最終行を取得してCSVを作成するのでデータ数に影響を受けません
• VBAを実行したエクセルと同じ階層にCSVが出力されます

Sub csvCreate()

    ' CSV出力用のシートの指定
    Dim outputSheet = Worksheets("{CSV出力用のシート名}")

    ' CSV生成する前にエクセルの再計算処理を実行
    Worksheets("{入力用のシート}").Calculate
    outputSheet.Calculate

    '最終行列
    Dim maxRow As Long: maxRow = outputSheet.Columns("A").Find("*", , xlValues, , , xlPrevious).Row
    Dim maxCol As Long: maxCol = outputSheet.Range("A1").SpecialCells(xlLastCell).Column

    'ファイル名作成
    Dim strFileName As String
    strFileName = "outputCSV_" & Format(Now, "yyyymmddhhmmssms") & ".csv"

    'ファイルパス指定
    Dim strFilePath As String
    strFilePath = ThisWorkbook.Path & "/" & strFileName

    ' ファイルをUTF-8で開く
    Dim st As Object: Set st = New ADODB.Stream
    st.Charset = "UTF-8"
    st.Open

    Dim strLine As String
    Dim i As Long, j As Long
    For i = 1 To maxRow
        strLine = ""
        For j = 1 To maxCol - 1
            strLine = strLine & outputSheet.Cells(i, j) & ","
        Next j
        strLine = strLine & outputSheet.Cells(i, j)
        st.WriteText strLine, adWriteLine
    Next i

    '上書きモードでセーブ
    st.SaveToFile strFilePath, adSaveCreateOverWrite
    st.Close

    '完了メッセージ
    MsgBox "CSVファイルを保存しました。" & vbCrLf & _
    "保存先のフォルダは" & strFilePath & "です。"

End Sub

まとめ

こんな記事を書いておきながらではありますが、本来は一括登録をCSVで行わなくても良くなるUXを先に検討すべきだと個人的には思います。
なぜなら、システムの保守とは別にVBAやそのエクセルの保守も追加で必要になってくるためです。
ただしこの保守の話はお客さんやエンドユーザーにとってはあまり関係ないので、ユーザー目線で使いやすければ結局エクセルでも良いという話はあるかもしれません。

もちろんUXを検討してみた結果、コストや費用対効果・登録内容の複雑さといった観点でCSVで一括登録する機能を実装することは珍しくないと思いますので、本記事が何かの参考になれば幸いです。