11

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.

Luuklag
  • 3,897
  • 11
  • 38
  • 57
Jason
  • 607
  • 3
  • 9
  • 25

3 Answers3

6

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.

i_saw_drones
  • 3,486
  • 1
  • 31
  • 50
  • The toolbox is a nasty, it first required .NET framework 1.1, then it refused to install with an unspecified reason, so I had to manually extract the files from the msi, and when I finally got it appearing in Excel, the Reload XML Map button would display the remapping dialog, but with no schemas listed in it. Does it only work with schemas mapped to a datasource (ours aren't)? – GSerg Aug 16 '13 at 15:59
4

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

enter image description here

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

enter image description here

  1. 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.

enter image description here

  1. Click the XML Maps button at bottom of flyout

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

enter image description here

  1. Remove the old mapping if you want

  2. 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.

mrtsherman
  • 39,342
  • 23
  • 87
  • 111
  • This isn't working in Office2016 for me. The "Add" step is refusing URL entries. – mrtsherman Jan 09 '17 at 17:02
  • I used a similar workflow, which worked ok when columns were removed or added in the middle and if you don't have to many mappings: 1) delete the old XML Map 2) add the new XML Map 3) recreate the mapping for each element by individually drag and drop each element from the XML Source pane into it's column of the existing table (or into a new column). – kapex Jan 31 '18 at 11:41
4

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

Rubans
  • 4,188
  • 6
  • 40
  • 58
  • So the step is you extract the files then you update(replace the xmlMaps.xml) then what is the next step to convert it back to an excel file? – akd Oct 06 '14 at 10:44