我正在嘗試使用 VBA 從以下 xml 檔案中提取 2 位資訊，但它們似乎真的被埋沒了，似乎無法訪問它們，我對 xml 很陌生，所以這有點超出我的范圍。我在網上找到了許多可以使用簡單的 xml 檔案進行作業的示例，但不是這個。
在下面的 xml 中，我標記了我需要提取到電子表格中的兩個資訊位 - 我需要的第一個資訊將形成一列的標題，然后第二個資訊可能會在下面出現多次。
Sub ReadXML()
Call fnReadXMLByTags
End Sub
Function fnReadXMLByTags()
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook
mainWorkBook.Sheets("Sheet1").Range("A:A").Clear
Set oXMLFile = CreateObject("Microsoft.XMLDOM")
XMLFileName = "C:\Users\xxx\Documents\TestFile.xml"
oXMLFile.Load (XMLFileName)
Set slotNodes = oXMLFile.SelectNodes("/instrument/member/list/obj/member/string")
End Function
<?xml version="1.0"?>
<instrument>
<string name="name" value="TEST" wide="true"/> <!--I NEED TO EXTRACT THIS FIRST-->
<member name="slotvisuals">
<int name="ownership" value="1"/>
<list name="obj" type="obj">
<obj class="USlotVisuals" ID="882527840">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="CUSTOM ART1" wide="true"/>
<string name="description" value="CUSTOM ART1 DESCRIPTION" wide="true"/>
<int name="group" value="0"/>
</obj>
<obj class="USlotVisuals" ID="45186017184">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 2" wide="true"/>
<string name="description" value="GROUP 2" wide="true"/>
<int name="group" value="1"/>
</obj>
<obj class="USlotVisuals" ID="882712304">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 3" wide="true"/>
<string name="description" value="GROUP 3" wide="true"/>
<int name="group" value="2"/>
</obj>
<obj class="USlotVisuals" ID="44402087248">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 4" wide="true"/>
<string name="description" value="GROUP 4" wide="true"/>
<int name="group" value="3"/>
</obj>
</list>
</member>
<member name="slots">
<int name="ownership" value="1"/>
<list name="obj" type="obj">
<obj class="PSoundSlot" ID="2271687808">
<obj class="PSlotThruTrigger" name="remote" ID="2276282784">
<int name="status" value="144"/>
<int name="data1" value="0"/>
</obj>
<obj class="PSlotMidiAction" name="action" ID="2268706176">
<int name="version" value="600"/>
<member name="noteChanger">
<int name="ownership" value="1"/>
<list name="obj" type="obj">
<obj class="PSlotNoteChanger" ID="2277009712">
<int name="channel" value="-1"/>
<float name="velocityFact" value="1"/>
<float name="lengthFact" value="1"/>
<int name="minVelocity" value="0"/>
<int name="maxVelocity" value="127"/>
<int name="transpose" value="0"/>
<int name="minPitch" value="0"/>
<int name="maxPitch" value="127"/>
</obj>
</list>
</member>
<member name="midiMessages">
<int name="ownership" value="1"/>
</member>
<int name="channel" value="-1"/>
<float name="velocityFact" value="1"/>
<float name="lengthFact" value="1"/>
<int name="minVelocity" value="0"/>
<int name="maxVelocity" value="127"/>
<int name="transpose" value="0"/>
<int name="maxPitch" value="127"/>
<int name="minPitch" value="0"/>
<int name="key" value="-1"/>
</obj>
<member name="sv">
<int name="ownership" value="2"/>
<list name="obj" type="obj">
<obj class="USlotVisuals" ID="909444640">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="CUSTOM ART1" wide="true"/>
<string name="description" value="CUSTOM ART1 DESCRIPTION" wide="true"/>
<int name="group" value="0"/>
</obj>
<obj class="USlotVisuals" ID="43544745280">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 2" wide="true"/>
<string name="description" value="GROUP 2" wide="true"/>
<int name="group" value="1"/>
</obj>
<obj class="USlotVisuals" ID="44505641184">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 3" wide="true"/>
<string name="description" value="GROUP 3" wide="true"/>
<int name="group" value="2"/>
</obj>
<obj class="USlotVisuals" ID="44402092688">
<int name="displaytype" value="1"/>
<int name="articulationtype" value="0"/>
<int name="symbol" value="73"/>
<string name="text" value="GROUP 4" wide="true"/>
<string name="description" value="GROUP 4" wide="true"/>
<int name="group" value="3"/>
</obj>
</list>
</member>
<member name="name">
<string name="s" value="THIS IS SLOT 1" wide="true"/> <!--AND THEN THIS-->
</member>
<int name="color" value="1"/>
</obj>
</list>
</member>
<member name="controller">
<int name="ownership" value="1"/>
</member>
</instrument>
uj5u.com熱心網友回復：
這應該得到您在已有的查詢中尋找的內容（這對我有用）。您應該能夠使用類似的代碼來獲取標題，具體取決于查詢應該如何進行。
Function fnReadXMLByTags()
Dim mainWorkBook As Workbook
Set mainWorkBook = ActiveWorkbook
mainWorkBook.Sheets("Sheet1").Range("A:A").Clear
Set oXMLFile = CreateObject("Microsoft.XMLDOM")
XMLFileName = "C:\Users\xxx\Documents\TestFile.xml"
oXMLFile.Load (XMLFileName)
Set slotNodes = oXMLFile.SelectNodes("/instrument/member/list/obj/member/string")
r = 2
With mainWorkBook.Sheets("Sheet1")
For Each slotNode In slotNodes
.Cells(r, 1).Value = slotNode.getAttribute("value")
r = r 1
Next slotNode
End With
End Function
uj5u.com熱心網友回復：
接受所有評論，并以@Professor Pantsless上面的答案為基礎，這里是作業代碼。
Sub ReadXML()
Call fnReadXMLByTags
End Sub
Function fnReadXMLByTags()
Dim mainWorkBook As Workbook
Dim oXMLFile As Object
Dim MapNameNode As Object
Dim MapNameNodes As Object
Dim slotNode As Object
Dim slotNodes As Object
Set oXMLFile = CreateObject("MSXML2.DOMDocument.6.0")
Set mainWorkBook = ActiveWorkbook
mainWorkBook.Sheets("Sheet1").Range("A:A").Clear
XMLFileName = "C:\Users\xxx\Documents\TestFile.xml"
oXMLFile.Load (XMLFileName)
Set slotNodes = oXMLFile.SelectNodes("/InstrumentMap/member/list/obj/member/string")
Set MapNameNodes = oXMLFile.SelectNodes("/InstrumentMap/string")
r = 2
With mainWorkBook.Sheets("Sheet1")
For Each MapNameNode In MapNameNodes
.Cells(1, 1).Value = MapNameNode.getAttribute("value")
Next MapNameNode
For Each slotNode In slotNodes
.Cells(r, 1).Value = slotNode.getAttribute("value")
r = r 1
Next slotNode
End With
End Function
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/354003.html
上一篇：將內容插入到最后一行
下一篇：如果列沒有任何大于1的值，則退出