Import of multiple XML files

Hello,
I have a problem with an each day task:
1. Download from a set ftp a file (self extracting .exe) with set name to a set folder on my hard drive.
2. Extract the file to get a varying number (I estimate 10 - 1000 separate files) of .xml files. All the files have the same schema/mapping.
3. Check the same "tag" in each file against a set table of values (under 100 different values) -> find the files with desired values.
4. Present them in a usable format.

I was advised that all the points can be automated in Excel.
Doing 1 and 2 manually I can live with so I started with point 3.
As I have no experience with VBA and only a superficial grasp of Excel and programming I got stuck.

A. First of all I tried to use (Developer -> XML -> Import) to import all the files at once (Idea from built in help)
It does not work. I get "XML file importing error" window which lists all the files with either:
"Not able to find XML map corresponding to this data"
or "No data was imported or only a part of data was imported" <- no data in a workbook

I tried to pre-map (dragged the map from the source) the workbook. It works fine for one file only or for the first file of many.

I left it at that.

B. I used (Data -> External Data -> Other sources -> from xml data) to get the files into a workbook.
I recorded a macro to find out that I need to use

Please Login or Register  to view this content.

I just have to feed the names of the files to the function in a loop.(?)

C. I wrote some code to check what names the files from directory have and to put them in an Array

Please Login or Register  to view this content.

It fills the array with the name of the first file in the folder an infinite number of times.
As the help for "Dir" suggests I tried running Dir("") to get to the next file but with no success.

Please excuse the length of the post but I do not know if any of the above is a good idea.
Moreover my Excel is in Polish so I can only hope that I translated correctly.

I would greatly appreciate any hints, advises or solutions.

I have posted the same on another forum, but it is there for over a week with no replies, so I think I am in clear to post here.

Edit:
Sample XML files. I was not able to attach them as .xml so I cheated a bit.
266355_2011.xml.xls
266357_2011.xml.xls
266359_2011.xml.xls

hi,

I have some other way a bit similar to presented by Chippy

Please Login or Register  to view this content.

Hello Gzdnkh,

Could you attach any source XML file. I could try to test it (wgraj pliki z danymi do testu ;-)