Home > Software engineering >  Warrior advice please, reads the XML data is very slow.
Warrior advice please, reads the XML data is very slow.

Time:10-04

Warrior, please advice, how to use VBA to read large XML file

Problem: the XML file is bigger, the speed is slower, the code is as follows:

'read Rawdata. XML data
Private Sub OpenXml1 ()
Dim objDOM As Object
Dim n nodes, x As String
Dim I, iLength As Long
Dim j As Integer
Dim Strh () As the Variant
Strh ()=Array ("//TDX ", "//the tda", "//TDB", "//TDC", "//TDK", "//TDL", "//tdy", "//TDM", "//TDN", "//tdo", "//TDP", "//TDQ", "//tdu", "//TDV", "//td1", "//new1", "//new2")
'loading XML documents into the dom document
The Set objDOM=CreateObject (" MSXML. DOMDocument ")
ObjDOM. Load (Workbooks (" Rawdata. XLSX "). The Path & amp; "\ Rawdata XML")
ObjDOM. Async=False
Get the number of nodes in the XML data
The Set of nodes=objDOM. DocumentElement
ILength=nodes. The.childnodes. Length - 1

Dim arr () As String
To iLength ReDim arr (0, 0 To UBound (Strh))

For j=0 To UBound (Strh)
I=0
The Set of nodes=objDOM. SelectNodes (Strh (j))

For Each n nodes In
On the Error Resume Next
Arr (I, j)=n.T ext
I=I + 1
Next

Next
With Workbooks (" Rawdata. XLSX ".) Sheets (1)
The Range (A2: "Q" & amp; (iLength + 2))=arr ()
End With
The Set of nodes=Nothing
The Set obDOM=Nothing
Erase arr 'clear memory array
End Sub
  •  Tags:  
  • VBA
  • Related