XML mapping in Excel
 

I have hundreds of existing spreadsheets that I want to gather into a DB.
Basically it is a form that project managers fill out and return with info on
their projects. I created a schema and mapped elements to cells on one of the
existing forms. This worked great when I imported the XML in Access. However
I discovered that when I import the schema into another spreadsheet the map
does not follow and I have to map every cell again in every one of those
hundreds of spreadsheets. Is there a way to automate this or to apply a map
to the other spreadsheets? Thanks.

XML mapping in Excel
 

Hi Stu,

I have hundreds of existing spreadsheets that I want to gather into a DB.
Basically it is a form that project managers fill out and return with info on
their projects. I created a schema and mapped elements to cells on one of the
existing forms. This worked great when I imported the XML in Access. However
I discovered that when I import the schema into another spreadsheet the map
does not follow and I have to map every cell again in every one of those
hundreds of spreadsheets. Is there a way to automate this or to apply a map
to the other spreadsheets?

All Excel 2003'2 XML functionality has been exposed in the object model, so yes,
you can automate the mapping of a workbook. However, it raises that question of
why you'd want to. As you would have to open every workbook to do it, and
identify all the cells to map, it'd probably be easier to just write their
values to the Access database, rather then mapping them, exporting the XML and
importing it.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk