Microsoft's SharePoint Services represents a new technology for creating and maintaining collaborative Web sites where team members can easily share information. This information can take the form of shared documents (including Excel spreadsheets, charts, and lists), calendars, to-do lists, event announcements, and other similar notices. Such a team Web site not only serves as a central repository of all information pertinent to the team but also enables team members to enter into online discussions, respond to online surveys, and review and approve documents.
You can run SharePoint Services on an internal server, on which your team site functions as a corporate intranet. If you don't have the manpower, money, or expertise to run SharePoint Services on your own Web server, you can get SharePoint Services as a subscription service through a Web Presence Provider (WPP) that hosts your team site on the Internet.
This tutorial covers how to add Excel workbooks to a SharePoint Web site for review by team members. You also find out how to publish Excel 2003 lists to a SharePoint site and link the data so that changes you make to the original list can be quickly and easily synchronized with the copy of the list residing on the SharePoint server.

Adding Excel Spreadsheets to the SharePoint Site

After you've set up your SharePoint Web site, you can easily add to the site the worksheets you want to make available to other team members. Just log on to the home page of your SharePoint Services team site and then follow these steps:
  1. Click the Documents link on the home page of your SharePoint site.
  2. If you want to create a new document library for housing the spreadsheets, click the Create Document Library link. Then on the Create Page that appears, click the Document Library link. Finally, fill in the information about the new document library on the New Document Library page and click the Create button.
    When you create a new document library, you're creating a new folder on the Web site into which you can upload the related documents to which team members need access. If you don't have many documents to share on the site, you can just put the spreadsheets in the predefined Shared Documents library.
  3. Click the Documents and Lists button on the toolbar and then, on the Documents and Lists page, click the name of the document library into which you want to upload the spreadsheets.
    To upload Excel spreadsheets into the generic Shared Documents library, click its hyperlink.
  4. On the document library's page, click the Upload Document button on the toolbar to open the Upload Document page for the document folder you selected.
  5. Upload a single document or multiple documents:
    • Single document: Click the Browse button on the Upload Document page, select the Excel workbook file in the Choose File dialog box that appears, and then click the Open button.
    • Multiple workbooks: Click the Upload Multiple Documents link on the Upload Document page, and then in the new window that appears, select the drive and folder containing the files. Finally, click the check boxes in front of their filenames.
  6. Click the Save and Close button on the Upload Document page.
  7. Click the Yes button in the Internet Explorer alert dialog box asking you if you want to continue.
    When Windows finishes uploading the files you specified, you're automatically returned to the Document Library page where the workbook file names appear.
  8. Click the Home button on the SharePoint site's toolbar to redisplay the home page.

Opening the spreadsheets on the SharePoint site

After uploading spreadsheets to the SharePoint site, other team members who have access to the site can open and view them. Be sure to e-mail all the team members ahead of time and let them know which document library contains what spreadsheets. Team members can then follow these steps to access the spreadsheets:
  1. Log on to the home page of the site and click the link for the document library that contains the uploaded workbooks.
  2. After the Document Library page opens, click the link attached to the workbook file's name.
  3. When Internet Explorer displays an alert dialog box warning that if you don't trust the source of the file you shouldn't open it, click OK.
    Internet Explorer downloads the spreadsheet to your local computer, opening it as a read-only file in Excel. When the workbook opens in your copy of Excel, the Shared Workspace task pane is also automatically displayed.
The user can then review and print the worksheet opened in Excel. If the user wants to make changes to the spreadsheet and save them, he or she must rename the workbook or save it locally in order to do so. (The read-only status prevents changes to the original document saved on the SharePoint site.)

Adding List Spreadsheets to SharePoint Site home page

To make it as easy as possible for users to access the spreadsheets, you can customize the home page of the SharePoint site by adding the document library to the main content area. When you do this, all the spreadsheets saved in a particular document library are listed in this area. A team member can open any of the workbooks listed on the home page simply by clicking the link attached to the workbook's filename.
To add a document library to the main contents area of the home page, follow these steps:
  1. Log on to the home page of your SharePoint site.
  2. Click the Site Settings button on the home page toolbar.
  3. Click the Customize Home Page link in the Customization section.
    Internet Explorer returns you to the home page, which now contains an Add Web Parts task pane on the right side. (In the SharePoint world, document libraries are considered Web parts.)
  4. In the Add Web Parts task pane, find the name of the document library you want to add and then drag it to the place in the main contents area where you want it to appear.
  5. After placing the document library - along with its list of spreadsheet files - where you want it on the home page, click the Close button in the Add Web Parts task pane to get rid of it.
After adding a document library to the home page, you as the administrator can upload new spreadsheets to it by clicking its Add New Document link at the bottom of the list of its documents.

