We have several different "Forms" that field agents complete, part of the
completion process is having the form emailed to the office.
Somewhere a macro automatically takes these attachment forms and saves them
into a folder.
Every half hour IT have it so the system takes these reports and adds them
to the mainframe (HP Reflections).

Someone is asking me for a report to show trends and to basically compare
data. And what I basically need is to open 8500 XML sheets, get the
information from a handful of fields, for each sheet, and add them to the
list. I currently have a sheet with the complete filename in column A and the
filename without the path in column B.

What I usually do in instances like this is have a cell with "1" in it, and
do index's off this number, then have a macro open the sheet, use the data,
then close the sheet; have it change 1 to 2, and the index's change to the
next one, then open / close / etc. When I opened the first sheet manually to
try and set up a VLOOKUP() INDIRECT() the sheet opened was called "Book11"
rather than the name of the file.

Also, I was using the top option when opening the XML sheet ("As an XML
List") and not all the data was in row 2, sometimes it was staggered over
several lines, sometimes simply repeated 20 or so times...

Is there an easy way to get the same information from 8500 .xml sheets using
code/formulas?

Doesn't matter. Sorted it by using the Read Only option, which works well :)

"PaulW" wrote:

We have several different "Forms" that field agents complete, part of the
completion process is having the form emailed to the office.
Somewhere a macro automatically takes these attachment forms and saves them
into a folder.
Every half hour IT have it so the system takes these reports and adds them
to the mainframe (HP Reflections).

Someone is asking me for a report to show trends and to basically compare
data. And what I basically need is to open 8500 XML sheets, get the
information from a handful of fields, for each sheet, and add them to the
list. I currently have a sheet with the complete filename in column A and the
filename without the path in column B.

What I usually do in instances like this is have a cell with "1" in it, and
do index's off this number, then have a macro open the sheet, use the data,
then close the sheet; have it change 1 to 2, and the index's change to the
next one, then open / close / etc. When I opened the first sheet manually to
try and set up a VLOOKUP() INDIRECT() the sheet opened was called "Book11"
rather than the name of the file.

Also, I was using the top option when opening the XML sheet ("As an XML
List") and not all the data was in row 2, sometimes it was staggered over
several lines, sometimes simply repeated 20 or so times...

Is there an easy way to get the same information from 8500 .xml sheets using
code/formulas?