facebook google plus twitter
Webucator's Free SharePoint 2013 Tutorial

Lesson: Office Integration

Welcome to our free SharePoint 2013 tutorial. This tutorial is based on Webucator's SharePoint 2013 End User Training course.

One of the nice features of SharePoint is its ability to integrate with Microsoft Office applications. Just about every Office application offers some level of integration with SharePoint whether it be simple, such as using an Excel spreadsheet to create a new list, or more full featured, such as Outlook integration. Some integration features enhance what is available online through a browser while others allow access to SharePoint content offline such as a mobile computer environment. This lesson will use a series of walk-throughs and exercises to show how each Office application can integrate with SharePoint.

Lesson Goals

  • Learn how to create a list from an Excel spreadsheet.
  • Learn to update a spreadsheet view of SharePoint list data.
  • Learn how to create an alert.
  • Learn how to subscribe and view an RSS feed from a SharePoint list.
  • Learn how to make a copy of a library in Outlook.
  • Learn how to use the Datasheet view.
  • Learn how to open and edit a list in Access.

Excel Integration

Import Excel Spreadsheet to List

This walk-through will show you how to import data from an Excel spreadsheet and create a new SharePoint list.

  1. Create a new list by using the Import Spreadsheet template.
    1. Click the Settings menu and then choose the Add an app link.Site Actions menu More Options circled
    2. Locate the Import Spreadsheet App template and click the link. Create dialog form
    3. In the new list form that opens enter Golf Clubs in the Name field and List of golf clubs and their average loft angle and distances in the Description field.Import spreadsheet name form
    4. Click the Browse button to select the spreadsheet for the import.
    5. In the Choose File to Upload dialog window, browse to the folder you downloaded the class files to and select the club-specs.xlsx file. Click the Open button to close the dialog window and complete the selection.
    6. Click the Import button. This should open Microsoft Excel with the selected file loaded. A second dialog window titled Import to Windows SharePoint Services list should also open as shown in the following image.Import to Windows SharePoint Services list dialog
    7. In the Range Type drop-down, select Named Range and in the Select Range drop-down field choose ClubData from the list.Named range dialog

      Optionally you could choose Range of Cells from the Range Type field. This would then allow you to use your mouse to click and drag across your spreadsheet selecting the range of cells to import. Having a named table as you do in this example makes it a bit easier to just select the table.

    8. Click the Import button to complete the import process.
  2. Add the new imported list to the Quick Launch menu.
    1. Note how the new list "Golf Clubs" does not have a link in the Quick Launch menu. The Import Spreadsheet template you used does not have an option to add the list to the Quick Launch menu as part of the import and creation process.
    2. Click on the List tab to open the toolbar and click the List Settings link button. List toolbar
    3. Click the List name, description and navigation link under the General Settings group on the List Settings page.List settings with Title, description and navigation link circled
    4. Click the Yes option under the section Display this list on the Quick Launch? and then click the Save button to save your changes.
    5. Note how now there is a new link Golf Clubs under the Lists group within the Quick Launch menu.Quick Launch with Golf Clubs link circled

      The text for the link in the Quick Launch menu is based on the title of the list. The lists title can be changed on the Site Settings page using the same List name, description, and navigation link used to add the list to the Quick Launch.

Export List Data to Excel

This walk-through will show you how to export the list data from the Golf Clubs list to an Excel spreadsheet. It does not matter that the data originally came from an Excel spreadsheet; you could perform these steps on any list. The walk-through does require that you have done the previous walk-through to create the Golf Clubs list.

  1. Export the Golf Clubs list to Microsoft Excel.
    1. Open your browser if it is not already open and navigate to your root team site.
    2. Click the Golf Clubs link in the Quick Launch menu to navigate to the list.Quick Launch with Golf Clubs link circled
    3. Click the List tab to open the toolbar.List toolbar
    4. Click the Export to Excel in the Connect & Export area of the List tab toolbar.List tab toolbar with Export to Excel circled
    5. Click the OK button on the Message from Webpage notifying you about a compatible applicationMessage from webpage.
    6. In the Open or Save dialog, click the Open button to open the owssvr.iqy file.IE Open or Save dialog

      The image shows the dialog you get from IE 9 and 10.

    7. Click the Enable button on the Microsoft Excel Security Notice dialog window to enable the data connection back to the SharePoint server.Excel Security Notice
    8. Microsoft Excel will open with the Golf Clubs list data in a linked spreadsheet. Note that the link is one way. You can only get updates from the SharePoint server, and any changes you make to the spreadsheet data do not sync back to the list in the site.Excel spreadsheet with Golf Clubs data
  2. Create a chart of the Favorite Cars list data.
    1. Inside the Excel spreadsheet, select the Club Name column heading then click and drag your mouse over the data and stop on the Fast Swing Yards column and the last row. The selected region should look like the following image. Note that you are including the column headings in our selection.Excel spreadsheet with Golf Clubs data
    2. With the two data columns selected, click the Insert tab on the Excel toolbar.Excel toolbar Insert tab circled
    3. Click the Line drop-down menu in the Charts region of the Excel Insert tab toolbar and click the first 2-D Line option.Excel Insert tab Bar charts menu
    4. There should now be a 2D line chart of the Golf Clubs data displayed in the center of your spreadsheet.Excel Chart of Golf Clubs
  3. Update the Golf Clubs list data and sync the updates to the linked Excel spreadsheet.
    1. Go back to your browser window with the Golf Clubs list All Items view showing.
    2. Click the Driver link to open the item's properties form.Driver properties dialog
    3. Click the Edit Item link on the Driver properties form.
    4. Change the Fast Swing Yards field of the Driver to "325" and click the Save button to save the change.Driver properties dialog
    5. Click back in your Excel window to bring the it back to the foreground.
    6. Click the DATA tab in Excel to open the toolbar.Excel toolbar Design tab circled
    7. Click the Refresh All button in the Connections region of the Data tab toolbar.Excel toolbar Refresh tab circled
    8. The Excel spreadsheet and chart should now reflect the changes we made to the list.Excel Chart of Golf Clubs
    9. Close Microsoft Excel and click Don't Save when asked if you want to save the changes to 'Book1'.

Outlook Integration

Create an Alert

This walk-through will show you how to create an alert in the Golf Clubs list to notify you when the list is changed. The demonstration requires that you have done the earlier demonstration that created the Golf Clubs list.

This walk-through will only work if your SharePoint server is configured with an email server it can deliver to and the account you are logged in with has a valid email address.

  1. Create an alert for the Golf Clubs list.
    1. Open your browser if it is not already open and navigate to your root team site.
    2. Click the Golf Clubs link in the Quick Launch menu to navigate to the list.Quick Launch with Golf Clubs link circled
    3. Click the List tab in the List Tools toolbar.List toolbar
    4. Click the Alert Me drop-down button in the Share & Track region on the List Tools List tab toolbar and select the Set alert on this list option.List toolbar with Alert circled

      The Alert Me button only displays if the Farm administrator has configured the server with an outgoing mail server address.

    5. Leave all the default options in the Golf Clubs - New Alert dialog form and click the OK button to save the new alert.New Alert dialog
  2. Add a new Golf Clubs item to the list to test the new alert.
    1. Click the new item link at the top of the list.Golf Clubs Add new item link
    2. Enter the following values for new Favorite Car item and click on the Save button to save the data back to the list.
      Club Name Loft Degrees Medium Swing Yards Fast Swing Yards
      Sand Wedge 55 75 80
  3. Check for an alert in Microsoft Outlook 2013.
    1. Launch Microsoft Outlook 2013.
    2. You will eventually have two emails in your Inbox generated by the alert system. It could take up to 10 minutes for them to appear due to a delay in the SharePoint service responsible for generating emails even with the alert option set to Send notification immediately.Outlook inbox with alert messages

Subscribe to a List's RSS Feed

This walk-through will show you how to subscribe to the RSS (Really Simple Syndication) feed generated by the Golf Clubs list. The alert in the previous walk-through can become an annoyance with a lot of changes occurring and emails being generated. A less intrusive way of keeping up to date on lists would be to use RSS. This walk-through requires the Golf Clubs list created in the first walk-through in this lesson.

  1. Subscribe to an RSS feed in the Golf Clubs list.
    1. Click the Golf Clubs link in the Quick Launch menu to navigate to the list.Quick Launch with Golf Clubs link circled
    2. Click the RSS Feed button in the Share & Track region on the List Tools List tab toolbar.List toolbar with RSS Feed circled
    3. Click the Subscribe to this feed on the Favorite Cars RSS feed page.Golf Clubs RSS page
    4. Click Yes in the Add this RSS Feed to Outlook dialog window to complete adding this RSS subscription to the Common Feed List.RSS subscribe to this Feed dialog
    5. Click the link at the top of the RSS feed page to navigate back to the SharePoint site.RSS feed list link
  2. View the Golf Clubs RSS feed in Microsoft Outlook 2013.
    1. Go back to our Outlook window and click on the RSS Feeds link in the side bar menu.Outlook side bar menu
    2. Verify that your RSS Feed is now showing the latest changes to the Golf Clubs list. You can test this further by modifying or creating a new item in the Golf Clubs list.

Connect to Outlook

This walk-through will show you how to connect a library to Outlook. Using the Connect to Outlook feature in SharePoint allows you to have a copy of a library and certain lists in Outlook that you can work with offline. This walk-through uses the Work Orders library that was created in a previous walk-through in the "Site Columns and Content Types" lesson but the same steps can be performed on any library.

  1. Connect the Work Orders library to Outlook.
    1. Click the Work Orders library on the root site's Quick Launch menu. Quick Launch with Work Orders circled

      If Work Orders is not listed under the Recent group in the Quick Launch menu it might be listed under Site Contents. The Recent list only displays the last five lists or libraries created.

    2. Click the Connect to Outlook icon in the Connect & Export region on the Library tab toolbar. Library tab toolbar

      Depending on the width of your browser screen, you may or may not see the label text "Connect to Outlook".

    3. In the Internet Explorer dialog window that asks, Do you want to allow this website to open a program on your computer?, click the Allow button.Internet Explorer Dialog Allow access window
    4. In the Microsoft Outlook dialog window that asks, "Connect this SharePoint Document Library to Outlook?", click the Yes button.Outlook connect library dialog
    5. A new SharePoint Lists tree menu will be added to your Outlook side bar menu with the Work Orders added and opened. Outlook can now be used to view, create, and edit items in the Work Orders library. Additionally you can work offline with this library through Outlook.Outlook with Work Orders Library

Note: In the video that follows, the services must be set for the Outlook button to appear. If your services are set, but the button is still grayed out, please click F5 key to refresh. This is a known quirk in SharePoint.

Access Integration

Open a List with Access

In this walk-through you will learn how to open a SharePoint list in Access. Unlike exporting to Excel, we can both read and update list data from Access. This walk-through uses the Golf Clubs list created as part of an earlier walk-through.

  1. Open the Golf Clubs list in Microsoft Access.
    1. Open your browser if it is not already open and navigate to your root team site.
    2. Click the Golf Clubs link in the Quick Launch menu to navigate to the list.Quick Launch with Golf Clubs link circled
    3. Click the LIST tab to open the toolbar.List toolbar
    4. Click the Open with Access icon in the Connect & Export region on the LIST tab toolbar. List tab toolbar

      Depending on the width of your browser screen, you may or may not see the label text "Open with Access".

    5. Leave the default settings and click the OK button in the Open in Microsoft Access dialog window.Open in Microsoft Access dialog window
    6. In the Microsoft Access window that opens, double-click the Golf Clubs link in the Tables window to open it and view the data.Access with Golf Clubs table circled
    7. Modify the Driver's Fast Swing Yards to be "275". Click the Refresh All button in the Home tab on Access's toolbar.Access Home tab toolbar

      The ribbon message with the Save Changes warning icon and the Save to SharePoint Site button is deceiving. That button will save a new Access database file back to a library on your SharePoint site; it will not save your changes back to the original list.

    8. Go back to your browser window and refresh the Golf Clubs page. The Driver item in the browser window should now match what you changed it to in Access.Golf Clubs List
    9. Try editing the Driver Fast Swing Yards in the browser back to "350" and saving it. Go back to the Access window and click the Refresh All button in the Home tab and verify that Access now displays "350" for the Driver Fast Swing Yards.
    10. Close Access when you are done trying out the editing and synchronizing.

Excel Integration

Duration: 15 to 25 minutes.

