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!

Works perfectly!

Thank you for your help

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.

When I follow these instructions exactly, it imports only a single file and errors out on all the other with the helpful message "No data was imported."

I have a single messge map that I've built. I have multiple xml files each containing a single message (there may be some repeating elements within the message). I can only get Excel to fill in one line of data. Presumably I could build a message map for each file (they are all essectially identical) and get them all in, but that kind of defeats the purpose; it would probably be easier to type it all in manually.

Is there any way to get Excel to do what I want? The instructions look like they should work for this, but they don't.