Export to spreadsheet from SharePoint 2007 with Excel 2010

I frequently have to export data from a SharePoint list to Excel but earlier today I found it no longer works since I’ve upgraded to Office 2010.  Our SharePoint infrastructure is based on SharePoint 2007 and each time I attempted to Export to Spreadsheet from the Actions menu on the Toolbar, Excel would hang.

Luckily, I found Gustaf Lindqvist’s post on synchronising data between Excel 2010 and a SharePoint 2007 list. He suggests installing the 2007 Office System Driver Data Connectivity Components (I wasn’t getting an error message to help me find the problem – just a “Not Responding” application and the “doughnut of death”.

It’s still a bit flaky – and I’m not sure I have the complete answer (in fact, I suspect there may even be issues with some of the views in SharePoint) – but at least I can export data now…

Setting a blank value in a SharePoint workflow

I spent far too much of yesterday fighting with SharePoint workflows… including trying to cancel some that have run away on a list with almost 400 items in it…

Some idiot (initials MW) created a workflow to auto-populate a column based when a record was created or updated. Then, thinking that was successful, he created another one to update a different column. He was so pleased with himself that he didn’t stop to think of the consequences of one workflow updating an item, which would then trigger another workflow because the item had been updated… the resulting event from which would then trigger another workflow… and so on…

The long and short of it, is that I found SharePoint 2007 doesn’t have the ability to bulk remove errant (or otherwise) workflow tasks. Not from the user interface, at least – and I didn’t want to do it, twice, for each one of 377 items (there weren’t quite that many runaway workflows but it was well into three figures). I found some code on a blog post from SharePoint MVP Brian Farnhill (@BrianFarnhill) to cancel a workflow programmatically but I’ll need to put the code  into an app and I’m not really sure how to do that (my limit is probably opening Visual Studio). Besides which, I figure that the administrators of our SharePoint platform, who have probably spent the last 3 weeks wondering why it was running out of memory (or running more processes, or something similar) won’t appreciate me running unsupported code against their servers – even if it is client side…

Although it would be gratifying to create the app, in the end I settled for removing the workflow from the list, which has the side effect of ending all running workflows (unless you select the “no new instances” option). Unfortunately I still have a bunch of items that generate an error message when I try to open them – goodness knows why.

I decided to fight fire with fire and given that I’m clearly some sort of SharePoint Destroyer Designer workflow deity (not), I created another workflow to nuke all the column settings I’d created with the first two workflows (since neutered).

The logic goes like this:

Condition: If columnname is not empty
Action: Set columnname to null

Unfortunately it’s not possible to leave a null/blank value when setting field values but, like so many things in SharePoint, there is a workaround. That workaround is to set a workflow initiation parameter (imaginatively, I called mine Blank) of type single line of text, with an empty default value.

Now, the workflow action can use the value Initiation: Blank (i.e. null) to clear the contents of a column.

Unfortunately, it still hasn’t fixed the problem with the items that will not open. And, in case my manager is reading this post (which I doubt), hopefully this now puts to bed the running joke whether it’s he or I that is our team’s “SharePoint guru”. Clearly my infrastructure background does not lend itself to being a “guru” in an application platform (as I have always maintained!)…

Creating a SharePoint calculated column that uses information from a lookup column

A few months ago, Klout suggested I knew something about SharePoint. I laughed at the time but the last few weeks have seen me spending far more time working on a couple of SharePoint-based systems than I would like, so maybe Klout is less of a measure of social influence than one of future gazing…

…anyway, back to the point.

SharePoint lists have the ability to include columns that are lookups from other lists. That’s really useful when building a system with several lists of related items, for example Technologies and Vendors. I use a lookup on Name from the Vendor list to populate the available entries for a Technology item’s Product Vendor column.  That’s all fine but I also have Product Family, Product Name and Product Version columns. And then there is a Similar Products column – for which Product Name is not a clear enough lookup – I need a combination of Product Vendor, Product Family (where present), Product Name and Product Version – For example a concatenation of Microsoft + Office + SharePoint Server + 2007.

Creating a calculated column (Full Product Name) for the “in-list” (Product Family, Product Name, Product Version) is straightforward but certain column types (Lookup, Person, Group) are not available for calculated columns. I believe this is because they are based on internal SharePoint IDs, rather than the information displayed.

The workaround seems to be to create an additional text column and a workflow in SharePoint Designer that sets a value in this field.

The workflow (which I named Set Product Vendor) is a basic workflow with one step. That step has no conditions (i.e. it applies to all items) and an Update List Item action which updates the Current Item to set my new column (Product Vendor Plain Text) to the Technologies:Product Vendor value (using the fx button to select).

Now, when creating a new item the lookup is used to select an existing Product Vendor but the plain text version of the field can then be used for calculations, like the Full Product Name. The formula for my Full Product Name column is:

=IF([Product Family]="",[Product Vendor Plain Text]&" ",[Product Vendor Plain Text]&" "&[Product Family]&" ")&[Product Name]&" (version: "&[Product Version]&")"

This is used to create a single line of text.

The logic here is that a Product Family value might be empty. If it is, then I take the Product Vendor Plain Text value and add a space, if it’s not I take the Product Vendor Plain Text Value and the Product Family, adding spaces to separate, then (regardless of the previous condition), I add the Product Name and the Product Version (with some additional text around it).

Unfortunately, whilst the workflow can be triggered manually for an item (which is what I did to test it), or on item creation/update, there is no way to run the workflow on all of the existing items in a list.  But, like so many things in SharePoint, this also has a workaround. By viewing all items in SharePoint datasheet view and making a minor change to each one (for example adding an entry to a temporary column that can then be removed), this will update the item, triggering the workflow.

The bonus of all of this is that I have now updated my Similar Products column to be a lookup on Full Product Name instead of Product Name and, because it works on the item IDs, the entries are now correct, with no data cleansing required on my part.

SharePoint list migration between sites – a few lessons learned

What a week! Believe it or not, my job isn’t one as a SharePoint administrator, although sometimes it feels like it is (and recent blog posts might suggest otherwise)!

The thing about SharePoint is that even non-developers like me can string together a few webparts and lists to create something reasonably useful. Last week, one of the sites that I’m managing as part of my day job was migrated from its incubation location as a subsite of my team’s portal, to join some related content on another site. It should have been fairly straightforward, but sadly that wasn’t the case…

Updating hyperlinks

I knew that I needed to edit the hyperlinks on some of my pages but I forgot that the webparts showing views of my lists would also need to be changed, meaning that the data was actually being displayed from the old site (still left in place until I knew that the migration had been a success). I couldn’t find a way to just edit a link to the list, so I had to replace each webpart with a new version, picking up the appropriate list (and view).  That was issue number 1 sorted.

Corrupted lists with orphaned columns

Most of the lists in my site came across without issue but the largest one (30-odd columns and around 350 items) was generating an error, with the expected data replaced by “One or more field types are not installed properly. Go to the list settings page to delete these fields”. I found various articles about this, but they all seemed to relate to migrations from SharePoint 2007 to 2010.

Then, as I was digging around in the site, I found a “hidden” column that wasn’t visible in the list settings but could be seen when editing list views. The name of the column was familiar though – it matched a workflow that someone had created on the original (source) site but which wasn’t attached to the list. Apparently, when SharePoint starts a workflow on a list for the first time, it adds a workflow status column to the default view of the list  and it seems that “orphaned” workflow status columns are not unheard of. I tried creating another column with the same name (to then delete it again) but, predictably, that wasn’t possible (although creating a new workflow with the same name did create a duplicate column, which was deleted when the workflow was removed).

A little more research turned up “official” advice from Microsoft that suggested these orphaned columns couldn’t be deleted but, as is often the case, there is a way to do it with third party software and a bit of coding, as described by Nik Patel at his SharePoint World. Unfortunately, the SharePoint Manager tool that Nik used needs to be run on the server – there’s no chance of me being able to do that – but our SharePoint admins were not convinced that the orphaned column was causing the issue anyway and found some columns using lookups that were not correctly linked following the list migration. These couldn’t be edited (at least not through the SharePoint web interface), so they were deleted and recreated, before I copied the data back into them. A page refresh later and the list was displayed as expected (so the orphaned column was not the cause), although there was still one final problem…

Broken New and Edit forms

I still had an issue in that attempting to add a new item to the list resulted in “an unexpected error has occurred” messages, which are far from helpful!

A colleague had spotted that the NewForm.aspx and EditForm.aspx forms were incorrectly linked (the list had been renamed at some point and for some reason the original list name was still being used in the path to the forms) but that was easily corrected in SharePoint Designer. Even so, adding or editing an item in the list was generating errors and I was running out of ideas (as were my colleagues).

I thought that I had spotted all of the differences between the two copies of the list (source and target sites) so, I conceded defeat and started to recreate the list from scratch (before copying in the data in data sheet view – I know I can import/export via Excel, but that sometimes results in incorrect column types that can’t be edited). That’s when I found some more corrupted lookup columns. I couldn’t edit them (at least not through the SharePoint web interface) so, again, I deleted and recreated them, before repopulating the data. All of a sudden, my site was working as it should have been – hooray!


All of the problems I had were avoidable, with the benefit of hindsight, but I’m hoping not to have to migrate too many SharePoint sites in future. I expect I won’t be the last person to go through this process though and hopefully the experiences I’ve written about here will come up on a search when others are looking for help…

Creating a dashboard using a SharePoint list (part 4): bringing it all together

This week has been a bit of a SharePoint-fest as I’ve walked through the stages of creating my SharePoint dashboard. For obvious reasons, I can’t share the version I use at work, so I’ve created a mockup using the SharePoint site that comes with my Office 365 subscription. Feel free to take a look…

The three posts that explain how I built this dashboard can be found below:

As the code examples I gave use quite a lot of SharePoint formulas for calculated values, I thought it might also help to link to Microsoft’s formulas and functions documentation as well as their examples of common SharePoint formulas.

Right, that’s enough SharePoint for now… next week will be back to the usual mix of tech, marketing, photography and whatever else I stumble across…

Creating a dashboard using a SharePoint list (part 3): indicating several states of progress

Yesterday’s post looked at using ticks/crosses on my SharePoint dashboard to track progress on activities. I was quite happy with this, but my manager wanted to show progress, rather than a simple binary done/not done – and I wanted it to be visual, not text-based.

We settled on a system using a series of circles with various stages of “filling-in” (none, quarter, half, three-quarters, full) to show how far an activity had progressed, and I amended the code that I used for the ticks and crosses.

Using the same principle as with the ticks and crosses, this time the font changed from Wingdings to Arial Unicode MS, which includes the appropriate shapes under the block elements and geometric shapes subrange:

First of all, I changed my column to indicate progress from a Yes/No to a Choice with radio buttons for Not Started; Started; Additional Information Required; Almost Complete; Complete and Not Applicable.

Next I needed to adapt the formula used to calculate the correct HTML code for the display column by nesting repeated iterations of

IF([Task Progress]="Started","<DIV style='font-family:Arial Unicode MS; font-size:24px;'>?</DIV>","<DIV style='font-family:Arial Unicode MS; font-size:24px;'>?</DIV>")

Eventually this ended up as:

=IF([Task Progress]="Complete","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Started","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Additional Information Required","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Almost Complete","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>",IF([Task Progress]="Not Applicable","<DIV style='font-family:Arial; font-size:36px; color:gray;'> </DIV>","<DIV style='font-family:Arial Unicode MS; font-size:36px; color:gray;'>?</DIV>")))))

