EXCEL VBA讀取XML解析取出資料

XML檔內容如下


<Form formVersionId="5449540f">
  <FormFieldValue>
    <FieldItem fieldId="0" fieldValue="180900010" realValue="" enableSearch="True" />
    <FieldItem fieldId="1" fieldValue="台北" realValue="ec3a9bd9" enableSearch="True" />
    <FieldItem fieldId="2" fieldValue="XXXXX" />
    <FieldItem fieldId="9" fieldValue="單位" realValue="" enableSearch="True" />
    <FieldItem fieldId="5" fieldValue="台北醫院" realValue="" enableSearch="True" fillerName="XXXX" fillerUserGuid="e1aee539" fillerAccount="0122" fillSiteId="" />
    <FieldItem fieldId="7" fieldValue="2018/09/20" realValue="" enableSearch="True" fillerName="XXXXXX" fillerUserGuid="e1aee539" fillerAccount="0122" fillSiteId="" />
    <FieldItem fieldId="3" fieldValue="08:30"  enableSearch="True" fillerName="XXXX" fillerUserGuid="e1aee539" fillerAccount="0122"  />
    <FieldItem fieldId="8" fieldValue="2018/09/20" fillerName="XXXXX" />
    <FieldItem fieldId="4" fieldValue="09:00" fillerName="XXXXX" />
    <FieldItem fieldId="6" fieldValue="TEST"  fillerName="XXXXX"/>
  </FormFieldValue>
</Form>





VBA程式

            Dim objXML As New MSXML2.DOMDocument60 '啟用XML解析器

            If Not objXML.LoadXML(wkfDOC!Content) Then
                Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason
            End If
         

            Dim nodeList As MSXML2.IXMLDOMNodeList

            Set nodeList = objXML.SelectNodes("/Form/FormFieldValue/FieldItem")  '==>對應上面XML的第3層元素
            If Not nodeList Is Nothing Then
                 Dim node As MSXML2.IXMLDOMNode
                 Dim name As String
                 Dim value As String
     
                 For Each node In nodeList
                    ' Could also do node.attributes.getNamedItem("name").text
                    name = node.SelectSingleNode("@fieldId").Text    '==>元素裡面的屬性名稱
                    value = node.SelectSingleNode("@fieldValue").Text    '==>元素裡面的屬性名稱

                    Debug.Print "fileId=" & name & "   value=" & value

                 Next node
            End If