First, I've read Excel help and worked through several on-line
tutorials on the Microsoft website in regards to XML support in Excel
2003. And, have searched web and Usenet looking for clues. So, please
forgive if this has been asked and answered; I didn't find it if it
I've got an Excel spreadsheet containing data.
I'd like to be able to export that as XML.
Not via the "save as" an
XML spreadsheet method - too much extraneous data in the resultant
output. Too difficult to parse out the unwanted stuff. Microsoft
doesn't even recommend that method.
What I'd like to do is what Microsoft recommends anyway: Define an "XML
Map" and so on.
Problem: All the documentation seems to indicate that you can define
the map (either by supplying your own xml schema or by letting Excel
create one for you), and then you have to IMPORT xml data into your
spreadsheet where it is then stored when you save it.
Well, my data's ALREADY in a spreadsheet. Is there ANY way
Excel to accept THAT DATA as being xml data without me having to (a)
re-key it all into a new spreadsheet or (b) export it out via whatever
method and convert it to "well formed" xml BY HAND so I can re-bring it
back in again?
That really, really seems kind of ludicrous!
I've tried mapping my xml map to the
existing data and it will happily
accept JUST THE ONE ROW as being xml data (and subsequently exports
just that one row).
I've tried making my data into an Excel "list" (2003 feature), and then
mapping the xml map to that. Seems to accept it. You see the fields
in the tree in the XML Source window jump around as you click on cells
in different columns in the data, so it seems to recognize the data as
being mapped in it's entirety. But, when you do to the Data menu, XML,
Export, it says "Cannot save or export XML data. The XML maps in this
workbook are not exportable."
Sigh. Come on. Surely many, many, many folks are in this position.
Don't tell me Microsoft went to all the trouble to add the level of XML
integration that they did to Excel, and negelected to provide some way
- any way - for folks to make use of their existing, legacy data?
(Without having to go through a needless conversion)
Answers, suggestions, tips welcome!!!! In advance, Thank
p.s. If you can answer the above, perhaps you can also answer this?
Say I have my XML map defined, have data that happily believes it's xml
in the worksheet, can export to "well formed" xml and so on.
Say I want to add a new column? One would think that
Excel would then
allow you to define a new node in the XML Source tree. Nope. You can
add the column, but Excel refuses to accept that new column as xml.
Any suggestions? A procedure? Am I just not getting something
obvious? Again, thank you!