The code is not pretty, but it seems to work, and the result is something like this:

Creating a dashboard using a SharePoint list (part 2): tick/cross status indicators

In my last post, I wrote about how I used a script I found on the Internet and a couple of calculated columns to show a Red/Amber/Green (RAG) status on a list in SharePoint. I also wanted to clearly indicate which items in my list were complete, and which were still ongoing.

My inspiration for this came from Chris Bell’s posts on displaying tick marks in HTML, combined with the same code that converts my text RAG Status to HTML.

This time, I converted a Yes/No Checkbox (actually, it’s TRUE/FALSE) to HTML code for a tick or a cross in another column, using the following formula:

=IF([Task Completed]=TRUE,"<DIV style='font-family:Wingdings; font-weight:bold; font-size:24px; color:green'>ü</DIV>","<DIV style='font-family:Wingdings; font-weight:bold; font-size:24px; color:gray;'>û</DIV>")

The end result is something like this:

It needs the Wingdings font to be installed, but everyone who needs to access this list is using a Windows PC – feels like a bit of a fudge but it works for now… and can always be replaced with a graphic (as I did for the KPI indicators on the RAG status).

Creating a dashboard using a SharePoint list (part 1): colour coded status indicators

I’ve been having some fun over the last couple of weeks, developing a dashboard in SharePoint to track a number of activities that I’m co-ordinating.

