LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@Q11Q

Excel VBA + PowerQuery Jsonを出力して取り込む

Last updated at Posted at 2025-08-12

今回の特徴

たぶんJsonを外部ライブラリを使わず、Pythonも使わずVBAの標準装備のみで書き出し、読み込みをしている数少ないサンプルコードになります。Copilotからリンクされそう。
「純VBAでM言語を操作する最小構成」 らしい。

次のようなExcelのシートを想定

1:確認時点:                 
2:"2025/8/12 9:25","現在"
3:"現在の状況："
4:"所属職員３名全員、家族および家屋に関する被害は確認されておりません。"
5:報告対象職員：                       
6:"役職名","氏名","家族","家屋"
7:"社長","社長太郎","被害なし","被害なし"
8:"副社長","福田長","被害なし","被害なし"

image.png
確認時点:
2025/8/12 23:02
現在の状況:
所属職員３名全員、家族および家屋に関する被害は確認されておりません。
報告対象職員:
役職名 氏名 家族 家屋
社長 社長太郎 被害なし 被害なし
副社長 福田次郎 被害なし 被害なし

今回はこの中から

役職名	氏名	家族	家屋
社長	社長太郎	被害なし	被害なし
副社長	福田次郎	被害なし	被害なし

この部分のJsonを作り出す。これを\\Server\name\Documents\full_report.jsonとする。
またこのxlsmファイルも同じ場所にある。

ExcelからJsonを出力

このマクロ自体は表部分以外の部分も含めて出力している

Sub ExportFullReportToJSON3()
' For Excel
' Jsonファイルの作成
' 参照設定なし
    Dim ws As Worksheet
    Dim json As String
    Dim r As Long, lastRow As Long
    Dim sr As Object: Set sr = CreateObject("ADODB.Stream")
    Dim byteData() As Byte
    Set ws = ThisWorkbook.Sheets("Sheet1") ' シート名を適宜変更
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' 文章部分の取得
    Dim timestamp As String, statusText As String
    timestamp = ws.Range("A2").Value
    statusText = ws.Range("A4").Value

    json = "{"
    json = json & """確認時点"":""" & timestamp & ""","
    json = json & """現在の状況"":""" & statusText & ""","
    json = json & """報告対象職員"":["
    ' 表部分の処理（A6から開始）
' 列タイトルを読み込んでも意味がないので、実データから読み込む
' 空白がある場合、不明とする
' データは7行目以降Ａ1(役職名)が空白になるまで続く
    r = 7
    Do While ws.Cells(r, 1).Value <> ""
        json = json & "{"
        json = json & """" & ws.Range("A6").Value & """:""" & ws.Cells(r, 1).Value & ""","
        json = json & """" & ws.Range("B6").Value & """:""" & ws.Cells(r, 2).Value & ""","
        json = json & """" & ws.Range("C6").Value & """:""" & IIf(ws.Cells(r, 3).Value = "", "不明", ws.Cells(r, 3).Value) & ""","
        json = json & """" & ws.Range("D6").Value & """:""" & IIf(ws.Cells(r, 4).Value = "", "不明", ws.Cells(r, 4).Value) & """"
        json = json & "}"
        r = r + 1
        If ws.Cells(r, 1).Value <> "" Then json = json & ","
    Loop
        json = json & "]}"

    ' JSONファイルとして保存　UTF-8(65001), BOM なし, 改行コード LF
    With sr
    .LineSeparator = 10 'adLF = 10
    .Mode = 3 ' adModeReadWrite = 3
    .Type = 2 ' adTypeText = 2
    .Charset = "UTF-8"
    .Open
    .WriteText json, 0 ' adWriteChar = 0
' BOMを削除
    .Position = 0
    .Type = 1 ' adTypeBinary = 1
    .Position = 3
     byteData = .Read
    .Close
    .Open
    .Write byteData
' Save この場合上書き
    .SaveToFile ThisWorkbook.Path & "\full_report.json", 2  'adSaveOverWrite = 2
    .Close
    End With
    MsgBox "JSONファイルを出力しました。"
    Set sr = Nothing
End Sub

Jsonを書きだすポイント

1.エンコード

UTF-8 BOM なし

2.改行コード

LF

3.True FalseはLcaseで小文字に

Lcaseでよい

4.日時が出てきた時はformatで整形

5.エスケープが必要な場合に注意

ダブルクォーテーションもエスケープ

6.入れ子構造に注意

すみカッコと波カッコで変わるので注意

7.タイトル行は原則不要

各データ内にキーが入るためタイトル行が不要
このため、Jsonはタイトル行を含めずに読み込む

PowerQueryの操作

手動操作の時、
詳細エディターを書き換える
また, 65001を追加する

