转载 VBA 操作xml



Dim xmlDoc As Object
  
Dim intLastRow As Integer
   
Dim intLoop As Integer
  
Dim strConca As String
  
Dim xmlChild As Object
 
Dim intAcc As Integer, arrTemp
  
Set xmlDoc = CreateObject("Microsoft.XMLDOM")
  
xmlDoc.async = False
 
intLastRow = Cells(Rows.Count, 1).End(xlUp).Row
  
strConca = "<?xml version='1.0' ?>" & vbCrLf & "<aaaa>"
 
ReDim arrTemp(1 To intLastRow, 1 To 3)
 
For intLoop = 1 To intLastRow
      
strConca = strConca & vbCrLf & Trim(Cells(intLoop, 1).Value)
 
Next intLoop
 
strConca = strConca & "</aaaa>"
  
xmlDoc.LoadXML (strConca)
   
intAcc = 1
   
arrTemp(intAcc, 1) = "X2Acess id"
  
arrTemp(intAcc, 2) = "macroEnbId"
   
arrTemp(intAcc, 3) = "sctpAssocRemAddr"
  
For Each xmlChild In xmlDoc.getElementsByTagName("X2Access")
 
intAcc = intAcc + 1
      
arrTemp(intAcc, 1) = xmlChild.Attributes(0).Text
   
arrTemp(intAcc, 2) = xmlChild.ChildNodes(0).ChildNodes(2).ChildNodes(0).NodeValue
  
arrTemp(intAcc, 3) = xmlChild.ChildNodes(1).ChildNodes(0).ChildNodes(1).ChildNodes(0).ChildNodes(0).NodeValue

Next
 
[c1].Resize(intAcc, UBound(arrTemp, 2)) = arrTemp