前提

下記のエクセルの表があります。

Excel表

MCompGrp
| ID    | GName | Code       |
| ----- | ----- | ---------- |
| Head  | 本社  | 〒xxx-xxxx |
| Tokyo | 東京  | 〒yyy-yyyy |

実施したいこと

このエクセルシートを読み込んでXMLを出力VBAマクロを作成したいです。上記の表の場合出力したいXMLは下記です。

<MCompGrp>      
    <CompGrp>  
        <ID>Head</ID>
        <GName>ソリューション</GName>
        <Code>〒xxx-xxxx</Code>
    </CompGrp> 
    <CompGrp>  
        <ID>Tokyo</ID>
        <GName>東京</GName>
        <Code>〒yyy-yyyy</Code>
    </CompGrp> 
</MCompGrp>

どの様にVBAコードを書けばいいですか？

ANS

以下に、指定されたエクセル表からXMLを出力するVBAマクロのコード例を示します。

Sub ExportToXML()

    Dim ws As Worksheet
    Dim xml As String
    Dim i As Long
    Dim lastRow As Long
    
    ' シート "MCompGrp" を選択
    Set ws = ThisWorkbook.Sheets("MCompGrp")
    
    ' 最終行を取得
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' XMLのヘッダー
    xml = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf
    xml = xml & "<MCompGrp>" & vbCrLf
    
    ' 表のデータをXML形式に変換
    For i = 2 To lastRow
        xml = xml & vbTab & "<CompGrp>" & vbCrLf
        xml = xml & vbTab & vbTab & "<ID>" & ws.Cells(i, 1).Value & "</ID>" & vbCrLf
        xml = xml & vbTab & vbTab & "<GName>" & ws.Cells(i, 2).Value & "</GName>" & vbCrLf
        xml = xml & vbTab & vbTab & "<Code>" & ws.Cells(i, 3).Value & "</Code>" & vbCrLf
        xml = xml & vbTab & "</CompGrp>" & vbCrLf
    Next i
    
    ' XMLのフッター
    xml = xml & "</MCompGrp>" & vbCrLf
    
    ' XMLをファイルに出力
    Dim fileName As String
    Dim fso As Object
    Dim outputFile As Object
    
    fileName = ThisWorkbook.Path & "\output.xml"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set outputFile = fso.CreateTextFile(fileName, True, True)
    
    outputFile.Write xml
    outputFile.Close
    
    MsgBox "XMLファイルが出力されました。"

End Sub

このコードは、以下の手順で動作します。

"MCompGrp" シートを選択し、最終行を取得します。
XMLのヘッダー部分を作成します。
表のデータを読み取り、XML形式に変換します。
XMLのフッター部分を作成します。
変換したXMLをファイルに出力します。
このマクロを実行すると、エクセルファイルと同じディレクトリに "output.xml" という名前のXMLファイルが生成されます。

Q

VBAマクロは表があるシートのExcel Object上に作成する予定です。その場合はシートの選択は不要でしょうか？

A

Sub ExportToXML()

    Dim xml As String
    Dim i As Long
    Dim lastRow As Long
    
    ' 最終行を取得
    lastRow = Me.Cells(Me.Rows.Count, 1).End(xlUp).Row
    
    ' XMLのヘッダー
    xml = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf
    xml = xml & "<MCompGrp>" & vbCrLf
    
    ' 表のデータをXML形式に変換
    For i = 2 To lastRow
        xml = xml & vbTab & "<CompGrp>" & vbCrLf
        xml = xml & vbTab & vbTab & "<ID>" & Me.Cells(i, 1).Value & "</ID>" & vbCrLf
        xml = xml & vbTab & vbTab & "<GName>" & Me.Cells(i, 2).Value & "</GName>" & vbCrLf
        xml = xml & vbTab & vbTab & "<Code>" & Me.Cells(i, 3).Value & "</Code>" & vbCrLf
        xml = xml & vbTab & "</CompGrp>" & vbCrLf
    Next i
    
    ' XMLのフッター
    xml = xml & "</MCompGrp>" & vbCrLf
    
    ' XMLをファイルに出力
    Dim fileName As String
    Dim fso As Object
    Dim outputFile As Object
    
    fileName = ThisWorkbook.Path & "\output.xml"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set outputFile = fso.CreateTextFile(fileName, True, True)
    
    outputFile.Write xml
    outputFile.Close
    
    MsgBox "XMLファイルが出力されました。"

End Sub

このコードは、上記のコード例と同じように動作しますが、シート選択部分が Me キーワードを使用している点が異なります。これにより、マクロが配置されているシートに直接アクセスできます。

Q

エクセルのテーブル機能を使って、表にはテーブル名として「MCompGrp」と設定する予定です。その場合、表から値を取得する方法は変わりますか？

A

