Using Microsoft Excel with SharePoint 2007

Microsoft Excel

SharePoint lists are typically used to store the same types of data that exist in worksheets. So it should come as no surprise that Microsoft designed SharePoint 2007 to provide a high degree of interaction between SharePoint lists and Excel worksheets.

In this article, I will show you how these two products work together.

Using Excel as an editor
Just as you typically update the cells in a worksheet from time to time, SharePoint lists are updated in a similar way.

Normally, when you want to edit the data within a SharePoint list, the easiest way to get the job done is to use Datasheet view. Start by clicking the Document Center tab at the top of the page and then selecting your list from the column on the left. Next, choose the Edit In Datasheet command from the Actions menu.

When you do, the list will be displayed in a format that is similar to a worksheet, as shown in Figure A.

Figure A

Datasheet view sort of resembles a worksheet.

Although the Datasheet view looks like a worksheet, it is actually a lot more similar to the editor used by Microsoft Access. If you look in the upper-left corner of the Datasheet view, you will see that this view even includes an Access icon.

Access is great for storing large quantities of data, but it really isn't the best tool for doing a lot of computational analysis or data manipulation unless you've got a front-end application created specifically for your database. The SharePoint Web interface isn't much better for performing data analysis. You can do a few basic things, such as calculating totals, but if you want to do much more than that, you will need to export the data into Excel, which is perfect for those types of tasks.

To export the contents of a SharePoint list to an Excel worksheet, just choose the Export To Worksheet command from the Actions menu. When you do, Windows will ask whether you want to open or save the file. Click the Open button and Windows will load Excel.

When Excel loads, you may see a security warning telling you that you should not enable data connections unless you trust their source. You're seeing this warning because SharePoint creates a Web query file (an .IQY file) that it sends to Excel. Click the Enable button to go ahead and enable data connections. Your list will then be imported into Excel, as shown in Figure B.

Figure B

We have imported our list into Excel.

When you first look at the figure above, you'll probably notice the colors and formatting that have been applied to the data. This was done automatically as a part of the import process. I have not made any changes to the worksheet's formatting. This brings up a good point: You can't use Excel to edit the data that's stored in SharePoint. That's because the SharePoint data is linked to Excel through a Web query file. In this particular scenario, a Web query file facilitates only one-way operations.

This means that if you make a bunch of changes in Excel, you will have the option of saving your changes to a normal workbook file, just as you always could. You also have the option of publishing your worksheet to a SharePoint list. But you can't save your changes back to the original list.

That being the case, you may be wondering what good it does to export the data to a worksheet in the first place. What makes it so handy is that you have a live connection to the list. As the users on your network make changes to the list data, those changes can be reflected in your worksheet. Therefore, if you have set up analytical functions, your analysis can be based on the latest data.

Keep in mind that the worksheet's data is not refreshed automatically. If you look at Figure B, you will notice a Refresh button on the toolbar. Clicking Refresh updates the worksheet with the latest data from the SharePoint list.

Whenever you perform a refresh, any changes you have made to the data in the worksheet are automatically overwritten. But that isn't to say that the entire worksheet is overwritten. The data that is linked to the SharePoint list exists in the form of a table within the worksheet. Any data within the table is replaced by the data from the list when you click Refresh. Anything outside of the table is safe from being overwritten during a refresh. So if you want to create formulas or graphs, be sure to set them up outside the table's boundaries.

Of course, this raises a big question. Suppose that you're working away in Excel doing some sort of analysis on a data set, and meanwhile, there's a chance that someone is changing the data in the list. How do you know if you have the latest data in your worksheet? Well, pressing the Refresh button is the easiest way to bring in the latest data. But you also have the option of telling SharePoint to notify you by e-mail whenever changes are made to the data. If you are working in the Datasheet view, you can choose the Alert Me command from the Actions menu and set up an e-mail notification.

One last thing that I want to mention before we move on is the Unlink button (which you can see in Figure B). If you click this button, Excel will sever the connection to the SharePoint list and leave you with a standalone worksheet you can work with in the same way that you would use any other worksheet.

Other Excel tasks
So far, I've shown you how to export a SharePoint list to Excel, but you can actually perform some Excel functions on the list data without having to go through a full blown export (not that an export is difficult). If you switch to the list's Datasheet view and choose the Task Pane option from the Actions menu, SharePoint will display a series of Excel-related tasks in the browser's right pane, as shown in Figure C.

Figure C

The task pane allows you to perform a variety of Excel related tasks.

As you can see, the task pane provides four tasks:

• Query List With Excel--This is similar to exporting the worksheet to Excel.
• Print With Excel--This task exports the SharePoint list into Excel and then opens the Print dialog box.
• Chart With Excel--This task exports the SharePoint list into Excel and then opens the dialog box used to create a new chart.
• Create Excel Pivot Table Report--This Task causes the SharePoint list to be exported into Excel. Excel will then create a new pivot table using the list data.

Importing a worksheet into SharePoint
Being able to extract SharePoint list data and place it into a worksheet is nice. But sometimes, you may already have data stored in a worksheet and want to move that data into a SharePoint environment so that multiple users can collaborate on it. You actually have several options for getting the Excel data into SharePoint.

The most basic option is to create a document library and treat the worksheet as a standalone document that users can check in and out. This is probably going to be your best option if you have a lot of charts or formulas in the worksheet that you want to preserve, but it does mean that only one person will be able to modify the worksheet at a time.

Another option is to import the worksheet into SharePoint. This is actually a little bit trickier than it sounds, because you can't just export the worksheet as it is. First, you must convert the worksheet to a table. To do so, just select all of the cells that make up the portion of the worksheet you want to export. Next, go to Excel's Insert tab and click Table. Excel will display a dialog box asking you where the data is for your table. You don't have to worry about entering anything since you selected the cells ahead of time, so just click OK. Excel will place your data into a table.

Now, select Excel's Design tab if it is not already selected, click the Export button, and choose Export Table To SharePoint List. You should now see a dialog box asking you where you want to publish your table.

The first thing this dialog box asks for is a URL. Keep in mind that the table will be published as a new SharePoint list, so you need to enter the URL of the document library under which the list will be published, not the URL of an existing list.

Just beneath the Address field is a check box you can use to create a read-only connection to the new SharePoint list. If you select this check box, you'll have the option of clicking the Refresh button to see any changes that SharePoint users have made to the list's data. As was the case before, though, this is a one-way connection. Any changes you make directly to the table through Excel will be overwritten the next time that the data is refreshed.

Finally, enter a name and an optional description for your table and click Finish. After a brief delay, you should see a message indicating that the table was successfully published.

Creating a new document workspace
There's one last trick I want to show you: You can create an entire workspace based on an Excel worksheet. To do so, open your worksheet in Excel and then click the Microsoft Office button (or the jewel, or the orb, or whatever the folks in Redmond are calling it this week). Next, choose the Create Document Workspace option from the Publish menu, You will now see a pane displayed on the right side of the screen asking you the name of the new workspace and its location, as shown in Figure D.

Figure D

Excel prompts for the workspace name and location.

Click the Create button, and the new workspace will be created. When the process completes, the pane on the right will change to display several icons you can use to see members, tasks, links, and documents associated with the new workspace, as shown in Figure E. If you look at the bottom of the window, you can see that Excel incorporates functions directly related to the workspace. For example, in this figure, you can see that Excel has functions that allow you to add documents to the workspace. There is also a link you can click to open the new workspace in a browser window.

Figure E

You can manage the new workspace directly through Excel.

Conclusion
As you can see, there is a high degree of interactivity between Excel 2007 and SharePoint 2007. This interactivity helps users to better collaborate on data that was once locked into worksheets.

Microsoft Word

To print from a specific page to the end of the document, you probably enter the beginning page number and a value that's higher than the total number of pages in the document. For instance, if you want to print from page 10 to the end of the document and you know that the document has fewer than 50 pages, you might enter 10-50 in the Page Range field.

I know that's old news. But did you know that the second page number value isn't necessary anymore? The hyphen character ( - ) tells Word to print to the end of the document! Instead of entering 10-50, enter just 10-. (Older versions still need the second page number.)

The improvement hardly seems important, but the older shortcut had one disadvantage--you had to have some idea how many pages were in the document. Guessing usually worked and you could always enter some insane value like 10000, but not entering a value at all is definitely easier.

Interestingly, this technique also works for printing from the first page to a specific page. For instance, to print from the first page to page 50, enter -50.

Microsoft Access

Are your users frequently typing today's date into their forms? You can set the default properties for the date field so that they will never have to type the date again.

For example, say you use a form based on the Orders table for entering each day's orders as they are processed. Follow these steps:

1. Open the Orders table in Design View.
2. Click on the Date field.
3. In the Table Properties window, click in the Default text box and enter Date().
4. Click the drop-down arrow of the Format text box and select Short Date (Figure A).

Figure A

1. Save the table.

Now, Access will automatically fill in today's date for any new records (Figure B). Any form based on this table will also have the current date entered by default.

Figure B