How to import multiple xml file into excel

Hi,

try this code, in order to import ALL xml files into sheet1.

 

1. Open a new wb
2. save as .xlsm (macros enabled)
3. in a regular module, paste  the following code..

note: xml files are in path "c:\folder1\folder2\xml folder" (change as needed)

 

 

**Sub From_XML_To_XL()**On Error GoTo errh

Dim myWB As Workbook, WB As Workbook

Set myWB = ThisWorkbook

Dim myPath

myPath = "c:\folder1\folder2\xml folder" '<<< change path

Dim myFile

myFile = Dir(myPath & "*.xml")

Dim t As Long

t = 1

Application.ScreenUpdating = False

Do While myFile <> ""

Set WB = Workbooks.OpenXML(Filename:=myPath & myFile)

WB.Sheets(1).UsedRange.Copy myWB.Sheets(1).Cells(t, "A")

WB.Close False

t = myWB.Sheets(1).UsedRange.Rows.Count + 2

myFile = Dir()

Loop

Application.ScreenUpdating = True

myWB.Save

Exit Sub

errh:

MsgBox "no files xml"

End Sub

 

TasosK,

Thank you for this code. It works great. Is there anyway to modify it so that the headers do not display. When I import my 1000s of XML files, the XML map header comes up each time. I would ideally like just one header in row 1 and each following row just to be the data.

If possible, I would like the first two rows only to appear one time and not duplicated for each entry. So it would look like this when imported:

Thank you for your advice and assistance!

I have the same problem, following the same instructions. 

Even trying to re-import the first file fails after the initial import.

This is really frustrating.