How to import multiple xml file into excel

Hi TasosK, 

                I've been watching intently your discussion regarding the import of XML files into a single work sheet and while I've had very significant success (ie: almost there) I have a annoyance that I can't get rid of. 

If I can use two sample XML's to illustrate my issue 

XML1

{code}

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

<details>

  <movie isExtra="false" isSet="false" isTV="false">

    <title source="IMDB">Twelve Monkeys</title>

    <year Year="1990-99" source="IMDB">1995</year>

    <releaseDate source="IMDB">1996-04-19</releaseDate>

    <rating>81</rating>

    <plot source="IMDB">An unknown and lethal virus has wiped out five billion people in 1996. Only 1% of the population has survived by the year 2035, and is forced to live underground. A convict (James Cole) reluctantly volunteers to be sent back in time to 1996 to gather information about the origin of the epidemic (who he's told was spread by a mysterious "Army of the Twelve Monkeys") and locate the virus before it mutates so that scientists can study it. Unfortunately Cole is mistakenly sent to 1990, six years...</plot>

    <country source="IMDB">USA</country>

    <runtime source="MEDIAINFO">2h 4m</runtime>

    <language source="UNKNOWN">UNKNOWN</language>

    <subtitles>NO</subtitles>

    <container source="MEDIAINFO">AVI</container>

    <videoCodec>XviD</videoCodec>

    <audioCodec>MP3</audioCodec>

    <audioChannels>2</audioChannels>

    <resolution source="MEDIAINFO">576x304</resolution>

    <fps source="MEDIAINFO">25.0</fps>

    <fileSize>703 MB</fileSize>

    <genres count="3" source="IMDB">

      <genre index="Genres_Thriller_1">Mystery</genre>

      <genre index="Genres_Sci-Fi_1">Sci-Fi</genre>

      <genre index="Genres_Thriller_1">Thriller</genre>

    </genres>

    <director>Terry Gilliam</director>

    <cast count="10" source="IMDB">

      <actor>Joseph Melito</actor>

      <actor>Bruce Willis</actor>

      <actor>Jon Seda</actor>

      <actor>Michael Chance</actor>

      <actor>Vernon Campbell</actor>

      <actor>H. Michael Walls</actor>

      <actor>Bob Adrian</actor>

      <actor>Simon Jones</actor>

      <actor>Carol Florence</actor>

      <actor>Bill Raymond</actor>

    </cast>

  </movie>

</details>

{code}

 and XML2

{code}

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

<details>

  <movie isExtra="false" isSet="false" isTV="false">

    <title source="IMDB">12 Angry Men</title>

    <year Year="1950-59" source="IMDB">1957</year>

    <releaseDate source="UNKNOWN">UNKNOWN</releaseDate>

    <rating>89</rating>

    <plot source="IMDB">The defense and the prosecution have rested and the jury is filing into the jury room to decide if a young Spanish-American is guilty or innocent of murdering his father. What begins as an open and shut case of murder soon becomes a mini-drama of each of the jurors' prejudices and preconceptions about the trial, the accused, and each other. Based on the play, all of the action takes place on the stage of the jury room.</plot>

    <country source="IMDB">USA</country>

    <runtime source="MEDIAINFO">1h 36m</runtime>

    <language source="MEDIAINFO">English</language>

    <subtitles>YES</subtitles>

    <container source="MEDIAINFO">MPEG-4</container>

    <videoCodec>AVC</videoCodec>

    <audioCodec>AAC LC (en)</audioCodec>

    <audioChannels>1</audioChannels>

    <resolution source="MEDIAINFO">1200x720</resolution>

    <fps source="MEDIAINFO">23.976</fps>

    <fileSize>700 MB</fileSize>

    <genres count="1" source="IMDB">

      <genre index="Genres_Drama_1">Drama</genre>

    </genres>

    <director>Sidney Lumet</director>

    <cast count="10" source="IMDB">

      <actor>Martin Balsam</actor>

      <actor>John Fiedler</actor>

      <actor>Lee J. Cobb</actor>

      <actor>E.G. Marshall</actor>

      <actor>Jack Klugman</actor>

      <actor>Edward Binns</actor>

      <actor>Jack Warden</actor>

      <actor>Henry Fonda</actor>

      <actor>Joseph Sweeney</actor>

      <actor>Ed Begley</actor>

    </cast>

  </movie>

</details>

{code}

what I'm really looking for is to extract items such as title,runtime and IMDB rating BUT while your script above (ie: Sub Load_XML_in_a_new_Sht()) extracts all the information out of the XML ( I could naturally handle the trivial task of removing unwanted columns) , when I run the previous suggested script (ie: Convert_XML_Files()..) ..it does two things ...1) it arranges the information in a completely different series of headers 

/movie/title
12 Angry Men

which is located at column "AK"

versus 

title
Twelve Monkeys

which is located at column "D"

secondly, I've tried to use header information as generated by the script "Convert_XML_Files()" but these get ignored and nothing gets populated , I should also mention that header information is in line 2 as "/details" is located in line 1. Instead if I leave the header fields blank then everything gets populated.

                  My problem then is that the movie items such as title / runtime  are not aligned and so I filter out necessary information (process the two xmls above should illustrate this).

any update would be greatly appreciated.

Kind regards

S

Hi Again, 

            Firstly as per your suggestion I've created a schema1.xml with the following contents

{code}

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

<details>

  <movie>

    <title></title>

    <year></year>

    <releaseDate></releaseDate>

    <rating></rating>

    <plot></plot>

    <country></country>

    <runtime></runtime>

    <language></language>

    <subtitles></subtitles>

    <container></container>

    <videoCodec></videoCodec>

    <audioCodec></audioCodec>

    <audioChannels></audioChannels>

    <resolution></resolution>

    <fps></fps>

    <fileSize></fileSize>

    <genres>

      <genre></genre>

      <genre></genre>

      <genre></genre>

    </genres>

    <director></director>

    <cast>

      <actor></actor>

      <actor></actor>

      <actor></actor>

      <actor></actor>

      <actor></actor>

      <actor></actor>

      <actor></actor>

      <actor></actor>

      <actor></actor>

      <actor></actor>

    </cast>

  </movie>

</details>

{code}

This XML file imports successfully into Excel on a new active worksheet, but unfortunately I'm not able to save it again as an XML Data (*.xml) file using Excel, but I can curiously I could save it as a XML Spreadsheet 2003 (*.xml) file (I'm using Excel 2010) and naturally it could be saved as a xlsm file.

I'm not sure of your logic at this task so I haven't proceeded past this point.

Regards

S