Sub ExportToXML()

    Dim tbl As ListObject
    Dim xml As String
    Dim i As Long
    
    ' テーブル "MCompGrp" を選択
    Set tbl = Me.ListObjects("MCompGrp")
    
    ' XMLのヘッダー
    xml = "<?xml version=""1.0"" encoding=""UTF-8""?>" & vbCrLf
    xml = xml & "<MCompGrp>" & vbCrLf
    
    ' 表のデータをXML形式に変換
    For i = 1 To tbl.ListRows.Count
        xml = xml & vbTab & "<CompGrp>" & vbCrLf
        xml = xml & vbTab & vbTab & "<ID>" & tbl.DataBodyRange(i, 1).Value & "</ID>" & vbCrLf
        xml = xml & vbTab & vbTab & "<GName>" & tbl.DataBodyRange(i, 2).Value & "</GName>" & vbCrLf
        xml = xml & vbTab & vbTab & "<Code>" & tbl.DataBodyRange(i, 3).Value & "</Code>" & vbCrLf
        xml = xml & vbTab & "</CompGrp>" & vbCrLf
    Next i
    
    ' XMLのフッター
    xml = xml & "</MCompGrp>" & vbCrLf
    
    ' XMLをファイルに出力
    Dim fileName As String
    Dim fso As Object
    Dim outputFile As Object
    
    fileName = ThisWorkbook.Path & "\output.xml"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set outputFile = fso.CreateTextFile(fileName, True, True)
    
    outputFile.Write xml
    outputFile.Close
    
    MsgBox "XMLファイルが出力されました。"

End Sub

このコードは、以下の手順で動作します。

"MCompGrp" テーブルを選択します。 XMLのヘッダー部分を作成します。テーブルのデータを読み取り、XML形式に変換します。 XMLのフッター部分を作成します。変換したXMLをファイルに出力します。このマクロを実行すると、エクセルファイルと同じディレクトリに "output.xml" という名前のXMLファイルが生成されます。

Q

VBAでXMLフォーマットを扱うには「MSXML」というライブラリを使用すると良いとききました。
- 参照設定から「Microsoft XML, v6.0」を選択しておく必要はないですか？
VBAでXMLオブジェクトを扱う場合は「XML DOMオブジェクト」を作成したほうが良いとききました。その必要はないですか？

A

おっしゃる通り、VBAでXMLを扱う場合、「MSXML」ライブラリを使用することが一般的です。また、XML DOMオブジェクトを使ってXML構造を操作することもできます。

上記のコード例では、シンプルなXMLを文字列として扱っていますが、XML DOMオブジェクトを使用してXMLを作成・操作することも可能です。

以下に、「Microsoft XML, v6.0」ライブラリを使用し、XML DOMオブジェクトを作成してXMLを出力するVBAマクロのコード例を示します。

Sub ExportToXML()

    Dim tbl As ListObject
    Dim i As Long
    
    ' テーブル "MCompGrp" を選択
    Set tbl = Me.ListObjects("MCompGrp")
    
    ' XML DOMオブジェクトの初期化
    Dim xmlDoc As New MSXML2.DOMDocument60
    Dim xmlMCompGrp As IXMLDOMElement
    Dim xmlCompGrp As IXMLDOMElement
    Dim xmlNode As IXMLDOMElement
    
    xmlDoc.async = False
    xmlDoc.validateOnParse = False
    xmlDoc.resolveExternals = False
    
    ' ルート要素の作成
    Set xmlMCompGrp = xmlDoc.createElement("MCompGrp")
    xmlDoc.appendChild xmlMCompGrp
    
    ' 表のデータをXML形式に変換
    For i = 1 To tbl.ListRows.Count
        
        Set xmlCompGrp = xmlDoc.createElement("CompGrp")
        
        Set xmlNode = xmlDoc.createElement("ID")
        xmlNode.Text = tbl.DataBodyRange(i, 1).Value
        xmlCompGrp.appendChild xmlNode
        
        Set xmlNode = xmlDoc.createElement("GName")
        xmlNode.Text = tbl.DataBodyRange(i, 2).Value
        xmlCompGrp.appendChild xmlNode
        
        Set xmlNode = xmlDoc.createElement("Code")
        xmlNode.Text = tbl.DataBodyRange(i, 3).Value
        xmlCompGrp.appendChild xmlNode
        
        xmlMCompGrp.appendChild xmlCompGrp
    Next i
    
    ' XMLをファイルに出力
    Dim fileName As String
    Dim fso As Object
    Dim outputFile As Object
    
    fileName = ThisWorkbook.Path & "\output.xml"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set outputFile = fso.CreateTextFile(fileName, True, True)
    
    outputFile.Write xmlDoc.XML
    outputFile.Close
    
    MsgBox "XMLファイルが出力されました。"

End Sub

Q

XMLファイルの改行コードは"LF"である必要があるとききました。上記VBAマクロで生成されるXMLファイルは問題ないですか？

A

ご指摘の通り、XMLファイルの改行コードは通常、"LF"（\n）が推奨されています。しかし、上記のVBAマクロでは、改行コードとして "CrLf"（\r\n）が使用されています。これは、Windows環境で一般的な改行コードです。

XMLファイルの改行コードを "LF" に統一するには、以下のようにコードを修正します。

Sub ExportToXML()

    ' 他のコードは変更なし

    ' XMLをファイルに出力
    Dim fileName As String
    Dim fso As Object
    Dim outputFile As Object
    
    fileName = ThisWorkbook.Path & "\output.xml"
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set outputFile = fso.CreateTextFile(fileName, True, True)
    
    ' 改行コードを LF に変更
    Dim xmlLF As String
    xmlLF = Replace(xmlDoc.XML, vbCrLf, vbLf)
    
    outputFile.Write xmlLF
    outputFile.Close
    
    MsgBox "XMLファイルが出力されました。"

End Sub