Convert xs:Enumerations in XSD to dropdown lists in Excel

Question

I have an XSD file which contains the schema for my XML. The XSD file contains an xs:Enumeration definition, which allows me to choose between 5 options as a value for one of the nodes.

Now, we want to be able to generate this data through Excel, so that non-technical people can create it.

When I import this XSD file into Excel, i want the xs:enumeration values to be listed as dropdowns.

How do I get to do that?

Edit: Starting a bounty. To win, I need a working sample code for this :)

Answer 1

I eye-balled this question a few times yesterday, and didn't answer it, partly because there is no quick solution to what you're trying to do.

You are a bit unclear on what exactly you're trying to achieve: exporting enums from Excel as a Schema, or importing enums from a Schema into Excel, or round-trip.

If you are willing to maintain your spreadsheet as a "master" copy then you can do the following:

• Main the enums in Excel (e.g. Column A has a type name and the rows in Column B have the values)
• Periodically, when you need to, hit "Save as..." XML Spreadsheet
• Run XSLT on the XML spreadsheet to produce the schema file

Well, it's an idea, at least :) Alternatively, you can write VBA code into it.

Answer 2

As xcut says, there is no easy way to do this. But it can be done. What you'll need to do is:

1. Use the WorkbookAfterXmlImport event to read your schema with MSXML for your enums.
2. Find the mapped column in your data table for the element that has your enums in code. This is usually a ListObject.
3. Add these enums to a list in Validation.InCellDropdown to your mapped column range, minus the heading.