Loading xml data into excel

[Azhar0554]

Hi

We will get 200+ xml and each will include 100+ item details so in total 20000+ item details needs to be loaded into excel. the below code take more than an hour and the excel went to not responding.

Can anyone help me to improve this.

For sheet_x = 1 To main_loop ' Loop for multiple xml files
Sheet8.Cells(sheet_x, 2) = Path & "" & Sheet8.Cells(sheet_x, 1)
Filename = Sheet8.Cells(sheet_x, 2)


Set XMLDoc = CreateObject("Microsoft.XMLDOM")
XMLDoc.async = "false"


'Loading individual files into XMLDOC


XMLDoc.Load (Filename)
XMLDoc.setProperty "SelectionLanguage", "XPath"
XMLDoc.setProperty "SelectionNamespaces", "Companies namespace'"








Set objNodeList1 = XMLDoc.SelectNodes("/ns0:ItemMaintenance/ns0:Item")
length1 = objNodeList1.Length 'length1 is for number of items




Dim obj1, obj2 As String
Dim ItemID, DepartmentId, ItemPrice, ProductId, PLU As String




For i = 1 To length1 ' Till all items in the file


ItemID = "/ns0:ItemMaintenance/ns0:Item" & "[" & & "]" & "/arts:ItemID"

DepartmentId = "//ns0:Item" & "[" & & "]" & "/arts:MerchandiseHierarchy [@Level='Department']/@ID"
ItemPrice = "/ns0:ItemMaintenance/ns0:Item" & "[" & & "]" & "/ns0:ItemPrice"
ProductId = "//ns0:ItemMaintenance/ns0:Item" & "[" & & "]" & "/ns0:AlternativeItemID [@Name='ProductID']"
PLU = "//ns0:Item" & "[" & & "]" & "/ns0:AlternativeItemID [@Name='POSItemID']"


Set ItemIDObj = XMLDoc.SelectNodes(ItemID)
Set DepartmentIdObj = XMLDoc.SelectNodes(DepartmentId)
Set ItemPriceObj = XMLDoc.SelectNodes(ItemPrice)
Set ProductIdObj = XMLDoc.SelectNodes(ProductId)
Set PLUObj = XMLDoc.SelectNodes(PLU)

j = 0

Sheet6.Cells(l1, 4) = DepartmentIdObj(j).Text
Sheet6.Cells(l1, 8) = ItemPriceObj(j).Text
Sheet6.Cells(l1, 6) = ItemIDObj(j).Text
Sheet6.Cells(l1, 5) = ProductIdObj(j).Text
Sheet6.Cells(l1, 7) = PLUObj(j).Text
l1 = l1 + 1

Next
l1 = l1
Next

Thanks in advance
Azhar