I am looking for a way to combine multiple XML files into a single spreadsheet. Each XML file when viewed in Excel will have the same header and a single row of information (mainly text based).

It is possible to import these XML files into a spreadsheet but it will only allow me to do this one file at a time. At this point we do not know whether we will be dealing with 10 files or 100.

I only have access to Excel 2003 and do not have access to the command prompt, a SQL server or the option to download an add on. I have the option of using VB but no real experience of how to fix it. I tried amending some code from here but I kept running into errors when running it.

I tried using the Data > Consolidate option but it didn't appear to work as I had hoped. The contents of the XML file are completed fields from a form and are mainly text strings.

Grateful for any help/suggestions on whether this is possible.