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.

Advertisements
Make SharePoint open Excel files on a specific Worksheet

4 thoughts on “Make SharePoint open Excel files on a specific Worksheet

  1. Heather says:

    Did you ever find a way to get the worksheet to open to a specific tab? I am messing around with this and having no luck at all…

    Like

  2. Yep, I got it to work by updating the file’s XML as described above.

    The customer wasn’t keen to do this in the end, though. It turned out the tab that “must be selected for every workbook” was different for different workbooks.

    Like

  3. Tina says:

    So i get that it should be simple to fix, but how do you do it? Basically, how do you edit the xml? I have to have a lot of files open to a specific tab but macros just aren’t the way to go. Too many of the people using the files won’t activate the content to make the macros work so it does me no good. I really like this as a possible answer, i just don’t get how to do it. Please help.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s