In this exercise, you will learn how to export a SharePoint 2010 list to Microsoft Excel.

  1. Export the Favorite Cars list to Microsoft Excel.
    1. Open your browser if it is not already open and navigate to your root team site.
    2. Click the Favorite Cars link in the Quick Launch menu to navigate to the list. Quick Launch with Favorite Cars circled

      If Favorite Cars is not listed under the Recent group in the Quick Launch menu it might be listed under Site Contents. The Recent list only displays the last five lists or libraries created.

    3. Click the Export to Excel in the Connect & Export area of the List tab toolbar.List tab toolbar with Export to Excel circled
    4. In the Open or Save dialog, click the Open button to open the owssvr.iqy file.IE Open or Save dialog

      The image shows the dialog you get from IE 9 or 10.

    5. Click the Enable button on the Microsoft Excel Security Notice dialog window to enable the data connection back to the SharePoint server.Excel Security Notice
    6. Microsoft Excel will open with the Favorite Cars list data in a linked spreadsheet. Note that the link is one way. You can only get updates from the SharePoint server; any changes you make to the spreadsheet data do not sync back to the list in the site.Excel spreadsheet with Favorite Cars data
  2. Create a chart of the Favorite Cars list data.
    1. Inside the Excel spreadsheet, select the data in both the Model and Top Speed columns by clicking and dragging your mouse over both columns of data.Excel spreadsheet with Favorite Cars data
    2. With the two data columns selected, click the Insert tab on the Excel toolbar.Excel toolbar Insert tab circled
    3. Click the Bar drop-down menu in the Charts region of the Excel Insert tab toolbar and click the 3-D Bar option.Excel Insert tab Bar charts menu
    4. There should now be a 3-D chart of the Favorite Cars top speeds displayed in the center of your spreadsheet.Excel Chart of Favorite Cars top speed
  3. Update the Favorite Cars list data and sync the updates to the linked Excel spreadsheet.
    1. Go back to your browser window with the Favorite Cars list All Items view showing.
    2. Click the Ferrari 458 Italia link to open the item's properties form.Ferrari Italia properties dialog
    3. Click the Edit Item link on the Ferrari properties dialog form.
    4. Change the Top Speed field of the Ferrari to "300" and click the Save button to save your change.Ferrari Italia properties dialog
    5. Click back in your Excel window to bring it back to the foreground.
    6. Click the DATA tab in the Excel toolbar.Excel toolbar Design tab circled
    7. Click the Refresh All button in the Connections region of the DATA tab toolbar.Excel toolbar Refresh tab circled
    8. The Excel spreadsheet and chart should now reflect the changes you made to the list.Excel Chart of Favorite Cars top speed
    9. Close Microsoft Excel and click Don't Save when asked if you want to save the changes to 'Book1'.

Outlook Integration

Duration: 15 to 25 minutes.

