Project Advice

Project Advice

2. Reusing old data

2.2 Microsoft Excel

Microsoft Excel allows XML export, but via a slightly convoluted process. In Excel you need to install the Developer tab if it’s not already available. Then go to the Developer tab and click on Source in the XML section; now click on XML maps in the bottom right of the page and then Add; then you browse to your Schema (.xsd ) file. Then map the elements of this schema by dragging each to the relevant column. There are two ways to export: go to the Developer tab and click Export, or simply save the file as an xml file type.

If you don’t have the .xsd file that is needed you can create a dummy XML file, or use an appropriate old one; then open that file from Excel and Excel will offer to infer an .xsd file; when the XML has been imported press ALT+F11 to get into the VB screen, then CTRL+G for the immediate pane. Type in

Print ActiveWorkbook.XmlMaps(1).Schemas(1).Xml

and the schema text will be displayed and can then be pasted into a blank xsd file and used. (These instructions were tested on Excel 2007, so there may be minor differences with other versions).