HOWTO “Refresh” XML Schema in Excel

The short Microsoft answer is that starting Office 2007 there is no way to update the XSD Schema you imported and the only option is to Import the updated one, remove the previous and rebind tables. (takes long time)
I though to my self why is it too hard to implement ?
So for my test i created two Schema (old and new) :


The only difference is that i added one attribute.
I created two excel sheets (and binded to relevant schema)


I renamed the two XLSX files to ZIP and compare its content :) (I could have used OpenXML also)
While comparing the two files, I saw that there is specific XML file that interest me called “xmlMaps.xml”


And when I compared its content than I saw the solution:


So at the end the XSD is imported as regular text, and you can “update” it by updating the xmlMaps.xml (update your specific XSD and not others)

My next small project is to create tool to refresh Schema :)

You can download the source of this here

UPDATE 8/23/2015: Got feedback from several people about missing images, so I re-did the post and added the missing images and added source zipped

Hope this helps and saves time
HOWTO “Refresh” XML Schema in Excel HOWTO “Refresh” XML Schema in Excel Reviewed by Ran Davidovitz on 2:57 PM Rating: 5

13 comments:

Anonymous said...

Thank you thank you thank you!!!

Anonymous said...

Sweet workaround! Did you ever build the tool that does this automatically?

Anonymous said...

You are the king!!!! Thank you very much :)

Ran Davidovitz said...

Glad I could help!
I didn't write the tool at the end (time issue), also i tried using the openXML but I think its not trivial at all.

Anonymous said...

Thank you ..Thank you ..Thank you..

This saved me years of time as I used to spent time lot of time regenerating the spreadsheet.

Only thing I was facing problems with, if you have a file in .xls format it wont work.

you have to open the file and do save as .xlsx. Do not just change the extension by renaming the file then it wont work because that is the mistake I was doing.

shoban babu said...

Your solution was awesome and thanks a lot, it saved lot of rework, since I was dealing with more than 25+ templates and each template have more than 100+ cells mapped.
Shoban Babu - Mphasis an HP Company

strumpet said...

Great tip, thank you so much!

Simon

Unknown said...

Excellent solution. I thought I had to rebuild my schema mappings from scratch. Simply da bomb!

Mike said...

Saved me a ton of time as well! thanks!

heru said...

You're my hero :D
Thanks for the tip. It would be a nightmare if I don't have this workaround as I have hundreds of XML tags to be assigned if I have to it from scratch again.

Anonymous said...

This solution is Genious. Thank you so much for your solution.

Ran Davidovitz said...

Guys to let you know, I have updated the post with fresh images and source to download if needed

Derik B said...

FANTASTIC, saved me several hours of work!

Much appreciated!

Powered by Blogger.