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