使用vbaexcel決議xml檔案，獲取特定值到excel表

你好，我是編程新手，我正在學習它。我不懂 xml 和 VBA，但是這個任務已經分配給我在作業中。我試圖在整個互聯網上搜索以找到解決方案，但由于我的知識不足，我無法弄清楚。

我的問題是我需要從檔案 excel 中的檔案 xml 中提取特定值

<NODE >
    <ANOTHER-NODE>
      <XXX name1="value" name2="value2" name3="value3"/>
      <XXX name1="value" name2="value2" name3="value3"/>
      <XXX name1="value" name2="value2" name3="value3"/>
      <XXX name1="value" name2="value2" name3="value3"/>
      <XXX name1="value" name2="value2" name3="value3"/>
      <XXX name1="value" name2="value2" name3="value3"/>
    <ANOTHER-NODE2>
      <File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
      <File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
      <File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
      <File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
      <File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
      <File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
      <File N="xyxyxyxy" N1="xyxyxyxy1" N2="xyxyxyxy2" N3="xyxyxyxy3" N4="xyxyxyxy4"/>
    

這就像我擁有的??結構，我需要在 excel 中提取的是 all 中的“name1="value" 和 all 中的 N="xyxyxyxy"

Private Sub CommandButtonImport_Click()

Dim xmlr As Office.FileDialog
Set xmlr = Application.FileDialog(msoFileDialogFilePicker)
With xmlr
        .Filters.Clear
        .Title = "Seleziona un File XML"
        .Filters.Add "XML File", "*.xml", 1
        .AllowMultiSelect = False
        
   If .Show = True Then
        XmlFileName = .SelectedItems(1)
        
        Dim xmlDoc As MSXML2.DOMDocument60
        
        
       Set xmlDoc = CreateObject("Msxml2.DOMDocument.6.0")
        
        If xmlDoc.Load(XmlFileName) = True Then

這是我制作的結構，用于帶來 xml 檔案，但我不知道它是否正確。

PS這是我在這里的第一篇文章，我希望它可以理解。

更新

Thanks a lot for the answers, sorry for the xml file, i made it on my own cause the original have sensitive data that i cant share, at the end i found hyour tips reaally helpfull and i wrote the code that do the work, i share

Private Sub CommandButtonImport_Click()

Dim xmlr As Office.FileDialog
Set xmlr = Application.FileDialog(msoFileDialogFilePicker)
With xmlr
        .Filters.Clear
        .Title = "Seleziona un File XML"
        .Filters.Add "XML File", "*.xml", 1
        .AllowMultiSelect = False
        
   If .Show = True Then
        XmlFileName = .SelectedItems(1)
        
        
        Dim xmlDoc As MSXML2.DOMDocument60
        Dim ECU As MSXML2.IXMLDOMNodeList
        Dim File As MSXML2.IXMLDOMNodeList
        Dim Feature As MSXML2.IXMLDOMNodeList
        
        Dim NodoLista As MSXML2.IXMLDOMNode
        Dim NodoLista1 As MSXML2.IXMLDOMNode
        Dim NodoLista2 As MSXML2.IXMLDOMNode
        
        Dim i As Integer
        Dim k As Integer
        Dim l As Integer
        
        Set xmlDoc = CreateObject("Msxml2.DOMDocument.6.0")
        
        If xmlDoc.Load(XmlFileName) = True Then

        Set ECU = xmlDoc.SelectNodes("//XXX")
        On Error Resume Next
        For Each NodoLista In ECU
        
        i = i   1
                
                With ThisWorkbook.Sheets("Foglio1").Rows(i)
                    .Cells(1).Value = NodoLista.Attributes(0).NodeValue
                End With
        Next NodoLista

        Set File = xmlDoc.SelectNodes("//File")
        On Error Resume Next
        For Each NodoLista1 In File
        
        k = k   1
                With ThisWorkbook.Sheets("Foglio1").Rows(k)
                    .Cells(3).Value = NodoLista1.Attributes(0).NodeValue
                End With
        Next NodoLista1


        Set Feature = xmlDoc.SelectNodes("//Feature")
        On Error Resume Next
        For Each NodoLista2 In Feature
        
        l = l   1
                With ThisWorkbook.Sheets("Foglio1").Rows(l)
                    .Cells(5).Value = NodoLista2.Attributes(0).NodeValue
                End With
        Next NodoLista2

        
        End If
        
   End If

End With

End Sub

If i can ask another thing i would love to update this code, doing another control on the xml, i have values on theattributes that appear more then once, there is a command line to dont print in excel the same attributes more then once ?

uj5u.com熱心網友回復：

到目前為止您所嘗試的內容存在一些不一致（沒有嘗試您自己的一些代碼來獲取 xml 節點或屬性值）；我會回答只是因為以下幾點可能有助于在您查看并重新嘗試自己的代碼之前克服頻繁的啟動問題（參見上面的@TimWilliams 評論）：

• xml 結構格式不正確，因為缺少 and 的結束標記<ANOTHER-NODE>，<ANOTHER-NODE2>因此加載會失敗；這就是為什么我插入了一個TESTONLY變數以使執行可重現，并使用作為xml 字串加載的格式良好的修訂 xml 結構（通過LoadXML而不是外部檔案參考） - 您可以輕松更改該常量以通過參考其檔案名TESTONLY=FALSE來開始加載實際檔案。Load

