Import Excel sheet as SharePoint List

Introduction

If you maintain data in a spreadsheet the columns and data can be imported to SharePoint. The imported data becomes a list. The steps are outlined below for SharePoint Foundation 2010 and Excel 2007.

Application Used

1. SharePoint 2010 Foundation

2. Microsoft Excel 2007

Steps

· Select Site Actions > More Options

· Select Import Spreadsheet and click Create

· Enter Name and optionally Description for the list

· Click Browse and select the spreadsheet to be imported onto SharePoint.

· Click Import (Your browser should support ActiveX for the import operation)

· Import to Windows SharePoint Services List is displayed

· Select the Range Type. For this example, Range of Cells is selected

· Select Range of cells to be imported

· Click Import

Errors

Do you get this error?

Solution

Modify EXPTOOWS, which is an Excel Add In file. This article has instructions with screen print to walk you through the changes required. Before making the changes .  .

1. Make  a copy of  EXPTOOWS.XLA

2. Double-click to open EXPTOOWS.XLA file. You get a blank page. Press Alt + F11 to open in code view

3. Comment out the lines not required by using  ’/

This imports the spreadsheet as a SharePoint list. Modify the view as required. The first row of the spreadsheet will be imported as columns in the SharePoint list

Housekeeping

· Remove blank rows before importing the spreadsheet as blank rows are taken as an item in the SharePoint list

· Before importing sort the columns in the desired order in the spreadsheet, to avoid doing the sort later in the SharePoint list.

· As filtered columns are not considered while importing, do not filter in the spreadsheet before import. It can be done from SharePoint.