Excel Add A Field To An Xml Map

Question

I've a XML map on an Excel 2010 worksheet, that I refresh from the contents of an XML file on a regular basis. I recently added a new field to the XML data and wish this to show up on my XML map.

I simply want to be able to add the new field however it appears although ill have to import the whole structure again, I dont want to do this as it will require redeveloping alot of custom formating.

Any help appreciated.

Answer 1

Unfortunately there is no easy "refresh" method for schema in the XML object model in Excel at the moment. The XML Toolbox for Excel 2003 used to be able to do this, but I am not sure if this still runs in Excel 2010 (worth a try). Alternative to this is to write your own VBA code which would import your "new" schema into a new map, and then look at the existing element maps for the "old" schema and then remap these to the "new" schema, finally deleting the "old" one. Sounds a bit hairy I know, but if your schema doesn't change significantly then it could be the answer.

There is a less graceful method than this, which involves changing the schema in the workbook's underlying XML directly. If have a look inside the workbook structure under the xl folder, you will see that there is a file named xmlMaps.xml and inside this file will be a copy of your schema - you can then edit this directly (add new elements etc.) and the new fields will then show up for use in your workbook when you open it again in Excel, leaving your original cell formatting unchanged.

Answer 2

This isn't exactly what the OP asked for, but it worked in my case, so I am adding it as a probable solution (using Office 2013)

1. Right click in your ribbon and Enable Developer Tools

2. Go to the new Developer Tools section on your ribbon and click Source

3. This will open a righthand side flyout. Elements in bold are currently mapped to your columns. Note where things map to by clicking on them. In my case it was pretty straightforward with the first element mapping to column A, etc. Then right click on anything in bold and unmap it.

4. Click the XML Maps button at bottom of flyout

5. Click Add and navigate to a file or enter a URL

6. Remove the old mapping if you want

7. Highlight all your new elements and right click to assign a new mapping. Use the notes you jotted down earlier to do this. Again, my case was very straightforward since columns were merely appended to the end. If you have columns added in the middle, you would want to remap them to the end of your table.

Answer 3

I extracted the xlsx as a zip and was able to make the changes manually without having to delete and add the mappings again following the instructions on this site: http://davidovitz.blogspot.com/2010/05/howto-refresh-xml-schema-in-excel.html