create button that export custom list to excel file

Question

I'm using sharepoint 2007, custom list. from the datasheet view I can export the list into excel/access file. is it possible to create a button that proposes to export the list to excel. (in order to alleviate on the end user)

Answer 1

I needed to do this for one of my projects. Here are the steps I took.

Generate an export for the data you want to share via the out of the box process (ie via the Action Menu). When prompted, name and save this file locally. This is the Excel Query File and it has information about the site, list and view that generates this data.

Place the file in the SharePoint site. I created a separate document library for this content. Make sure you set the appropriate permissions.

On the page, place the html that launches the query file. I placed it in an html link on a publishing page but this could also exist in a content editor webpart or a link list.

<a href="../filestorage/myExportFile.iqy">Export Data</a>

This was a 2007 site and used FBA for authentication. If you are using FBA, the 'Remember Me' checkbox must be checked on the sign in page for the query to work in Excel.

Answer 2

The Actions menu of a Custom List has the "Export to Excel" button in. Are you after more than this offers? If so, what?

Bear in mind that the Export to Excel downloads an Excel Query File, which opens Excel, creates a Data Connection to the list and gets the data.

Answer 3

Another way to do it is:

1. Create a new Excel file
2. Retrieve the data from the SharePoint list using Data > Web query
3. Set the query to be refreshed when the file is opened
4. Save the file in a SharePoint document library
5. Create a button that opens the Excel file

This way, the query is inside the Excel file, which allows you to format the document and saves the end user a step.