Using Windows SharePoint Services with Excel and Access

Doing More with Microsoft Windows SharePoint Services

Importing Data to a SharePoint List from an Excel Worksheet

1	To begin creating a SharePoint list, on the Team Web Site Home page, on the top link bar, click Create.
2	To display the available list templates, on the Team Web Site Create Page, in the left pane, on the Select a View menu, click Lists.
3	To display the custom list options, click once below the scroll box on the scroll bar.
4	To create a list containing the columns and contents of an existing Excel worksheet, in the right pane, under Custom Lists, click Import Spreadsheet.
5	To enter a name for the new list, on the Team Web Site New List page, for the purposes of this exercise, press SPACEBAR, and the text will be typed for you.
6	To enter a description for the new list, on the Team Web Site New List page, click in the Description box, and press SPACEBAR to have the text typed for you.
7	To begin identifying the worksheet from which you want to import data, in the right pane, under Import from Spreadsheet, click Browse.
8	To locate and select the worksheet, in the Choose file dialog box, double-click The Garden Company, click Plant_Prices, and then click Open.
9	To link the SharePoint list to the selected worksheet, on the Team Web Site New List page, click once below the scroll box, and then click Import.
10	To begin importing a specific range of data, in the Import to Windows SharePoint Services list dialog box, click the Range Type arrow, and then click Range of Cells.
11	To begin selecting the data range to be imported, click the indicated Select Range button.
12	To select the data, in the Plant_Prices worksheet, click the A1 cell, hold down the SHIFT key, click the F8 cell, and then click the indicated button.
13	To import the selected data and create and display the SharePoint list, in the Import to Windows SharePoint Services list dialog box, click Import.

Exporting a SharePoint List to an Excel Worksheet

1	To begin exporting a SharePoint list to an Excel worksheet, on the Team Web Site Plant Prices page, in the left pane, on the Actions menu, click Export to spreadsheet.
2	To create the specified Excel Web Query file, in the File Download box, click Open.
3	To continue creating the worksheet with links to external data, in the Opening Query dialog box, click Open.
4	To begin adding data to the worksheet, in the owssvr(1) worksheet, click cell B9, and then, for the purposes of this exercise, press SPACEBAR and the data will be typed for you.
5	To finish adding the data, press ENTER.
6	On the List toolbar, click the indicated Synchronize List button.
7	To switch to the Plant Prices page of the SharePoint site, on the Windows taskbar, click the Plant Prices taskbar button.
8	To verify that the Carophyllus item has been added to the bottom of the list, on the Internet Explorer toolbar, click the indicated Refresh button.
9	To add a new item to the list, on the Plant Prices toolbar, click New Item.
10	To name and create the new item, press SPACEBAR, wait as the name is typed for you, and then on the Plant Prices: New Item toolbar, click Save and Close.
11	To switch to the Excel worksheet, on the Windows taskbar, click the Microsoft Excel – Book1 taskbar button.
12	To verify that the Pumila item has been added to the bottom of the worksheet, on the List toolbar, click the Synchronize List button.

Working with a List in Datasheet View

1	To begin displaying a list in Datasheet view, on the Team Web Site Plant Prices page, on the toolbar, click Edit in Datasheet.
2	To begin adding a list item, on the Plant Prices toolbar, click New Row.
3	To begin entering information about the item, press TAB, and for the purposes of this exercise, press SPACEBAR and wait as the information is typed for you.
4	To continue entering information about the item, press TAB, press SPACEBAR, and wait as the information is typed for you.
5	To complete the process of entering information about the item, press TAB, press SPACEBAR, wait as the information is typed for you, and then press ENTER.
6	To fit a column to its contents, in the boundary between the Brief Description and Plant Type column headings, double-click the indicated area.
7	To select a list item that you want to remove, click the indicated contorta row selector.
8	To permanently remove the selected item from the list, press the DELETE key.
9	To acknowledge the message and finish deleting the list item, in the Microsoft Internet Explorer dialog box, click Yes.
10	To begin editing a list item, click once to the right of the scroll box on the scroll bar, click ppp, press SPACEBAR, wait as the item is edited for you, and then press TAB.
11	To begin editing another list item, press SPACEBAR, wait as the item is edited for you, and then press TAB.
12	To edit another list item, press SPACEBAR, wait as the item is edited for you, and then press ENTER.
13	To display a menu of the actions you can perform with columns, click the Total column heading.
14	To begin adding a calculated column to the list, on the shortcut menu, click Add Column.
15	To enter the column heading, on the Team Web Site Plant Prices: Add Column page, press SPACEBAR, and the column heading will be typed for you.
16	To specify that the contents of the column will be calculated based on other columns, on the Plant Prices: Add Column page, click Calculated.
17	To specify the first column on which the calculation is based, click once below the scroll box on the scroll bar, and then under Optional Settings for Column, under Insert Column, double-click In Stock.
18	To finish entering the calculation, click in the Formula box after [In Stock], press SPACEBAR, and the calculation formula will be typed for you.
19	To finish creating the calculated column, click once below the scroll box on the scroll bar, and then click OK.
20	To see the new calculated column, click once to the right of the scroll box on the scroll bar.
21	To display a menu of the actions you can perform with columns, click the Total column heading.
22	To begin deleting a column, on the shortcut menu, click Edit/Delete Column.
23	To continue, on the Team Web Site Plant Prices: Change Column page, click once below the scroll box on the scroll bar, and then click Delete.
24	To acknowledge the message and finish deleting the selected column, in the Microsoft Internet Explorer dialog box, click OK.
25	To confirm that the column has been deleted, on the Team Web Site Plant Prices page, click once to the right of the scroll box on the scroll bar.
26	To display the task pane used to work with Excel 2003 and Access 2003 from within SharePoint, click the indicated Net Shade bar.
27	To view additional context-sensitive options, point to the scroll bar at the bottom of the Office links task pane.

