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
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)
While comparing the two files, I saw that there is specific XML file that interest me called “xmlMaps.xml”
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
Reviewed by Ran Davidovitz
on
2:57 PM
Rating:
Reviewed by Ran Davidovitz
on
2:57 PM
Rating:
15 comments:
Thank you thank you thank you!!!
Sweet workaround! Did you ever build the tool that does this automatically?
You are the king!!!! Thank you very much :)
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.
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.
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
Great tip, thank you so much!
Simon
Excellent solution. I thought I had to rebuild my schema mappings from scratch. Simply da bomb!
Saved me a ton of time as well! thanks!
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.
This solution is Genious. Thank you so much for your solution.
Guys to let you know, I have updated the post with fresh images and source to download if needed
FANTASTIC, saved me several hours of work!
Much appreciated!
You save my live today, thanks a million!
My colleague who showed me this blog and you both are Genius.
Post a Comment