Zenn
Open9

VBAでExcelのデータをJson形式で出力するまで

Excel
初心者
JSON
VBA
なみおなみお

はじめに

タイトルの通りExcelのデータをJsonとして出力したいことがあったので調べた内容ややったことを議事録として残していきます。

なみおなみお

筆者の環境

  • OS: Windows 10 pro
  • Excel: Microsoft 365 Apps for enterprise
なみおなみお

Excelに「開発」タブ（リボン）を表示させる

  1. Excelを開き、ファイル > その他 > オプション を開く
  2. Excelのオプションが開くので、リボンのユーザー設定を選択し、右側にある開発にチェックを入れる
  3. Excelの画面に戻り、上に開発というタブが出ていればOK
なみおなみお

Visual Basicを開く

  1. 上で表示させた開発タブをクリックし、左上にあるVisual Basicをクリックする
  2. するとVBE( Visual Basic Editor )というマクロを作成・編集するための専用のエディターが開かれる

使い方は下記サイトが詳しく書かれており、私はこちらのサイトで学習しました。
https://excel-ubara.com/excelvba1/EXCELVBA481.html

なみおなみお

「VBA-JSON」のインポート

VBA-JSONはDictionary型というVBAで使用する型をJson形式のString型に変換してくれるものです。
Jsonを出力するためには必須です。

  1. 下記のサイト ( github ) を開く

https://github.com/VBA-tools/VBA-JSON

  1. 開いたgithubの最下部あたりにあるInstallationのlatest releaseをクリック
  2. Source code (zip)をクリックしzipファイルをダウンロード
  3. ダウンロードしたzipファイルを解凍する
  4. 解凍したフォルダの中にあるJsonConverter.basをVBEにインポートする
なみおなみお

早速出力してみる

  1. VBEを開いた状態で上のメニュータブから挿入 > 標準モジュールをクリック
  2. 上のコードを入力する窓に下記コードを入力
コード全文
Module1
Sub test()
    Dim params As New Dictionary
    
    ' keyとvalueを追加
    params.Add "key1", "value1"
    params.Add "key2", "value2"
    params.Add "key3", "value3"
    
    ' Dictionaryの入れ子
    Dim child As New Dictionary
    child.Add "childKey1", "childValue1"
    child.Add "childKey2", "childValue2"
    child.Add "childKey3", "childValue3"
    params.Add "key4", child
    
    ' Listを追加
    Dim list As New Collection
    list.Add "item1"
    list.Add "item2"
    list.Add "item3"
    list.Add "item4"
    params.Add "key5", list
    
    ' Jsonを作成
    Dim json As String
    json = JsonConverter.ConvertToJson(params, Whitespace:=4)
    
    ' Jsonを出力
    With CreateObject("ADODB.Stream")
        .Type = 2
        .Charset = "utf-8"
        .Open
        .WriteText json, 1
        .SaveToFile ThisWorkbook.Path & "/" & "data.json", 2
        .Close
    End With
    
End Sub
  1. VBEを開いた状態でF5を押し、実行する
  2. デスクトップにdata.jsonが出力されている

これでJsonファイルの出力ができました。
ちなみに実行したファイルをvscodeで開いたのが下記画像です。

コードにあるJsonConverter.ConvertToJson(params, Whitespace:=4)のWhitespace:=4という指定をしているの入れ子ごとにスペース4つ分の間隔があいてます。

なみおなみお

日本語を入力すると...

先ほどのコードのValue部分をすべて日本語に変えてみた。

日本語に変更後のコード全文
Module1
Sub test()
    Dim params As New Dictionary
    
    ' keyとvalueを追加
    params.Add "key1", "あいうえお"
    params.Add "key2", "かきくけこ"
    params.Add "key3", "さしすせそ"
    
    ' Dictionaryの入れ子
    Dim child As New Dictionary
    child.Add "childKey1", "たちつてと"
    child.Add "childKey2", "なにぬねの"
    child.Add "childKey3", "はひふへほ"
    params.Add "key4", child
    
    ' Listを追加
    Dim list As New Collection
    list.Add "まみむめも"
    list.Add "やゆよ"
    list.Add "らりるれろ"
    list.Add "わをん"
    params.Add "key5", list
    
    ' Jsonを作成
    Dim json As String
    json = JsonConverter.ConvertToJson(params, Whitespace:=4)
    
    ' Jsonを出力
    With CreateObject("ADODB.Stream")
        .Type = 2
        .Charset = "utf-8"
        .Open
        .WriteText json, 1
        .SaveToFile ThisWorkbook.Path & "/" & "data.json", 2
        .Close
    End With
    
End Sub

このコードを実行し、data.jsonを確認したところ下記のようにUnicodeの16進数表記になってしまう。

なみおなみお

Unicode 16進数表記の改善

  1. VBEの左側プロジェクトの中にあるJsonConverterをダブルクリックして開く
  2. コード内でctrl + FでVBA.Right$を検索する
  3. 下記のようにシングルクォーテーションを付け加え、コードを書き換える
JsonConverter
・・・中略
- json_Char = "\u" & VBA.Right$("0000" & VBA.Hex$(json_AscCode), 4)
+ ' json_Char = "\u" & VBA.Right$("0000" & VBA.Hex$(json_AscCode), 4)
・・・中略
  1. 再び実行してみる
  2. デスクトップにあるdata.jsonを確認する

これで直りました！