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.
Andy Burns' Blog 
using a command like http://doclib/reports/example.xlsx#Sheet2 does not work, it still goes to sheet 1 (last sheet saved).
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…
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.
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.