Using the Access Web Datasheet

If you create a list by importing an Excel 2007 spreadsheet, you might find it convenient to use a spreadsheet-like environment when editing, formatting, or entering data into your newly created list. This spreadsheet-like environment is called the Access Web Datasheet, previously known as the Datasheet view, and was introduced in Chapter 4, “Working with Lists.”

Using a list or library in a datasheet can be a tremendous productivity booster. However, to make use of this feature, you must have the Microsoft Office 2003 Editions or the 2007 Microsoft Office suite installed on your computer and your browser must support Microsoft ActiveX controls. If these requirements are not met, you will receive a message indicating that the list will be displayed in Standard view. The Explorer view of a document, form, or a wiki page library does not support the Access Web Datasheet, nor does the Calendar view of Calendar lists. The following list types do not support the Access Web Datasheet.

• Discussion board

• Survey

• Picture library

• All galleries such as the Site template, List template, and Web Part galleries

• Data sources

• Inbox

The Access Web Datasheet presents all list items in a grid and facilitates editing across the entire table. A task pane on the right edge of the Access Web Datasheet enables powerful integration between Windows SharePoint Services, Office Excel 2003, Excel 2007, Microsoft Office Access 2003, and Microsoft Office Access 2007.

In the following exercise, you will add a new list item, edit an existing list item, remove an existing list item, add a list field, and briefly explore the task pane.

OPEN the SharePoint site to which you imported data from the Excel 2007 spreadsheet. Remember to use your SharePoint site location in place of http://wideworldimporters in the following exercises. If prompted, type your user name and password, and click OK.

BE SURE TO verify that you have sufficient rights to contribute to the list. If in doubt, see the Appendix on page 435. Also, complete the first exercise in this chapter before proceeding.

1. On the Quick Launch, under Lists, click Furniture Price List.

2. Click Actions.

3. From the drop-down list, click Edit in Datasheet to change the display from Standard View.

The standard view changes to the Access Web Datasheet.

Tip 

An Access Web Datasheet consists of rows, and each row corresponds to a list item. A column corresponds to a list field. The down arrow in the column headings is used to filter and sort data. Using the Access Web Datasheet is like editing a table in Excel 2007 or Office Access 2007. In the previous version of Windows SharePoint Services, the Access Web Datasheet was based on Office Excel technologies. When using SharePoint 3.0, the Access Web Datasheet is reliant on Access 2007. An Access icon appears in the top left corner of the Access Web Datasheet, and the words Access Web Datasheet display in the status bar.

When you type a value in a cell or choose a value from a drop-down list, an Edit icon displays to the far left indicating that changes have been made to the row. You can use the cursor keys or mouse to move from cell to cell to make changes to any row in the list.

When you leave a row that was changed or navigate away from the Web page, a Saving icon indicates that SharePoint is updating the database with your changes. You can continue making changes to different rows, and SharePoint saves your changes in the background. If your changes result in a conflict or error, the Access Web Datasheet will be updated with information necessary to resolve the conflict or error.

The last row in the Access Web Datasheet is always empty, and there is a New Item icon in the left column. This empty row can be used to add additional list items to the list. The Access Web Datasheet does not display the star (*) row for a document library.

Tip 

All list items in the list are displayed on one Web page in the Access Web Datasheet regardless of the size of the list. Therefore, it isn’t practical to edit extremely large lists by using the Access Web Datasheet. To improve the performance of an Access Web Datasheet, create a view and hide unnecessary columns, but do not remove those columns that are marked as Required. Apply one or more filters to hide rows that are not relevant to the view.

4. In the last row of the list, in the Furniture Name column, type Bench and press . Type Woodland, press , and type s.

Like Excel 2007 or Access 2007, the IntelliSense feature displays other values that occur in this column.

5. Press the to choose steel, and then press .

The new item row changes to become the currently edited row, and another new item row is added to the bottom of the list.

Tip 

