I have some code developed by a friend which imports multiple XML files into Excel (very useful tool!). However the order of the elements in the XML files is imported in random order, is there a small line of code that can be inserted to fix this?
Here is the code:
Sub ReadXML()
Dim strFile As String
MsgBox "I'll start reading please don't touch the computer"
Dim strPath As String
Dim colFiles As New Collection
Dim i As Integer
Dim wb As Workbook
strPath = ActiveSheet.Range("C2")
strFile = Dir(strPath)
While strFile <> ""
colFiles.Add strFile
strFile = Dir
Wend
If colFiles.Count > 0 Then
For i = 1 To colFiles.Count
Application.ScreenUpdating = False
Application.DisplayAlerts = False
strTargetFile = strPath & colFiles(i)
Set wb = Workbooks.OpenXML(Filename:=strTargetFile, LoadOption:=xlXmlLoadOpenXml)
Application.DisplayAlerts = True
wb.Sheets(1).UsedRange.Copy ThisWorkbook.Sheets("XMLDATA").Range("A" & i * 3 + 1)
ThisWorkbook.Sheets("XMLDATA").Rows(i * 3 + 3).Copy ThisWorkbook.Sheets("Result").Range("A" & i + 2)
wb.Close False
Application.ScreenUpdating = True
ActiveSheet.Range("P2") = i
Next i
ThisWorkbook.Sheets("XMLDATA").Rows(4).Copy ThisWorkbook.Sheets("Result").Rows(1)
ThisWorkbook.Sheets("XMLDATA").Rows(5).Copy ThisWorkbook.Sheets("Result").Rows(2)
MsgBox "I'm Done!"
End If
End Sub
Moderator Note:
Pls use code tags around your code next time as per forum rules.
Bookmarks