Excelで管理している表形式のデータを、システム連携などで使われる階層構造のXMLファイルとして出力したい、という場面は非常に多くあります。
手作業での変換は困難ですが、VBAを使えばこのプロセスを自動化できます。この記事では、Excelのセル範囲を読み込み、ヘッダー行をタグ名として利用した動的なXMLツリーを構築・出力する実践的なVBAコードを解説します。
変換元となるExcelデータ例
はじめに、VBAで処理する元データとして、以下のような表がSheet1
に用意されていると仮定します。1行目が見出し(XMLの要素名になる)、2行目以降がデータです。
OrderID | OrderDate | CustomerName | Amount |
T-001 | 2025/8/1 | 株式会社サンプル | 50000 |
T-002 | 2025/8/5 | 有限会社テスト | 75000 |
T-003 | 2025/8/9 | ABC商事 | 120000 |
Google スプレッドシートにエクスポート
完成したVBAコード
上記の表データを、構造化されたXMLに変換するVBAコードです。
Sub ExportTableToXmlTree()
' 変数を宣言します
Dim xmlDoc As Object
Dim rootNode As Object
Dim recordNode As Object
Dim fieldNode As Object
Dim sourceRange As Range
Dim i As Long, j As Long
' XMLドキュメントオブジェクトを生成します
Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
' --- ① XMLの基本構造を作成 ---
With xmlDoc
' XML宣言を追加
.AppendChild .CreateProcessingInstruction("xml", "version=""1.0"" encoding=""UTF-8""")
' 全体を囲むルート要素を作成 (例: <OrderList>)
Set rootNode = .CreateElement("OrderList")
.AppendChild rootNode
End With
' --- ② Excelのデータを読み込みXMLツリーを構築 ---
' 処理対象のセル範囲を設定 (ヘッダー行を含む)
Set sourceRange = ThisWorkbook.Worksheets("Sheet1").Range("A1:D4")
' データ行のループ (2行目から最終行まで)
For i = 2 To sourceRange.Rows.Count
' 1レコード分の親要素を作成 (例: <Order>)
Set recordNode = xmlDoc.CreateElement("Order")
' 1列目の値を"id"属性として設定
recordNode.setAttribute "id", sourceRange.Cells(i, 1).Value
' フィールド列のループ (2列目から最終列まで)
For j = 2 To sourceRange.Columns.Count
' ヘッダー行(1行目)の値から要素名を作成 (例: <OrderDate>)
Set fieldNode = xmlDoc.CreateElement(sourceRange.Cells(1, j).Value)
' データ行の値をテキストとして要素に追加
fieldNode.Text = sourceRange.Cells(i, j).Value
' 作成したフィールド要素をレコード要素の子として追加
recordNode.AppendChild fieldNode
Next j
' 完成したレコード要素をルート要素の子として追加
rootNode.AppendChild recordNode
Next i
' --- ③ ファイルに保存 ---
xmlDoc.Save ThisWorkbook.Path & "\OrderData.xml"
' オブジェクトを解放
Set fieldNode = Nothing
Set recordNode = Nothing
Set rootNode = Nothing
Set xmlDoc = Nothing
MsgBox "XMLファイルの出力が完了しました。"
End Sub
コードのポイント解説
① ルート要素の準備
まず、すべてのレコードを格納するための大枠となる「ルート要素」(この例では<OrderList>
)を作成し、XML宣言とあわせてドキュメントの基本構造を準備します。
② データ行のループ (For i = …)
For i = 2 To sourceRange.Rows.Count
最初のFor
ループで、データが格納されている2行目から最終行までを1行ずつ処理していきます。このループ1回で、XMLの1レコード(この例では<Order>
要素)が作られます。
③ レコード要素の作成と属性設定
Set recordNode = xmlDoc.CreateElement("Order")
recordNode.setAttribute "id", sourceRange.Cells(i, 1).Value
ループの中で、まず各レコードの親となる要素(<Order>
)を作成します。そして、そのレコードの識別に使うIDなどを.setAttribute
で属性として設定します。ここではExcelの1列目の値をIDとしています。
④ フィールド要素のループ (For j = …)
For j = 2 To sourceRange.Columns.Count
内側のFor
ループでは、レコード内の各項目(日付、顧客名、金額など)を処理します。
⑤ 動的な要素名の作成
Set fieldNode = xmlDoc.CreateElement(sourceRange.Cells(1, j).Value)
これが、このコードの最も重要なテクニックです。 要素名(タグ名)を "OrderDate"
のように固定で記述するのではなく、sourceRange.Cells(1, j).Value
、つまりExcelシートの1行目(ヘッダー行)にある見出しを動的に取得して要素名として設定しています。これにより、元のExcelの列構成が変わってもコードを修正する必要がなくなります。
⑥ ツリーの組み立て (.AppendChild)
recordNode.AppendChild fieldNode ' フィールドをレコードに追加
rootNode.AppendChild recordNode ' レコードをルートに追加
内側のループで作成したフィールド要素(<OrderDate>
など)をレコード要素(<Order>
)にAppendChild
で追加し、内側のループが完了したら、そのレコード要素をルート要素(<OrderList>
)にAppendChild
で追加します。この入れ子構造の組み立てにより、XMLのツリーが形成されます。
生成されるXMLファイルの例
上記のコードを実行すると、OrderData.xml
というファイルが生成され、その中身は以下のようになります。
<?xml version="1.0" encoding="UTF-8"?>
<OrderList>
<Order id="T-001">
<OrderDate>45879</OrderDate>
<CustomerName>株式会社サンプル</CustomerName>
<Amount>50000</Amount>
</Order>
<Order id="T-002">
<OrderDate>45883</OrderDate>
<CustomerName>有限会社テスト</CustomerName>
<Amount>75000</Amount>
</Order>
<Order id="T-003">
<OrderDate>45879</OrderDate>
<CustomerName>ABC商事</CustomerName>
<Amount>120000</Amount>
</Order>
</OrderList>
(日付がシリアル値で表示されています。必要に応じてFormat
関数などで整形してください。)
まとめ
Excelの表データを構造的なXMLに変換するには、**「入れ子(ネスト)にしたループ処理」と「動的な要素名の作成」**が鍵となります。
- 外側のループで1レコード分の親要素を作り、
- 内側のループで各フィールドの要素を動的に生成し、親要素に追加していく
このパターンを応用すれば、様々な形式の表データを柔軟にXML化することが可能です。