Within our company there are some people who criticise SharePoint as a platform, largely because of some of the confusing messaging around some of the collaboration features and the way in which it gets mis-used as a document store (complete with folders!) but, whilst it can certainly be infuriating at times, I have worked on quite a few sites over the years where it’s struck me just how powerful the concept of a list is (I should know that really – I remember spending quite a bit of time discussing linked lists in my Computer Studies degree, but that was 20 years ago…).

I wanted to create a list with a red/amber/green status for each item – but I didn’t just want the words – I wanted colour to jump out of the page and say – this is the stuff that’s on track… and this isn’t…

Some calculated values and a script to generate HTML from text

A bit of digging around on the ‘net turned up a method that works for me – and the details are in Anjali Bharadwa’s post on colour coding custom lists and tasks:

Firstly, I  interrogate the contents of my text column (in my case this is called RAG Status, with pre-defined choices or Red, Amber and Green) and convert that to an HTML colour code in another column (called Color). The formula is  as follows:

=IF([RAG Status]="Red","red",IF([RAG Status]="Amber","orange","green"))

I created a page that includes a Web Part that displays a view on my list and used a hidden Content Editor Web Part below this, containing Anjali’s script (there’s an alternative script available from Christophe Humbert at PathToSharePoint but the download link wasn’t working on the day I needed it…), to read this and to generate HTML in another column with a calculated value (called RAG).  For SharePoint 2007, the script can be included within the HTML source for the webpart but, for SharePoint 2010, it’s necessary to link to a separate file (Adam Preston explains how to do this in his post on inserting JavaScript into a Content editor Web Part) in the This time, the formula is:

="<DIV style='font-family:Wingdings; font-weight:bold; font-size:24px; color:"&Color&";'>n</DIV>"

Using a particular font is risky (how do you know that everyone has it?) but in this case I could be sure that all of my audience would be running our corporate build with Windows and Internet Explorer. I later changed the formula to use a different symbol:

="<DIV style='font-family:Arial Unicode MS; font-size:36px; color:"&Color&";'>?</DIV>"

Sorting the RAG status

One annoyance with this approach was that the list was sorted based on my RAG Status column, but I wanted Red-Amber-Green and the alphabetical order was Amber-Green-Red. My fix for this was to change the RAG Status choices to include a number so they become: (1) Red; (2) Amber; and (3) Green.

I also tweaked the calculated value for the Color column:

=IF([RAG Status]="(1) Red","red",IF([RAG Status]="(2) Amber","orange","green"))

With this change in place, my column ordering works – and it gave me the opportunity to change the way I represent the status too…

Alternative visualisations

Path to SharePoint has some ideas for alternative visualisations for colour-coding (and a follow-up post). I particularly like the KPI indicator version, which is based on some of SharePoint’s standard images:


To use these, I changed the formula for my RAG column to:

="<DIV><IMG src='/_layouts/images/KPIDefault-"&(3-RIGHT(LEFT([RAG Status],2),1))&".gif' /></DIV>"

This change also means that the Color column is redundant, although I’ve left it in place in case I want to go back to it later.

What next

So that’s my RAG status sorted. In the next post, I’ll explain how I used this method to show ticks and crosses to indicate activity completeness.