In this exercise, you will learn how to use Outlook with your SharePoint site.

  1. Create an alert for the Favorite Cars list.

    This alert exercise will work only if your SharePoint server is configured with an email server it can deliver to and the account you are logged in with has a valid email address.

    1. Open your browser if it is not already open and navigate to your root team site.
    2. Click the Favorite Cars link in the Quick Launch menu to navigate to the list.Quick Launch with Favorite Cars circled
    3. Click the List tab to open the toolbar.Favorite cars list tab
    4. Click the Alert Me drop-down button in the Share & Track region on the List Tools List tab toolbar and select the Set alert on this list option.List toolbar with Alert circled

      The Alert Me button only displays if the Farm administrator has configured the server with an outgoing mail server address.

    5. Leave all the default options in the Favorite Cars - New Alert dialog form but take a moment to read the options and their descriptions. Click the OK button, once you are done scanning the options, to save the new alert.New Alert dialog
  2. Add a new Favorite Cars item to the list to test the new alert.
    1. Click the new item link at the top of the list.Favorite Cars Add new item link
    2. Enter the following values for new Favorite Car item and click on the Save button to save the data back to the list.
      Make Model Top Speed Price
      Audi R8 198 196,800.00
  3. Check for an alert in Microsoft Outlook 2013.
    1. Launch Microsoft Outlook 2013.
    2. You will eventually have two emails in your Inbox generated by the alert system. It could take up to 10 minutes for them to appear due to a delay in the SharePoint service responsible for generating emails, even with the alert option set to Send notification immediately.Outlook inbox with alert messages

      If the alert message has not arrived in your Inbox yet, you may wish to continue to the next step in this exercise and check back later.

    3. Leave Microsoft Outlook open for the next step in this exercise.
  4. Subscribe to an RSS feed in the Favorite Cars list.
    1. Go back to your browser window with the Favorite Cars list All Items view showing.
    2. Click the RSS Feed button in the Share & Track region on the List Tools List tab toolbar.List toolbar with RSS Feed circled
    3. Click the Subscribe to this feed on the Favorite Cars RSS feed page.Favorite Cars RSS page
    4. Click Allow in the Internet Explorer dialog asking if you want to allow this website to open a program on your computer. RSS subscribe to this Feed dialog
    5. Click Yes in the Microsoft Outlook dialog asking if you want to "Add this RSS Feed to Outlook". Microsoft Outlook dialog.
    6. Click the link at the top of the RSS feed page to navigate back to the SharePoint site.RSS feed list link
  5. View the Favorite Cars RSS feed in Microsoft Outlook 2013.
    1. You will see an arrow next to the RSS Feeds link in the side bar menu. Click on the arrow to expand it and select the Favorite Cars link within it.Outlook side bar menu
    2. It might take a while before Outlook will automatically synchronize with the RSS feed. To force it to update, click on the Send/Receive tab in Outlook and click the Update Folder link.
    3. Verify that your RSS Feed is now showing the latest changes to the Favorite Cars list. You can test this further by modifying or creating a new item in the Favorite Cars list. If you do, follow the previous step to force Outlook to refresh the RSS feed folder. Leave Outlook open for the next step in this exercise.
  6. Connect the Documents library to Outlook.
    1. Go back to your browser window and click the Documents library on the root site's Quick Launch menu.Quick Launch with Shared Documents circled
    2. Click the Connect to Outlook icon in the Connect & Export region on the Library tab toolbar.Library tab toolbar

      Depending on the width of your browser screen, you may or may not see the label text "Connect to Outlook".

    3. In the Internet Explorer dialog window that asks you "Do you want to allow this website to open a program on your computer?", click the Allow button.Internet Explorer Dialog Allow access window
    4. If you get the Internet Explorer Security dialog window warning you about "A website wants to open Web content using this program on your computer", click the Allow button.Internet Explorer Dialog Allow access window
    5. In the Microsoft Outlook dialog window that asks you "Connect this SharePoint Document Library to Outlook?", click the Yes button.Outlook connect library dialog
    6. A new SharePoint Lists tree menu will be added to your Outlook side bar menu with the Documents added and opened. Outlook can now be used to view, create, and edit items in the Documents library. Additionally, you can work offline with this library through Outlook.Outlook with Shared Documents Library

Access Integration

Duration: 15 to 25 minutes.

In this exercise, you will learn how to use Microsoft Access with your SharePoint site.

  1. Open the Favorite Cars list in Microsoft Access.
    1. Open your browser if it is not already open and navigate to your root team site.
    2. Click the Favorite Cars link in the Quick Launch menu to navigate to the list.
    3. Click the LIST tab to open the toolbar.Favorite cars list tab
    4. Click the Open with Access icon in the Connect & Export region on the List tab toolbar. List tab toolbar

      Depending on the width of your browser screen, you may or may not see the label text "Open with Access".

    5. Leave the default settings and click the OK button in the Open in Microsoft Access dialog window.Open in Microsoft Access dialog window
    6. In the Microsoft Access window that opens, double-click the Favorite Cars link in the Tables window to open it and view the data.Access with Favorite Cars table circled
    7. Modify the Bugatti's Top Speed to be "250". Click the Refresh All button in the Home tab on Access's toolbar.Access Home tab toolbar
    8. Go back to your browser window and refresh the Favorite Cars page. The Bugatti item in the browser window should now be the same as what you changed it to in Access.Favorite Cars List
    9. Try editing the Bugatti's Top Speed in the browser back to "268" and saving it. Go back to the Access window and click the Refresh All button in the Home tab and verify that Access now displays "268" for the Bugatti's Top Speed.
    10. Close Access when you are done trying out the editing and synchronizing.