Publishing an Excel List to a SharePoint Site

1	To begin defining data in the current worksheet as an Excel list, in the Sales_Figures worksheet, click the C4 cell.
2	To create a list from the data surrounding the selected cell, click the Data menu, point to List, and then click Create List.
3	To finish creating the list, in the Create List dialog box, click OK.
4	To begin publishing the Excel list to a SharePoint list, on the List toolbar, click the List button, and then click Publish List.
5	To specify the SharePoint site to which you want to publish the list, in the Publish List to SharePoint Site Wizard, click the Address arrow, and then click http://gardenco.
6	To create a link between the Excel list and the SharePoint list, under the Address box, click to select the Link to the new SharePoint list check box.
7	To provide a name for the SharePoint list, click the Name box, and then, for the purposes of this exercise, press SPACEBAR, and wait as the text is typed for you.
8	To provide a description for the SharePoint list, click the Description box, press SPACEBAR, wait as the text is typed for you, and then click Next.
9	To finish publishing the list, click Finish.
10	To display the new SharePoint list, in the Windows SharePoint Services dialog box, click http://gardenco/Lists/SalesFigures/Allitemsg.aspx.

Exporting Data to a SharePoint List from an Access Table

1	To display the options for working with a table, in the GardenCo : Database window, in the right pane, click PlantStockValue.
2	To begin exporting an Access table as a SharePoint list, on the shortcut menu, click Export.
3	To export the selected table as a SharePoint list, in the Export Table ‘PlantStockValue’ To dialog box, click the Save as type arrow, click three times below the scroll box on the scroll bar, and then click Windows SharePoint Services.
4	To specify the site to which you want to export the list, in the Export to Windows SharePoint Services Wizard, on the Specify site and list information page, click the Site arrow, and then click http://gardenco.
5	To create the SharePoint list with the default name and display the exported table, with the Open the list when finished check box selected, click Finish.
6	To acknowledge the message and continue, in the Microsoft Office Access dialog box, click OK.

Importing a SharePoint List to an Access Table

1	To begin importing data into an Access table, in the Gardenco : Database window, click the File menu, point to Get External Data, and then click Import.
2	To import data from a SharePoint list, in the Import dialog box, click the Files of type arrow, click twice below the scroll box on the scroll bar, and then click Windows SharePoint Services.
3	To specify the site from which you want to import data, in the Import from Windows SharePoint Services Wizard, on the Select a site page, in the Site area, click http://gardenco, and then click Next.
4	To specify the list you want to import, on the Select lists page, in the Lists box, click Plant Prices, in the Import Options area, click Import one or more lists, and then click Next.
5	To import other lists containing linked information, on the Select related lists page, click Next.
6	To import the selected lists, on the Finish page, click Finish.
7	To finish importing, in the Microsoft Office Access dialog box, click OK.
8	To begin viewing the imported table, in the GardenCo: Database window, in the right pane, double-click the Plant Prices table.

Linking an Access Table to a SharePoint List

1	To begin linking an Access table to an external data source, in the Gardenco : Database window, click the File menu, point to Get External Data, and then click Link Tables.
2	To link the table to a SharePoint list, in the Link dialog box, click the Files of type arrow, click once below the scroll box on the scroll bar, and then click Windows SharePoint Services.
3	To specify the site you want to link to, on the Select a site page of the Link to Windows SharePoint Services Wizard, in the Site area, click http://gardenco, and then click Next.
4	To specify the list you want to link to, on the Select lists page, in the Lists box, click SalesFigures, click to clear the Retrieve IDs for lookup columns check box, and then click Next.
5	To link to the default All Items view, on the Select views page, click Next.
6	To link the table to the selected list and view, on the Finish page, click Finish.
7	To acknowledge the message and continue, in the Microsoft Office Access dialog box, click OK.