As in other 2007 Microsoft Office suite applications, you can use Ctrl + X to cut, Ctrl + C to copy, Ctrl + V to paste, Ctrl + Z to undo the last changes, and Esc to cancel an edit on the current list item.

6. Position the mouse cursor on the boundary between the Furniture Range and Material columns until the vertical I-beam icon appears.

7. Drag the column boundary to reduce the size of the Furniture Range column.

Tip 

Rows can be resized in much the same way by using the horizontal I-beam icon between the rows. Both columns and rows can be resized based on their content by double-clicking. You can also reorder columns by simply dragging them to the desired location.

8. Click the far left cell to highlight the entire Armchair list item.

Tip 

You can select and then delete multiple list items. Alternatively, while highlighted, you can copy and paste the list items into 2007 Microsoft Office suite programs. You can select the entire list with a single click in the top-left corner cell, where the list select icon appears.

Tip 

By holding down the Shift key while selecting the far left cell so that the row select pointer appears and then clicking the far left cell of another row, you can select all rows between the first row clicked and the second row clicked. Similarly, holding down the Shift key while selecting the top row of a column so that the column select pointer appears and then clicking the top row of another column will select all columns between the two clicks. However, holding down the Ctrl key while selecting does not select and deselect list items regardless of whether they are adjacent, as you might expect. Instead, it exclusively selects the clicked row and abandons all other selections just as if you had clicked the items without holding down the Ctrl key.

9. Press the key to permanently remove the list item from the list. A delete confirmation dialog box appears.

10. Click Yes to finish deleting the list item and redisplay the Access Web Datasheet of the list.

11. Click the fifth cell in the In Stock column. Overtype ppp with 10, and then press .

12. Replace $64 with 65, and then press .

The list item is saved to the database, and a dollar ($) sign is placed before the number 65 because the Unit Price column is a Currency type.

13. Replace #VALUE! with 250, and then press .

The list item is saved to the database, but no dollar ($) sign is placed before the number 250. When the spreadsheet was imported in the previous exercise, the Single Line Of Text column type was used to create the In Stock and Total columns because they did not contain data of one particular type, whereas the Unit Price column contained only currency values. In the Excel 2007 spreadsheet, the Total column was a calculated column. To provide the same functionality in the SharePoint list, the column needs to be a Calculated data type. You cannot change the data type of an existing column to a Calculated data type, but must instead create a new column by using the Calculated data type.

14. Right-click the Total column, and then click Add Column in the context menu.

The Create Column: Furniture Price List page appears.

15. In the Name and Type area, in the Column Name textbox, type Total Cost, and then select the Calculated (calculation based on other columns) option.

16. In the Additional Column Settings area, under the Insert Column, double-click In Stock.

17. Scroll to the bottom of the page and click OK.

18. Click the first cell in the Total Cost column, type =[In Stock]*[Unit Price]*1.1, and then press .

A dialog box appears warning you that the results of your calculation change could take some time.

19. Click Continue. The Access Web Datasheet status bar displays the formula that you typed.

Important  

Formulas are equations that perform calculations on values in the list and are similar to the formulas you use in Excel 2007 and Access 2007. They can contain functions, column references, operators, and constants. For example, a formula could be =PI()*[In Stock]^2, where PI() is a function, * and ^ are operators, [In Stock] is a column reference, and 2 is a constant.

20. Click the vertical bar on the far right side of the Access Web Datasheet page to expose the task pane, which allows you to quickly integrate with Excel 2007 and Access 2007.

At the top of the task pane is a series of buttons for common commands such as Cut, Copy, Paste, Undo, Custom Sort, Remove Filter/Sort, and Help.

Note 

More information about the Access Web Datasheet can be found by clicking the Help icon in the task pane, clicking the Help link in the status bar of the Access Web Datasheet, clicking a cell in the Access Web Datasheet and pressing F1, or right-clicking any cell inside the Access Web Datasheet and clicking Help.

CLOSE the browser.