Excelのセル範囲に記録された内容をxml形式で出力する。

上記のテーブルをxmlファイルに変換して出力するサンプルコードを記載する。

CellRange_to_XMLFile.bas

Option Explicit

Sub CellRangeToXMLFile()
    Dim myXMLDoc As Object, myNode As Object
    Dim ConstList As Object
    Dim myRootNode As Object, tmpNode As Object
    Dim myRange As Range, i As Long, j As Long
    
    Set ConstList = New ConstClass
    Set myXMLDoc = CreateObject(ConstList.XMLCONFIG.Item("cntLIBRARY"))
    With myXMLDoc
        Set myNode = _
            .CreateProcessingInstruction(ConstList.XMLCONFIG.Item("cntXMLPREFIX"), _
                                         ConstList.XMLCONFIG.Item("cntXMLFORMAT"))
        .AppendChild myNode
    
        Set myRootNode = .CreateElement(ConstList.XMLCONFIG.Item("cntDATANAME"))
        Set myRange = Range(ConstList.XMLCONFIG.Item("cntSTARTDATA")).CurrentRegion
        For i = 2 To myRange.Rows.Count
            Set myNode = .CreateElement(ConstList.XMLCONFIG.Item("cntTABLENAME"))
            myNode.SetAttribute ConstList.XMLCONFIG.Item("cntKeyName"), _
            myRange.Cells(i, 1).Value
            For j = 2 To myRange.Columns.Count
                Set tmpNode = .CreateElement(myRange.Cells(1, j).Value)
                tmpNode.AppendChild .CreateTextNode(myRange.Cells(i, j).Value)
                myNode.AppendChild tmpNode
            Next
            myRootNode.AppendChild myNode
        Next
        .AppendChild myRootNode
        .Save ThisWorkbook.Path & ConstList.XMLCONFIG.Item("cntOUTPUTXML")
    End With
End Sub

ConstClass.cls

Option Explicit

Private xmlFile As Object

Private Sub Class_Initialize()
  Call xml_config
End Sub

Public Property Get XMLCONFIG() As Object
  Set XMLCONFIG = xmlFile
End Property


Private Sub xml_config()
  Set xmlFile = CreateObject("Scripting.Dictionary")
  xmlFile.Add Key:="cntLIBRARY", Item:="MSXML2.DOMDocument"
  xmlFile.Add Key:="cntXMLPREFIX", Item:="xml"
  xmlFile.Add Key:="cntXMLFORMAT", Item:="version=""1.0"" encoding=""UTF-8"""
  xmlFile.Add Key:="cntDATANAME", Item:="FishMarketData"
  xmlFile.Add Key:="cntSTARTDATA", Item:="A1"
  xmlFile.Add Key:="cntTABLENAME", Item:="Sales"
  xmlFile.Add Key:="cntKeyName", Item:="No"
  xmlFile.Add Key:="cntOUTPUTXML", Item:="\XMLデータ.xml"
End Sub

以下のようなxmlファイルが出力される。