• 似乎您想使用xml 物件的后期系結，盡管您在這里使用了Dim xmlDoc As MSXML2.DOMDocument60需要顯式庫參考“Microsoft XML，v6.0”的檔案宣告型別（所謂的早期系結）；Dim ... As Object但是，在這種方法中，我僅通過（警告：沒有智能感知）演示了物件的后期系結和宣告。

此示例代碼假設

• <NODE>作為所謂的documentElement（~“根”節點），在任何其他情況下，它都需要修改 XPath 和/或代碼。因此，在獲取具有從屬節點的節點串列之前，我會參考 DocumentElement。這是通過使用XMLDOM 方法的所謂XPath 運算式來執行的。 SelectNodes
• Name1并且始終作為子節點和每個后續兄弟N節點的第一個屬性（僅在此處：） 。請注意，這里XMLDOM 使用從零開始的索引參考屬性，例如.<ANOTHER-NODE><ANOTHER-NODE2>curNode.Attributes.Item(0)

XMLDOM（檔案物件模型）是一個跨平臺且獨立于語言的介面，將檔案視為樹結構，并允許使用自己的方法和屬性以編程方式訪問樹。

但是，您可以使用XPath 運算式的特殊語法，例如“/NODE/ANOTHER-NODE/@name1”（或者甚至*/*/@name1應用于本文中假定的檔案元素）來處理分層 xml 檔案結構中的任何邏輯部分。

示例代碼

Private Sub CommandButtonImport_Click()
'...FileDialog etc.
    Dim XmlFileName
    '... further stuff      ' << insert your FileDialog code
    '...
    Dim xmlDoc As Object    ' << Late binding XML
    Set xmlDoc = CreateObject("Msxml2.DOMDocument.6.0")

    Const TESTONLY As Boolean = True             ' << Change to your needs!
    If TESTONLY Then                             ' << Only for testing xml string
        'Define wellformed xml content string(!) to make it reproducable
        Dim wellformed As String
        wellformed = getTestContent()            ' << calls help function
        If Not xmlDoc.LoadXML(wellformed) Then        ' LoadXML; Escape if Load error!
            Debug.Print "Cannot load!": Exit Sub
        End If
    Else        ' Load a file identified by FileName as in OP
        If Not xmlDoc.Load(XmlFileName) Then           ' Load; Escape if Load rror!         
            Exit Sub
        End If
    End If
    'start writing to VB Editor's immediate window
    Debug.Print "Main Node", "Subnode", "Attrib[1]", "Value" & vbNewLine & String(70, "-")
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    'Define & apply XPath expression
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Dim XPth As String
    XPth = "*/*"
    Dim SubNodes As Object
    Set SubNodes = xmlDoc.DocumentElement.SelectNodes(XPth)
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    'Loop through each Node in the referenced subnodes
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Dim curNode As Object
    For Each curNode In SubNodes
        Debug.Print _
        curNode.SelectSingleNode("..").nodename, _
        curNode.nodename, _
        curNode.Attributes.Item(0).Name, _
        curNode.Attributes.Item(0).Text
    Next curNode
    
End Sub

幫助函式`getTestContent()

Function getTestContent()
        getTestContent = _
        "<NODE>" & _
            "<ANOTHER-NODE>" & _
                "<XXX name1='valueA' name2='value2' name3='value3'/>" & _
                "<XXX name1='valueB' name2='value2' name3='value3'/>" & _
                "<XXX name1='valueC' name2='value2' name3='value3'/>" & _
            "</ANOTHER-NODE>" & _
            "<ANOTHER-NODE2>" & _
                "<File N='xyxyxyxy1' N1='xyxyxyxy1' N2='xyxyxyxy2' N3='xyxyxyxy3' N4='xyxyxyxy4'/>" & _
                "<File N='xyxyxyxy2' N1='xyxyxyxy1' N2='xyxyxyxy2' N3='xyxyxyxy3' N4='xyxyxyxy4'/>" & _
                "<File N='xyxyxyxy3' N1='xyxyxyxy1' N2='xyxyxyxy2' N3='xyxyxyxy3' N4='xyxyxyxy4'/>" & _
                "<File N='xyxyxyxy4' N1='xyxyxyxy1' N2='xyxyxyxy2' N3='xyxyxyxy3' N4='xyxyxyxy4'/>" & _
            "</ANOTHER-NODE2>" & _
        "</NODE>"
End Function

Example TESTONLY output in VB Editor's immediate window

Main Node     Subnode       Attrib[1]     Value
----------------------------------------------------------------------
ANOTHER-NODE  XXX           name1         valueA
ANOTHER-NODE  XXX           name1         valueB
ANOTHER-NODE  XXX           name1         valueC
ANOTHER-NODE2 File          N             xyxyxyxy1
ANOTHER-NODE2 File          N             xyxyxyxy2
ANOTHER-NODE2 File          N             xyxyxyxy3
ANOTHER-NODE2 File          N             xyxyxyxy4

標籤：excel xml vba

上一篇：如何將此以屬性為中心的XML轉換為可以匯入到MS-Access中的一個表中的以元素為中心的XML？

下一篇：使用ElementTree和python獲取XMLParentID和下面的所有Childs