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. 
[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:
WEB
1
http://domain/sitename/_vti_bin/owssvr.dll?XMLDATA=1&List={A533AC91-7AE5-4153-93FE-40025B8E704B}&View={49C6E728-96AD-44E4-818D-BFFC0D699443}&RowLimit=0&RootFolder=%2fsitename%2fLists%2fOffices
Selection={A533AC91-7AE5-4153-93FE-40025B8E704B}-{49C6E728-96AD-44E4-818D-BFFC0D699443}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=http://domain/sitename/_vti_bin
SharePointListView={49C6E728-96AD-44E4-818D-BFFC0D699443}
SharePointListName={A533AC91-7AE5-4153-93FE-40025B8E704B}
RootFolder=/sitename/Lists/Offices
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> <s:AttributeType name="ows_LinkTitle" rs:name="Title" rs:number="2"> <s:datatype dt:type="string" dt:maxLength="512" /> </s:AttributeType> </s:ElementType> </s:Schema> <rs:data> <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" /> </rs:data> </xml>
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?
I have some ideas based on my posts on my blog. Currently only supported in IE as far as I know.
Latest Post: http://spevilgenius.blogspot.com/2011/03/using-spservices-and-excel-to-create.html
Previous post with images:
http://spevilgenius.blogspot.com/2010/12/using-spservices-and-excel-to-create.html
These are used for formatting and making a report. I am not sure if it could be used to get macros to work, because I did not try.
Not exactly what you’re looking for, but you can use the Export Table to SharePoint List Wizard to export the spreadsheets to a custom list, and then use Excel Services to access the data within. I’m sure you can build a custom service application that extends this capability.
I used to do a lot of VBA programming in Excel, and I always preferred to have my code in one workbook that would call out to other files and pull the data in for manipulation. This allowed me alot of flexibility. In this case, i would export to excel, save that file, then use another file that contains the code to work with the newly created export file.
You can actually do this pretty easily with OfficeWriter ( http://officewriter.softartisans.com/default.aspx ).
You can use our template functionality to dump the list data into a excel file with the macros already in it! You can even just pre-format the template so there is no VBA required. I actually have a demo of this exact thing all ready to go for the SPTech Con in Boston this weekend, stop by the booth and I’ll give you a personalized tour!
I’m writing a blog post for this too and will make the project publicly available. (if you are interested in a sneak peek I’d be happy to show you over live meeting, just toss me a message on twitter: @BCJonesey)
Hi Ben:
Budget time is coming…. This will be on my wish list.
Thanks-
Charlie Epes
Buffalo, NY
Just export to a spreadsheet and copy/paste the table to a tab in any spreadsheet. The connection to sharepoint will still be there, just update the data via the Data/Update all tab in Excel or set it to update every time you update the file. Perfect when creating pivot tables from tables and using VLOOKUP to join several tables – no problems adding more than one list export to the same spreadsheet.
Hi Fredrik:
Exported to a Spreadsheet, saved it in a SP Library, added macros to it, and then used the refresh. Thanks!
Doesn’t this just work for “you” the user. I thought the tie back to Sharepoint was per user. So you could not have others see the spreadsheet. Or is this for something else?
Hi Dan:
Another user in my office just opened it successfully. All good here.
All of the suggestions seem fair, but most are not reusable or cost dough-re-mi. If you need to use macros on ANY workbook you are using, INCLUDING .iqy files, save your macro(s) to your Personal.xlsb file.
I have done extensive work in the VBA realm, specifically with Excel. I’ve pushed a modified .xlsb(countless custom macros) file to a whole corporation via login script for an ordering system before. This is the easiest way to reuse code(macros) with Excel as well.
Hi Marc,
I have a excel vba script that imports SharePoint 2010 list items to Excel 2010. Initially I used to do ‘export to excel’ in SharePoint list ribbon, that generates .iqy file but there’s a need to automate the process. The weird/strange thing is that when you do ‘export to excel’ in ribbon, the generated .iqy file even shows the values of a SharePoint field having multi selection set to it. For e.g., person or group. This iqy file when Saved as .xls file is fine. But my challenge is how do I get the field value having “Allow multi selection set to it” to excel using this code? My code completely ignores the column with ‘allow multi selection set to it. Any idea on how/where to edit my code to allow the multi selection column to show up in excel file? Thanks a lot in adv.
Sub TestMacro()
Dim objMyList As ListObject
Dim objWksheet As Worksheet
Dim strSPServer As String
Const SERVER As String = “http://abcd/”
Const LISTNAME As String = “{A486016E-80B2-44C3-8B4A-8394574B9430}”
Const VIEWNAME As String = “”
‘ The SharePoint server URL pointing to
‘ the SharePoint list to import into Excel.
strSPServer = “http://” & SERVER & “/_vti_bin”
‘ Add a new worksheet to the active workbook.
Set objWksheet = Worksheets.Add
‘ Add a list range to the newly created worksheet
‘ and populated it with the data from the SharePoint list.
Set objMyList = objWksheet.ListObjects.Add(xlSrcExternal, _ Array(strSPServer, LISTNAME, VIEWNAME),
True, , Range(“a1”))
Set objMyList = Nothing
Set objWksheet = Nothing
End Sub
dearnemo:
I think this one is a bit outsode my wheelhouse. You might want to post it to http://sharepoint.stackexchange.com.
M.
Hi Marc,
i am able to open the content of owssvr.dll in web page as XML format as you described. But the thing is that i want to search a particular data in available through Macro. Can you please help me out
hi Rahul,
How did you open the owssvr.dll in web page as XML
Mosotho:
I think I captured the XML in Fiddler if I remember correctly. It’s been a while.
M.
HI,
When i clicked on the URL ,it show like this :
WEB
1
https://portal.lsg.com/hrms/_vti_bin/owssvr.dll?XMLDATA=1&List={B30A5BBB-6369-446C-A0AA-D7AF28A67CBF}&View={619EC182-1081-4814-8997-372567C3E309}&RowLimit=0&RootFolder=
Selection={B30A5BBB-6369-446C-A0AA-D7AF28A67CBF}-{619EC182-1081-4814-8997-372567C3E309}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=https://portal.lsg.com/hrms/_vti_bin
SharePointListView={619EC182-1081-4814-8997-372567C3E309}
SharePointListName={B30A5BBB-6369-446C-A0AA-D7AF28A67CBF}
RootFolder=
But it worked properly worked in server.This issue is with the user system.
Please help me.
Nikhilp Shaju,
This problem appears because you’re using Chrome. This function only works in IE, and you must have MS Office Excel installed on your laptop.