Excel in SharePoint and "File is Corrupt" Error

This issue has caught me out several times – but if you’re using Excel on the SharePoint server – which is common during development – you need to turn off the protected views in the ‘Trust Center’ settings of Excel:

I’ve been set straight on this issue at least twice thanks to this post by JOPX. Unless those protected views are turned off then if you download the file you will only be told the file is corrupt – even if you can download the file and open it without any problem.

Excel in SharePoint and "File is Corrupt" Error

SharePoint: Check-Out vs Lock

I had been asked to look at a SharePoint event receiver that wasn’t behaving as it should. After a document had been checked in, it would set a property and update the item. However, we were getting an error:

SPException: The file “[File Name]” is locked for exclusive use by [User]

This was unfortunate – the user was the user who’d just been making changes, and the code was running after the document had been checked in – so what gives? Well, it turns out that the message is right – the file is locked, not checked out, and a lock is not the same as a check-out.
Continue reading “SharePoint: Check-Out vs Lock”

SharePoint: Check-Out vs Lock

Outlook Plugin – Finding the ImageMSO for the ribbon

Ages ago, I wrote an Outlook plugin for saving emails (.msg files) from Outlook 2003 to SharePoint 2007. Well, the time had come to upgrade that to Outlook 2010. Broadly speaking, that went well, with one exception – what icon to show in the ribbon?

I didn’t want to go through the pain of designing my own icon (in its various different sizes), so I decided to highjack one of the office ones. The customer I was dealing with wanted the ‘Filing cabinet’ icon of the ‘Auto Archive Settings’ button.

AutoArchive settings on the ribbon

Fine, no problem, it’s built in – in it’s various sizes and resolutions. Except – what is its ID? Continue reading “Outlook Plugin – Finding the ImageMSO for the ribbon”

Outlook Plugin – Finding the ImageMSO for the ribbon

'Edit Document' requires a Windows SharePoint Services-compatible application and Microsoft Internet Explorer 6.0 or greater

We test our applications often using ‘RunAs’ to run Internet Explorer under a different session. I know that SharePoint allows you to ‘Log in as a different user’ but it doesn’t always work that well for us (i.e. you log in repeatedly and it might let you in).

One of my colleagues was trying to edit a document logged into Windows as one user, but running his IE session at a different user. Clicking on the document opened it Word in IE – despite that the Library should try and open documents in their client application. Trying to edit the document he got the message:

‘Edit Document’ requires a Windows SharePoint Services-compatible application and Microsoft Internet Explorer 6.0 or greater.

This is the same message as mentioned in KB833714, but a different cause. When I tried editing the same action but as the user he was logged into Windows as it worked fine.

'Edit Document' requires a Windows SharePoint Services-compatible application and Microsoft Internet Explorer 6.0 or greater

Office 2003 and SharePoint

One of the main selling points of Office 2007 (to me, anyway) is it’s integration with SharePoint 2007. However, customers usually seem to still be using Office 2003, and this degrades the user experience. One of the main things that irritates is Check-out/check-in policy, and users just opening things in Read-only mode.

Well, again, Ton Stegeman has a solution. As one of my colleague’s comments ‘Smart man, that’.

Office 2003 and SharePoint

Make SharePoint open Excel files on a specific Worksheet

We’ve a customer who wants Excel Workbooks in one of their libraries to always open on a specific worksheet. Normally, Excel opens from SharePoint showing whatever the last selected tab was, but they’d like theirs to always open on the first worksheet.

I was curious, so I set up a document library with a template XSLX file. In it, the 4th worksheet was the active one when I saved the workbook as the template.

I created a new document in the library with that template. When Excel opened, it showed me the 4th tab, as expected. I saved the document, and downloaded a copy. I reopened the document from SharePoint, selected the first tab, saved the document and downloaded a second copy.

So, now I have 2 XLSX file for the same workbook, but with different tabs selected. I changed their extensions to .zip, and unzipped them. Next, I ran WinMerge on the folders they unzipped to to see what the differences were.

There weren’t many really – most were related to ‘last saved time’ and things like that. There were three XML files in the archive that seemed relevant – 2 for the worksheets (the one that was selected and the one that is now), and the Workbook xml.

Here are the differences for one of the WorkSheets:

Yup, the difference is the tabSelected=1 attribute. If it there, that’s the selected tab.

Or is it? The WorkBook xml also contains a difference:

This is a little more complicated – activeTab seems to be the index number into the Sheets node, if you treat it as a zero-based array. And if the first tab is selected, there doesn’t seem to be an activeTab element at all. Still, not too bad, I’m sure I could work with that.

So, how would this help? Well, if the customer is really keen that the first tab is always selected, then we could write an event receiver that captures a document uploaded to their library and then:

  • Checks it really is an Excel 2007 file
  • If it is, opens it up
  • Edits the XML we’ve just seen
  • Resaves the file.

Pretty straight forward, really.

Make SharePoint open Excel files on a specific Worksheet

Setting up your content types and templates…

I received a good link in one of the comments on the blog, and I thought I’d bump this up – Sensible Document and Template Management.

I’m with Mads on this – to me the killer feature of SharePoint is how it works with Offices, and template functionality is key in that. Demos of document properties, quick parts and then the list columns in Sharepoint have a very high wow-factor (rightly so – I think it’s pretty neat too!)

However, templates are something rarely used in my experience, or not use properly anyway. Often it’s seen as an unnecessary effort, and usually everyone just starts creating their own documents from blank, or deleteing the contents from an existing document and using that one, or copying and pasting across – none of which are pretty scenarios.

Then again, often the guys actually making these templates need a good course of ‘How to use Word’. No so much with Excel – I think that people accept you need a bit of training to use it – but Word seems to be an issue.

Anyway, the point is, do try and plan building templates into your project. And that there is probably a consultancy opportunity in trying to generate these…

Setting up your content types and templates…

Inserting Images into Word for the Document Conversion Service

Previously, I’ve mentioned how the Document Conversion Service doesn’t extract images and about how you have to insert them as linked objects. Well, it turns out that there are easier ways to link objects in than I’d explained previously. Continue reading “Inserting Images into Word for the Document Conversion Service”

Inserting Images into Word for the Document Conversion Service