JSONファイルから各キーの値を読み込んでEXCELファイルに出力するサンプルプログラムを書きました。VBA-JSONを利用してJSONデータをパースします。
準備
下記よりVBA-JSONをダウンロードします。ここではVBA-JSON-2.2.1.zipをダウンロードしました。
https://github.com/VBA-tools/VBA-JSON/releases
解凍すると「JsonConverter.bas」が含まれているので、これをインポートします。VBAの開発ツールを開き、File -> Import Fileより「JsonConverter.bas」を選択します。
また、Tools -> Referencesより「Microsoft Scripting Runtime」にチェックを入れます。
データモデル
上図のようなJSONデータを保持するTypeを用意します。
JsonTypes
Option Explicit ' メンバタイプ Public Type Member id As Integer name As String age As Integer End Type ' チームタイプ Public Type Team teamName As String members() As Member End Type
JSONファイルリーダー
JSONを読み込むクラスモジュールを次のように書きました。
JsonReader
Option Explicit Dim jsonObj As Object ' JSONファイルをロードする Public Sub LoadJsonFile(ByVal fileName As String) Dim fso As FileSystemObject Set fso = New FileSystemObject Dim jsonTS As textStream Set jsonTS = fso.OpenTextFile(fileName, ForReading) Dim jsonText As String jsonText = jsonTS.ReadAll jsonTS.Close Set jsonObj = JsonConverter.ParseJson(jsonText) End Sub ' JSONデータをロードする Public Function GetTeam(ByRef teamObj As JsonTypes.Team) ' 項目の読み込み teamObj.teamName = jsonObj("team_name") ' 配列の読み込み Dim Values As Variant ReDim teamObj.members(jsonObj("members").count - 1) Dim i As Integer For i = 1 To jsonObj("members").count ' メンバの作成 Dim memberObj As JsonTypes.Member memberObj.id = jsonObj("members")(i)("id") memberObj.name = jsonObj("members")(i)("name") memberObj.age = jsonObj("members")(i)("age") teamObj.members(i - 1) = memberObj Next End Function ' デストラクタ Public Sub Class_Terminate() If Not jsonObj Is Nothing Then Set jsonObj = Nothing End Sub
LoadJsonFIleでJSONファイルからデータを読み込み、JSONオブジェクトとして内部に保持します。JSONファイルを文字列として読み込み、JsonConverter.ParseJsonでその文字列をパースします。
GetTeamでは、JSONオブジェクトをデータモデルに従ったオブジェクトに変換します。JSONオブジェクトにキーを指定と、そのキーに対する値を取得できます。配列を読み込むときは、オブジェクトのcountが要素数を示すことを利用してループ処理で値を取得します。ただし、JSONオブジェクトの配列のindexは1から始まりのようなので、注意。
メイン処理
JsonReaderを利用して、JSONファイルから読み込んだ内容をExcelのセルに出力するモジュールを次のように書きました。
SampleJsonReaderModule
Option Explicit Sub ReadSample() ' 入力ファイル Dim inputFileName As String inputFileName = "C:\sample.json" ' JSONファイルの読み込み準備 Dim jr As JsonReader Set jr = New JsonReader jr.LoadJsonFile (inputFileName) ' JSONデータよりデータを読み込む Dim teamObj As JsonTypes.Team Call jr.GetTeam(teamObj) ' 結果の表示 Cells(1, 1) = "team_name" Cells(1, 2) = "id" Cells(1, 3) = "name" Cells(1, 4) = "age" Dim rowIndex As Integer rowIndex = 2 If Sgn(teamObj.members) <> 0 Then Dim i As Integer For i = 0 To UBound(teamObj.members) Cells(rowIndex, 1) = teamObj.teamName Cells(rowIndex, 2) = teamObj.members(i).id Cells(rowIndex, 3) = teamObj.members(i).name Cells(rowIndex, 4) = teamObj.members(i).age rowIndex = rowIndex + 1 Next i End If Set jr = Nothing End Sub
実行すると、team_name、id、name、ageが1〜4列目に出力されました。