Monday, August 28, 2006

Using Excel to generate picklist XML for MSCRM

In a recent post, Mitch Milam asked me to provide an Excel based solution to generating the customization XML for MSCRM picklist values. So, here it is: it uses a small VBA macro to loop through a named range of cells to generate the XML (see figure).

The range name is entered into A2 ("CountryList" in the example) and this defines the set of picklist item values. The option offset number to start with is entered into B2 ("10" in the example), it becomes the value of the first generated option. Use one as the 'Start option number' when adding items to an empty picklist.

I have added a button control to run the GenerateCustomizationXml method that generates the customization XML:

Sub GenerateCustomizationXml()

Dim namedRange As range
Dim outputCell As range
Dim listName As String
Dim xml As String
Dim ctr As Integer

listName = range("A2").Text
ctr = range("B2").Text

Set namedRange = range(listName)

xml = "<options nextvalue=""" & ctr + namedRange.Cells.count & """>"

For Each listItem In namedRange.Cells

xml = xml & "<option value=""" & ctr & """><labels><label description=""" & listItem.Text & """ languagecode=""1033"" /></labels></option>"

ctr = ctr + 1


xml = xml & "</options>"

Set outputCell = range("B4")
outputCell.Value = xml

End Sub

The macro generates the XML with the correct option value numbers, calculates the nextvalue number, and puts the result into B4. Mark the B4 cell and click CTRL-C to copy it to the clipboard. Follow the steps outlined in Mitch's blog to import the new picklist values into MSCRM. Refer to the SDK for more details.

The macro is generic and can be used for any named range of cells in the worksheet. Just enter the range name in A2 and run the macro. Naming a range of cells is as easy as selecting the range of cells and typing in the name in the 'name box' in the upper left corner of the worksheet.


Alfredo Aristimuño said...

Your code was very useful for me. I made some changes to make it easer to use (at least for me). See it at my blog:

Mario Raunig said...


I developed a browserbased approach:
we just recently launched a project with loads of picklists involved - this tool was quite handy.

feel free to use it at your own risk. liability hereby disclaimed. feedback appreciated.


STH said...

I did this to update a picklist in the Lead entity and it appeared to work a treat.
Unfortunately something's not right because I cannot export the Lead customizations any more to make further changes.
Any ideas please?

Kjell-Sverre Jerijærvi said...

The code is for MSCRM as it existed 9 years ago, so I have no idea what works these days. Still, MSFT supports their software for 10 years, thus support should be available.