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

MSI Setup projects in Visual Studio : 2005 != 2008

I’ve been working on a project that is a shared plugin to Office 2003. I was writing this in Visual Studio 2005, but for a variety of reasons (not least of which was having a tidier dev environment), I decided to move this onto a convenient VM which happened to have a lot of the things I needed (MOSS, a domain controller, AD, exchange).

Unfortunately, it also had Visual Studio 2008 too. I didn’t think this would be a problem, though, and so I when I opened the project on it for the first time and it asked me to upgrade the project to 2008, I was happy do. I didn’t think there’d be a problem.

Well, during development, there wasn’t. Whenever I went to test my MSI installer, though, I started to have problems. Installation kept failing and I kept getting the error:

Error 1937. An error occurred during the installation of assembly ‘Extensibility,Version=”7.0.3300.0″,Culture=”neutral”,PublicKeyToken=”B03F5F7F11D50A3A”,ProcessorArchitecture=”MSIL”‘. The signature or catalog could not be verified or is not valid. HRESULT: 0x80131045. assembly interface: IAssemblyCacheItem, function: Commit, component: {8C306A7E-AE8E-14F0-4168-C43060985CF4}

This was surprising. Extensibility.dll isn’t one of mine – it’s a Microsoft one that should’ve been in the Primary Interop Assemblies for Office. Anyway, it’s not one of mine, it hadn’t changed, and it was signed correctly. So what gives? Continue reading “MSI Setup projects in Visual Studio : 2005 != 2008”

MSI Setup projects in Visual Studio : 2005 != 2008

Can you sync a Pages library with Outlook?

An interesting question from one of my colleagues:

Can you sync the Pages list with an Outlook 2007 folder and still be able to read the HTML without the MOSS CSS and XSLT stuff?

Well, my gut feel was no, but I thought I’d take a look.

I found that the Connect to Outlook option was available on the Pages library settings, so I thought I’d give it a try.

Outlook showing the Pages library, but can't preview the page.

As we can see, we do have the Pages library synched, and you can see the pages within, but Outlook doesn’t know how to display them. I’m not surprised to be honest; I suspect that the default.aspx item actually just contains the data for the page – but isn’t yet rendered by the page layout or page. Still, one way to find out – open it and take a look:

An XML file that is shown when you open the Page item from Outlook

Yup, there you go – the XML of the page ListItem, and not a nice, rendered page with all the layout, master page, styles and stuff. Not really surprising, to be honest, but it would be nice if it did. I can’t think of how it might do that though – clearly the whole browser page would need to be rendered. An alternative would be to generate your pages via the Document Conversion Service, and to synch with the source documents themselves, rather than the pages.

Can you sync a Pages library with Outlook?

Outlook 2003 to SharePoint 2007 Email Integration

So what has Andy been working on lately? Well, lately I’ve been writing an integration between Outlook 2003 and SharePoint 2007. It was all a bit fraught – some of the web services I was using are a bit dodgy, uploading files and metadata as one transaction doesn’t seem to be possible, and everyone always underestimates the effort involved in coping with the metadata about an email. I’ve written an integration between GroupWise 6.5 and Open Text Livelink before now, and again, dealing with the metadata was a bigger effort than browsing the system or uploading the document.

Now, email saving into SharePoint seems to be an overlooked thing. Microsoft have their Exchange 2007 Managed Folders, which can push emails into a SharePoint Records center. Liam Cleary has written his usual high-quality description of setting it up, and the MS Records management team blogged about it in parts I, II, III and IV.

However, a lot of our customers aren’t impressed with this solution. They want to be able to save emails into particular SharePoint Libraries within their usual collaboration environment, rather than just booting them off into a Record Center.

U2U built an Outlook 2007 addin which is very nice – though a bit of a different take. Their approach requires defining the save location up front and mapping to the data, but that then saving emails is dead easy – just drag them into the folder. Nice.

Anyway, this is what I built – I’ll contrast with the U2U offering lateer…

Continue reading “Outlook 2003 to SharePoint 2007 Email Integration”

Outlook 2003 to SharePoint 2007 Email Integration

PowerPoint Slideshows (.pps) open as PowerPoint Files

Hmm. One of our salesmen called up today with an interesting problem. We’d published some PowerPoint presentations as slide shows (.pps files). If you open one of these, it should just launch PowerPoint into the presentation. However, he was opening them from our website and they were opening as a normal PowerPoint (.ppt) file. That is they were opening ready for editing, not in presentation mode. Weird.

I remembered having seen this before, but a bit of poking around showed that my fix was still in place. Curious. When I clicked on the link to the presentation I got the ‘Download or Open’ dialog thingy:

Vista's File Open or Save Dialog in Internet Explorer

First off I noticed that the file extension was .pps – good. I tried opening the file – and it opened it as if it were a .ppt file. Weird. So then I tried the other option – I saved it to my local machine and opened it locally. This time it opened correctly – it opened as a slideshow.

Hmm. So, they’re the same file, but something different is happening when they open. I wondered if it was to do with the HTTP headers, but the same difference occurs when the file is being served with the application/vnd.ms-pps or just text/html (we run MCMS 2002 on a IIS webserver but publish as static content to an Apache server. They serve the file as different mime types, but have the same result – Open opens the file for editing and Save and open shows the slideshow).

Next up, I tried the same experiment using Firefox. If you choose to Open the file rather than save it, it opens the slideshow – correctly! And if you save it, well, it saves the .pps file which, again, opens correctly.

text/html mime-type application/vnd.ms-pps mimetype
“Open” “Save” and open “Open” “Save” and open
IE7 WRONG OK WRONG OK
Firefox OK OK OK OK

So it looks like Internet Explorer will not open a .pps file as a slideshow irrespective of the mime type sent by the server and the .pps extension (and it’s file association in windows). However, if you save the file locally, it will. Weird.

The only thing that I can think is that this is some sort of security feature, but it’s a bit odd – after all, shouldn’t Firefox do this then?

PowerPoint Slideshows (.pps) open as PowerPoint Files