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

clip_image001

· 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

clip_image002

· Click Import

Errors

Do you get this error?

clip_image003

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.

Post a Comment

Previous Post Next Post