Publishing Lists to the SharePoint Site

The steps for publishing an Excel list to your SharePoint site are exceedingly straightforward:
  1. Click any cell in the Excel list to display the List toolbar.
  2. Click the List button on the List toolbar and then select Publish List on its pop-up menu.
    The Publish List to SharePoint Site - Step 1 of 2 dialog box opens.
  3. Enter the URL of the SharePoint site in the Address combobox or, if you've published to the site previously, select the URL in the dropdown list.
  4. Select the Link to the New SharePoint List check box.
    If you don't select this check box when you publish the list, you won't be able to synchronize the data in the list on the Web site with the list in your worksheet.
  5. Enter the name for the list in the Name text box.
  6. Enter a description of the list in the Description text box and then click Next.
  7. In the Connect to Share dialog box, enter your user name and password in the appropriate boxes.
    You must be able to enter both your user ID and password to gain entrance to the SharePoint site. Also, in order to publish to the site, your user ID must have Administrator, Advanced Author, or Author status.
  8. Click OK in the Connect to Share dialog box. The Publish List to SharePoint Site - 2 of 2 dialog box appears.
    When publishing a list to a SharePoint site, each column (field) of data must use one of the recognized data types: text, number, currency, or date. Prior to uploading the list to the Web site, Excel converts the data in your list into one of these types.
  9. Verify that the fields in your list are being converted into the correct types. If the information is correct, click the Finish button to publish the list. If not, click Cancel and then make any necessary changes to the data entries in the incorrectly converted fields so that they will be correctly converted the next time you try to publish the list.
    Excel closes the Publish List to SharePoint Site - 2 of 2 dialog box, connects to the SharePoint site, and uploads each row (record) of data in the list. When the program finishes uploading the data, a Windows SharePoint Services alert dialog box appears, indicating that the list was successfully published.
  10. If you want to view the list now, click the handy link to the page in the Windows SharePoint Services alert dialog box. If you want to view the list at a later time, click OK instead.
After you enter your ID and password again to gain entrance to the SharePoint site, the page with the published list appears in Internet Explorer When you first view the list on the Web site, it appears in Datasheet view, a view that mimics the Excel worksheet by showing gridlines for the columns and rows. The column headings (field names) in this view contain filtering drop-down buttons, which you can use to sort and filter the published list just as you would any data list in Excel. If you want to view the published list as a straight list without gridlines and filtering buttons, click the Show in Standard View button on the toolbar located above the list.
To return to the original worksheet in Excel, click the Microsoft Excel button on the Windows taskbar or press Alt+Tab. When you're finished comparing the lists, close the Internet Explorer window by clicking its Close button to return to Excel.
You can view the published list on the SharePoint site at any time after publishing it simply by selecting a cell in the list, clicking the List button on the List toolbar, and then selecting View List on Server on the pop-up menu.

Synchronizing List Data

After you've published a list that you've linked to your SharePoint site, it's easy to update the changes that you make to the list in the original worksheet in Excel to the copy of the list on the Web site. All you have to do is choose Data → List → Synchronize List. Excel then connects to the SharePoint site and updates the list published there with all the changes that you've made. That's all there is to it.
To verify your updates in the list on the SharePoint site, choose Data → List → View List on Server, or click the List button on the List toolbar and then choose View List on Server on its pop-up menu. If the list is already open in Internet Explorer at the time you make the updates to the original list in Excel, switch to the Internet Explorer window and click the Refresh Data button in the list's toolbar on the Web page in order to see the modifications in the list.
If, when updating the data to the list on the Web site, Excel encounters a conflict in which one of the entries you've changed in the worksheet in Excel has also been changed by a team member on the SharePoint site, a Resolve Conflicts and Errors dialog box appears when you synchronize the lists.
This dialog box highlights the conflict between your change and another team member's change and gives you the opportunity to decide which changes to use:
To use your change and override the other team member's change, click the Retry My Changes button.
  • To go with the other team member's change instead of yours, click the Discard My Changes button.
  • If you want to leave the other team member's change on the list on the Web site and keep your changes in the local worksheet, click the Cancel button.
  • If you need to sever the tie between the local and Web version of the list, click the Unlink My List button. Just be aware that if you select this option (which you can also do by choosing Data → List → Unlink List), you can no longer have Excel automatically update your changes to the list in the local worksheet on the SharePoint site.
To remove a list that you've unlinked from the SharePoint site, go to its Web page on the site, click the Modify Settings and Columns link in the task pane on the left, and then click the Delete This List link on the Customize page for the list.

Post a Comment

Blogger Tips and TricksLatest Tips And TricksBlogger Tricks


Cloud Power For You

Website Hosting At Low Price


Empire Views