Can You Enhance the "Export to Spreadsheet" Action for SharePoint Lists?

I got an interesting question today from the InterTubes, and I though it would make a good post. Export to Spreadsheet

[Do you] you know of any resources/solutions to enhance the List’s “Export to Spreadsheet” function so that, once the data is opened in Excel, something would trigger VBA Macro(s) to start formatting the spreadsheet.

“Export to Spreadsheet” merely opens the Book1 standard spreadsheet, absent of any macros, etc. Might some code embedded in a List column be able to trigger the macros?

I can’t see a way to do it.

When you Export to Spreadsheet, you aren’t really getting a spreadsheet. You’re getting a file called owssvr.iqy, which Excel understands and can open. It’s called an “Internet Query File”, based on a little quick Binging.

I just saved one of those files to my drive and cracked it open to see what was in it:



What that file points to is owssvr.dll on the server, and that is where the data actually comes from. If you aren’t familiar with owssvr.dll, it provides sort of an earlier version of the RESTful services that we see in SharePoint 2010, but it’s been around since SharePoint 2003 (at least — SharePoint 2001?).

What owssvr.dll provides in turn is XML which represents the contents of the list items. That XML looks very similar to that which is returned from the GetListItems operation of the Lists Web Service, plus information which tells Excel what type of data it’s getting and what the column names are:

<?xml version="1.0" encoding="utf-8" ?>

<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
	<s:Schema id="RowsetSchema">
		<s:ElementType name="row" content="eltOnly" rs:CommandTimeout="30">
			<s:AttributeType name="ows_Attachments" rs:name="Attachments" rs:number="1">
				<s:datatype dt:type="boolean" dt:maxLength="1" />
			<s:AttributeType name="ows_LinkTitle" rs:name="Title" rs:number="2">
				<s:datatype dt:type="string" dt:maxLength="512" />
		<z:row ows_Attachments="0" ows_LinkTitle="Washington" />
		<z:row ows_Attachments="0" ows_LinkTitle="Istanbul" />
		<z:row ows_Attachments="0" ows_LinkTitle="Mexico" />
		<z:row ows_Attachments="0" ows_LinkTitle="Buenos Aires" />
		<z:row ows_Attachments="0" ows_LinkTitle="Hong Kong" />
		<z:row ows_Attachments="0" ows_LinkTitle="London" />
		<z:row ows_Attachments="0" ows_LinkTitle="Warsaw" />

So what does all this add up to? Well, I can’t see a way to “hook into” the process to invoke a macro or any other code. This is undoubtedly by design, as with many things, to keep the client machine secure in the process.

There are undoubtedly ways to do this by writing managed code to extend the functionality or create new functionality, but that’s not in my wheelhouse. Anyone have any good ideas on that?