How to import multiple xml file into excel

I was able to find the solution. I made it reference the second row in the XML documents due to the first row just containing /my:my:Fields

Thanks for everything,

TPearl

Sub Convert_XML_Files()

On Error GoTo errh

Const N As Long = 3  '<<< number of headers/columns in row 1,change

Dim myWB As Workbook, WB As Workbook

Set myWB = ThisWorkbook

Application.ScreenUpdating = False

Dim myPath

myPath = "C:\Users\tp62215\Desktop\New folder" '<<< change path

Dim myFile

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

Dim t As Long, r As Long, c As Long, x As Long

t = 2

Dim cH As Range

Do While myFile <> ""

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

r = WB.Sheets(1).Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

c = WB.Sheets(1).Cells.Find(What:="*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For x = 1 To N

Set cH = WB.Sheets(1).Range(Cells(1, 1), Cells(2, c)).Find(What:=myWB.Sheets(1).Cells(1, x), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious) '.Column

If Not cH Is Nothing Then

WB.Sheets(1).Range(Cells(2, cH.Column), Cells(r, cH.Column)).Copy myWB.Sheets(1).Cells(t, x)

End If

Next

WB.Close False

t = myWB.Sheets(1).Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1

myFile = Dir()

Loop

Application.ScreenUpdating = True

myWB.Save

Exit Sub

errh:

MsgBox "no files xml"

End Sub

TasosK,

Thank you again for this wonderful code. I have a few questions, as I am not getting the desired results

1. Did you mean to include the comment ".Column" half way through the code? Should it read the following instead?

Set cH = WB.Sheets(1).Range(Cells(1, 1), Cells(1, c)).Find(What:=myWB.Sheets(1).Cells(1, x), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

2. I cannot get my columns to import. If I fill out row 1 with the column headers, after running the macro the output in blank.

If I leave row one blank, I get only one column imported (Version) and repeated as many times as the Const N As Long column number. In this case it was 3

3. Here is a look into the XML file I am importing:

<?xml version="1.0" encoding="utf-8"?>

<?mso-infoPathSolution name="urn:schemas-microsoft-com:office:infopath:eGOR--Domestic-A-M:-myXSD-2013-06-24T11-56-50" solutionVersion="1.0.0.687" productVersion="12.0.0.0" PIVersion="1.0.0.0" href="URL HERE"?>

<?mso-application progid="InfoPath.Document" versionProgid="InfoPath.Document.2"?>

<my:myFields xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xhtml="http://www.w3.org/1999/xhtml" xmlns:dfs="http://schemas.microsoft.com/office/infopath/2003/dataFormSolution" xmlns:my="http://schemas.microsoft.com/office/infopath/2003/myXSD/2013-06-24T11:56:50" xmlns:xd="http://schemas.microsoft.com/office/infopath/2003" xml:lang="en-US">

 <my:Header>

  <my:Date>2013-10-16</my:Date>

  <my:Location>AAA</my:Location>

  <my:Part>558</my:Part>

  <my:Verified>Yes</my:Verified>

  <my:Pairs>AAABBB</my:CPairs>

  <my:Room>B5</my:Room>

  <my:Name>CARLOS</my:Name>

  <my:Manager>BOB</my:Manager>

  <my:Supervisor>JEN</my:Supervisor>

 </my:Header>

 <my:Version>v. AP 2.0</my:Version>

</my:myFields>

4. Here is a look at how the columns import using the first macro on this thread:

Once again, thank you for your wisdom. What will I need to place in row one to get the columns Date, Location, and Part to import for each file. I have used this code and cannot get it to work. Does the  /Header/ folder before the field name affect the import with this code?

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,