請大俠指點下，如何使用VBA讀取大xml檔案
問題：xml檔案越大，速度就越慢。代碼如下：
' 讀取Rawdata.xml資料
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 Variant
Strh() = Array("//tdx", "//tda", "//tdb", "//tdc", "//tdk", "//tdl", "//tdy", "//tdm", "//tdn", "//tdo", "//tdp", "//tdq", "//tdu", "//tdv", "//td1", "//new1", "//new2")
' 裝載xml檔案到dom 檔案中
Set objDOM = CreateObject("MSXML.DOMDocument")
objDOM.load (Workbooks("Rawdata.xlsx").Path & "\Rawdata.xml")
objDOM.async = False
' 獲取xml資料的節點數
Set nodes = objDOM.DocumentElement
iLength = nodes.ChildNodes.Length - 1
Dim arr() As String
ReDim arr(0 To iLength, 0 To UBound(Strh))
For j = 0 To UBound(Strh)
i = 0
Set nodes = objDOM.SelectNodes(Strh(j))
For Each n In nodes
On Error Resume Next
arr(i, j) = n.Text
i = i + 1
Next
Next
With Workbooks("Rawdata.xlsx").Sheets(1)
.Range("A2:Q" & (iLength + 2)) = arr()
End With
Set nodes = Nothing
Set obDOM = Nothing
Erase arr '清除陣列記憶體
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/109536.html
標籤：VBA