Calculated value based on today’s date in a SharePoint column

One of my current projects involves developing an internal system to keep track of the technologies that are approved for use in our solutions.  As part of that, I’d like to set a flag on each entry so that it is regularly reviewed – for example every 90, or 180 days.

I had hoped to use a calculated column for this (i.e. today’s date plus x days) but it’s generally recognised that calculated fields only work on data entered by a user, and not from the system. Indeed, Microsoft’s own documentation for the TODAY function says:

“[…] You can use the TODAY function only as a default value; you cannot use it in a calculated column.”

Even so, I’ve had some success (with Microsoft Office SharePoint Server 2007) in setting a column type of Date and Time, in Date Only format, with a Calculated Value of =Today+90.  I’m not sure how/why this is working, given Microsoft’s statement above, but it may be worth considering.  Just be aware that your mileage may vary…

(Christophe Humbert has more information and another approach over at Path to SharePoint)

More SharePoint shenanigans

This week, I’ave mostly been working in SharePoint (those of a certain age may spot the reference to Jesse from the Fast Show?)

Earlier this month, I wrote a post with a few hints and tips I’d picked up whilst developing a site based on SharePoint.  Since then, I’ve come up against a few more barriers…

Item level permissions on document libraries

SharePoint allows administrators to set permissions on lists so that users can read and/or edit only their own items. Unfortunately, whilst that functionality is exposed in the user interface for lists, it’s not for document libraries (although it is in the object model). Basically, if you can write some code, you might be able to set the requisite permissions, but that’s beyond my abilities.  Thankfully, others have done the legwork, either in the form of a rough-and-ready utility like Matt Morse’s or Tim Larson’s Read/Write Security solution.  Either way I need to get the the changes applied to our SharePoint farm… which may well be more trouble than it’s worth…

Changing the contact for a page

Even though there is a page contact in the properties for each page (at least there is in my company – that might just be metadata that we use…), changing that property doesn’t seem to affect the page contact (which our templates show at the bottom of the page – and is also shown in a view of all pages).  The answer is to edit the page settings (in the same way as to change the page layout). Thanks to Ian Mitchell (@ianmitchell2) for setting me on the right path there…

A calculated column, with a formula based on a Yes/No field

I wanted to display a tick or a cross instead of a yes or a no in a view on one of my lists and, in order to do this I needed to create a calculated column that produced the necessary HTML (and a script to display it…). I’ll write another post about the fancy formatting but I really struggled to work out how the Yes/No is recorded (Yes/No; 1/0; TRUE/FALSE?). Exporting my list to Excel proved that SharePoint stores boolean values as TRUE/FALSE (confirmed by Peter Allen) but the trick is to leave out any quotes – if you look for =IF(Column="TRUE","This","That") it will always be negative and the outcome will be “That”.  The correct formula is =IF(Column=TRUE,"This","That").

Editing in data sheet view but the view is read-only?

I needed to perform some bulk updates on lists in SharePoint but, frustratingly, the view was marked as Read Only so I couldn’t make any edits.  I couldn’t see why this was, but googling turned up an explanation – the list was set to require content approval.  PointBeyond has more information on configuring approval in SharePoint but temporarily removing this setting allowed me to make the necessary updates, before re-enabling it.

Connecting data in web parts

Back in around 2003/2004, I remember attending a SharePoint training course where I connected a couple of webparts to work together.  For someone like myself with little or no coding skills, this was magical… and then I forgot how to do it.  Yesterday, I ran up against an issue where, partly as a result of some database design decisions by the previous  designer, I found myself unable to display the view on a list that I wanted to, as there was an implied hierarchy in the data, but the lower levels in the hierarchy link to their parent, rather than parents linking to children (it would be better still if things could work both ways…).

The workaround, albeit clunky, was to configure two webparts, each showing a view on a different list, before configuring a data connection so that the first list provided a row upon which to filter the second list.  I needed to be careful in selecting columns (i.e. the second list needs to have a column that is a lookup on the first) but, with that in place, I was able to at least show the relationships between the items in each list.