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

Hello,

I used this code and it works perfectly. The issue I am having now is that when I use it for my 27,000 XML files, it appears that the XML Map is not the same for every file. These are infopath files and some of them are from different versions with extra fields or rich text boxes with addtional data being imported.

Since I have so many files and further sorting and filtering of the data is becoming tedious, I need to define an XML map so that each column in the workbook is showing the same field. Right now some of the newer versions of the form have data in different order, so they are importing under a different column. All the files have the same field titles, so I am hoping that defining a xml map will solve this problem and they will all import in the same order and apend to the same map.

I would like to define an xml map in row one

ActiveWorkbook.XmlMaps.Add( _

        "C:\Users\tim\desktop\example.xml" _

        , "myFields").Name = "myFields_Map" 

and then import an entire folder of xml files that will be append to that same xml map. Keeping one file per row.

Thank you for your time and wisdom,