let
    ソース = Json.Document(File.Contents("\\\\Server\\name\\Documents\\full_report.json"), 65001),
    報告対象職員テーブル = Table.FromList(ソース[報告対象職員], Splitter.SplitByNothing(), {"Record"}),
    展開 = Table.ExpandRecordColumn(報告対象職員テーブル, "Record", {"役職名", "氏名", "家族", "家屋"})
in
    展開

これをM言語というらしいが、全角日本語名の変数名使える変態感満載な言語だ。
通常、マクロを自動記録しても変数が日本語になることはない。まあ変数が基本出ないけど。

VBA

Sub PowerQueryMacro()
    ThisWorkbook.Queries.Add Name:="full_report", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & _
 "    ソース = Json.Document(File.Contents(""\\\\Server\\name\\Documents\\full_report.json""), 65001)," & Chr(13) & "" & Chr(10) & _
 "    報告対象職員テーブル = Table.FromList(ソース[報告対象職員], Splitter.SplitByNothing(), {""Record""})," & Chr(13) & "" & Chr(10) & _
 "    展開 = Table.ExpandRecordColumn(報告対象職員テーブル, ""Record"", {""役職名"", ""氏名"", ""家族"", ""家屋""})" & Chr(13) & "" & Chr(10) & _
 "in" & Chr(13) & "" & Chr(10) & _
 "    展開"
    ThisWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=full_report;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable  ' Point ListObject...QueryTableで最終的に取得しているのはQueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [full_report]") ' 表形式だろうとお構いなく取得
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "full_report" ' ここで本来は親であるListObjectをもう一回取り直している
        .Refresh BackgroundQuery:=False
    End With
End Sub

Excel VBAにおける ListObject と QueryTable の関係と構造的逆転の面白さ

1. はじめに

Excel VBA で外部データを扱う場合、ListObject（テーブル）と QueryTable（外部データ接続）の2つのオブジェクトが登場する。
しかし、この2つの役割と実際の生成順序には「逆転」して見える面白い構造がある。

この記事では、特に Power Query（Copilot のデータ取得部分）経由で使う場合のオブジェクト階層と、その背景にある Excel の設計思想を整理する。

2. ListObject と QueryTable の違い

項目 ListObject（リストオブジェクト） QueryTable（クエリテーブル）
表現対象 Excel のテーブル機能 外部データ取得の設定と実行
主な機能 テーブル構造の管理（列・行・スタイル・フィルタ等） 外部データの接続・取得・更新
外部データとの関係 外部データ連携時に内部に QueryTable を持つ 単独でもセル範囲にデータを挿入可能
VBAでの生成例 ListObjects.Add(...) QueryTables.Add(...)
データ形式 表形式（列名付き） 生データ（取得結果をセルに展開）

3. オブジェクト階層の関係

Excel の VBA オブジェクト構造では ListObject が上位、QueryTable が下位 という関係になる。

ListObject
└─ QueryTable
  • ListObject … テーブルの見た目・構造・操作性を管理する「入れ物」。
  • QueryTable … データ取得のための「蛇口」。

4. 論理的な順序との逆転

論理的な目的順序はこうなるはずである。

  1. 外部データを取得する（QueryTable）
  2. 表示構造を決める（ListObject）

しかし Excel VBA では次のように逆順になる。

  1. ListObject を作る
  2. ListObject.QueryTable に接続情報を設定する
  3. .Refresh で中身を流し込む

これは Excel が「データベースアプリ」ではなく、見た目重視の表計算アプリとして発展してきた歴史に由来する。

5. Power Query 経由の場合の階層構造

今回のような Power Query経由では、さらに階層が深くなる。
ListObject
└─ QueryTable
└─ OLE DB 接続 (Microsoft.Mashup.OleDb.1)

6. この構造の面白さ

  • 目的と階層が逆：データ取得が本質のはずなのに、見た目（ListObject）を先に作る。
  • 蛇口とシンクの関係：
    • QueryTable = 蛇口（外部データの入口）
    • ListObject = シンク（受け皿）
    • Power Query = 浄水器（変換・加工）
  • ユーザーが最初に触るのは「見た目」なので、UI構造を先に作る設計になっている。
  • .QueryTable にアクセスすることで、裏側の M 式や接続文字列を動的に差し替えることも可能。

7. まとめ

  • ListObject はテーブル構造
  • QueryTable はデータ取得
  • Excel VBA では ListObject → QueryTable の順で作られるため、論理順序と逆転している。
  • Power Query 経由だとさらに階層が深まり、M 言語やデータ変換処理まで一連で管理できる。
  • この逆転は、Access や SQL Server のような「データ中心設計」とは真逆で、Excel が「見た目優先・UI優先」の文化を持っていることを示している。

Excel Power Query の M 言語で JSON を読み書きする ― 外部リンク廃止の布石としての機能

1. はじめに

