複数のJSONファイルをExcelにテーブルとして一気に読み込むVBAの作り方

本記事の目的

本記事では、以下の目的を実現するVBAの作り方について紹介したものです。

(1) jsonファイルの形式で記述されたデータについて、人間が視覚的にわかりやすく読むためにExcelの表(テーブル)形式に変換した形にしたい。
(2) 複数のjsonファイルについても一括で同様にExcelファイルに表形式で読み込ませたい。
なお、本記事で実証した条件は以下です。
・Excel は office 365付属のもの
・複数のjsonファイルについては、シンプルにExcelブックと同じフォルダにあるものとします。
　

１．マクロの記録によるVBAコードの生成

Excel にはExcel上の操作を記録する機能がついていますので、人間の行う操作を記録します。記録した内容はマクロのコードとして出力されます。

Excelの開発メニューからマクロの記録を開始して、以下の操作を行っていきます。
・「データ」を開き、「データの取得と変換」をみつける。

・データの取得は「Webから」を選びます。自分のPC上にあるローカルのファイルでもWebからを選択します。ここでは、VScodeに付属している言語ロケールの下にあるファイルを指定します。
※ファイルのフルパスを指定してください。フォルダを指定するとエラーメッセージがでます。

・読み込んだ後で、「ファイル」メニューにある「テーブルへ変換」をクリックします。

・表形式でデータがみえれば期待値どおりです。

ここまで期待値どおりでしたら、記録を終了します。

記録したマクロを編集でみると以下のようなVBAコードが作成されていることが期待値です。

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveWorkbook.Queries.Add Name:="locales", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    ソース = Json.Document(File.Contents(""C:\Users\user\node_modules\npx\node_modules\yargs\locales\ja.json""))," & Chr(13) & "" & Chr(10) & "    テーブルに変換済み = Record.ToTable(ソース)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    テーブルに変換済み"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=locales;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [locales]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "locales"
        .Refresh BackgroundQuery:=False
    End With
    ActiveSheet.ListObjects("locales").TableStyle = ""
End Sub

２．AIエディタによる汎用化

VSCodeのプロンプトで以下のように記述します。

Excelの画面の操作で、「データの取得」から「Webから」を選択して「C:\Users\user\node_modules\npx\node_modules\yargs\locales\ja.json」というjsonファイルを指定して、テーブルに変換して読み込みする操作を「マクロの記録」で記録してコードをみると以下のようになっていました。
画面の操作ではjsonファイルを1つずつしか指定できないです。
jsonファイルの部分を変数にして同じフォルダの下にあるjsonファイルについてループで同様にシートを追加して読み込むようにするには、どのように以下のコードを修正すればいいでしょうか?

AIによりますが、私の環境では、以下のような回答を得られました。

このコードは、カレントのExcelブックが存在しているフォルダ内のすべてのJSONファイルを読み込み、それぞれのJSONファイルの内容を新しいシートにテーブル形式で出力します。JSONファイルの内容は、キーと値のペアとして出力されます。

出力されたコード

Sub ImportJSONFiles()
    Dim folderPath As String
    Dim fileName As String
    Dim jsonFilePath As String
    Dim queryName As String
    Dim ws As Worksheet
    Dim i As Long
    
    ' 現在のブックが存在するフォルダのパスを取得
    folderPath = ThisWorkbook.Path & "\"
    
    ' フォルダ内のすべてのJSONファイルをループ
    fileName = Dir(folderPath & "*.json")
    i = 1
    Do While fileName <> ""
        ' JSONファイルのパスを取得
        jsonFilePath = folderPath & fileName
        
        ' クエリ名をファイル名に基づいて設定
        queryName = Replace(fileName, ".json", "")
        
        ' クエリを追加
        ActiveWorkbook.Queries.Add Name:=queryName, Formula:= _
            "let" & Chr(13) & "" & Chr(10) & "    ソース = Json.Document(File.Contents(""" & jsonFilePath & """))," & Chr(13) & "" & Chr(10) & "    テーブルに変換済み = Record.ToTable(ソース)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    テーブルに変換済み"
        
        ' 新しいシートを追加
        Set ws = ActiveWorkbook.Worksheets.Add
        ws.Name = queryName
        
        ' テーブルを追加
        With ws.ListObjects.Add(SourceType:=0, Source:= _
            "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & queryName & ";Extended Properties=""""" _
            , Destination:=ws.Range("$A$1")).QueryTable
            .CommandType = xlCmdSql
            .CommandText = Array("SELECT * FROM [" & queryName & "]")
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .BackgroundQuery = True
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .PreserveColumnInfo = True
            .ListObject.DisplayName = queryName
            .Refresh BackgroundQuery:=False
        End With
        
        ' 次のファイル名を取得
        fileName = Dir
        i = i + 1
    Loop
End Sub

３．コードの検証

AIの作成したコードが実際に動作するかを検証してみます。

jsontest.xlsm という名前でマクロを保存して実行してみます。
jsonファイルはExcelブックと同じフォルダに配置し、24個ほどの言語ローケール情報を記述したjsonファイルがあります。
マクロを実行した結果は以下のように各jsonファイルごとにシートでjsonのデータ内容が読み込まれていることが確認できました。

4. まとめ

ExcelなどのOffice製品にあるマクロの記録機能と、AIエディタを協業(コラボ）させることで、このようなマクロを短時間で効率的に作成することが実証できました。
ちなみに、このマクロを作成するのに10分程度しかかかっていません。

今回の記事は以上になります。