How to import multiple xml file into excel

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?

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