Microsoft Excel には Power Query（クエリエディタ）が標準搭載されており、
その裏で動いているスクリプト言語が M 言語（Microsoft Power Query Formula Language） です。

  • 由来は Mashup Language（複数ソースの統合＝Data Mashup）。
  • SQL のようなデータ取得言語ではなく、取得後の変換・加工ロジックを記述する関数型言語。
  • 大文字小文字を区別し、let ... in ... 構文を使う。

この記事では、M 言語を用いた JSONの読み込み例 と、
それが 外部リンク廃止 の流れに繋がる構造的意味について整理します。

2. M 言語の実例（JSON 読み込み）

例えば以下のJSONファイル（full_report.json）が共有フォルダにあるとします。

{
  "確認時点": "2025/8/12 9:25",
  "現在の状況": "所属職員３名全員、家族および家屋に関する被害は確認されておりません。",
  "報告対象職員": [
    { "役職名": "社長", "氏名": "社長太郎", "家族": "被害なし", "家屋": "被害なし" },
    { "役職名": "副社長", "氏名": "福田長", "家族": "被害なし", "家屋": "被害なし" }
  ]
}

これを Power Query で展開する M コードは以下のようになる。
ちなみにコードは一番近いfsharpでハイライトしている。

let
    // JSONをUTF-8で読み込む
    ソース = Json.Document(
        File.Contents("\\\\Server\\name\\Documents\\full_report.json"), 
        65001
    ),

    // "報告対象職員" 部分（リスト）をテーブルに変換
    報告対象職員テーブル = Table.FromList(
        ソース[報告対象職員], 
        Splitter.SplitByNothing(), 
        {"Record"}
    ),

    // Record を展開（欠損は null）
    展開 = Table.ExpandRecordColumn(
        報告対象職員テーブル, 
        "Record", 
        {"役職名", "氏名", "家族", "家屋"}, 
        {"役職名", "氏名", "家族", "家屋"},
        MissingField.UseNull
    )
in
    展開

3. JSON読み書きの意義

読み込み：外部ファイルを直接解析し、Excel内部のテーブルとして展開できる。
書き込み（VBA等と併用）：JSON形式で保存すれば、他システムとのデータ連携や再インポートが容易になる。
スキーマ自動認識：タイトル行を JSON に含めず、列キーを持たせることで展開時に自動的に列名になる。

4. 外部リンク廃止の布石

  • Excel の外部参照（ブック間リンク）は、従来は =[\Server\Share\book.xlsx]Sheet1!A1 のように直接リンクを張る方式が主流だった。
  • しかし、近年の Microsoft の動きとしては 外部リンク機能を段階的に弱体化・廃止 し、代わりに Power Query（M 言語）による接続と変換 を推奨する方向が明確です。

理由の一例：

  • 外部リンクはファイル構造の変更やアクセス権限の影響を受けやすく、破損リスクが高い。
  • クラウド環境（SharePoint / OneDrive / Teams）では、外部リンクよりもクエリ接続のほうが安定する。
  • JSON や CSV のような中間ファイル形式での受け渡しが、セキュリティ面・再現性で優れる。

結果として：

  • JSON 読み書き機能は、外部リンク依存から脱却するための実務的な移行手段になる。
  • Power Query を経由することで、データ変換・検証を取り込み時に自動実行できる。

5. まとめ

  • M 言語は Power Query の裏で動く正式なスクリプト言語（Mashup Language）。
  • JSON 読み込みは Json.Document(File.Contents(...)) から始まる。
  • この仕組みは単なる機能追加ではなく、Excelの外部リンク廃止への布石でもある。
  • VBAと併用すれば、JSON形式での双方向データ運用が可能になり、従来のブック間リンクより堅牢。

6.PowerQueryとM言語のリンク

M言語仕様
Power Query のドキュメント

参考リンク

Queries.Add メソッド (Excel) | Microsoft Learn

QueryTable オブジェクト (Excel) | Microsoft Learn
　QueryTable.CommandType プロパティ (Excel) | Microsoft Learn

　　XlCmdType 列挙 (Excel) | Microsoft Learn

　QueryTable.CommandText プロパティ (Excel) | Microsoft Learn

　QueryTable.RowNumbers プロパティ (Excel) | Microsoft Learn

　QueryTable.FillAdjacentFormulas プロパティ (Excel) | Microsoft Learn

　QueryTable.RefreshOnFileOpen プロパティ (Excel) | Microsoft Learn
True の 場合、ブックが開かれるたびにピボットテーブルのキャッシュまたはクエリ テーブルが自動的に更新されます。 既定値は False です。

　QueryTable.BackgroundQuery プロパティ (Excel) | Microsoft Learn
True を指定 すると、クエリ テーブルのクエリが非同期的に (バックグラウンドで) 実行されます。
BackgroundQuery プロパティにアクセスするには、ListObject の QueryTable プロパティを使用します。

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?