facebook google plus twitter
Webucator's Free SharePoint End User Training Tutorial

Lesson: Office Integration

Welcome to our free SharePoint End User Training tutorial. This tutorial is based on Webucator's SharePoint 2016 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.

Note that this chapter is exploring integration with Office locally installed on the machine connecting to SharePoint. Office Online Server is a browser based version of Office that allows you to create and modify office documents. With SharePoint 2016 Office Online Server can be integrated and is considered an essential piece for many SharePoint installations. That being said, since it is a separate product from SharePoint it is not covered as part of this course.

Lesson Goals

  • Create a list from an Excel spreadsheet.
  • Update a spreadsheet view of SharePoint list data.
  • Create an alert.
  • 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. 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 11.

    6. 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
    7. 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 Golf Clubs 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'.

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 11.

    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

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 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. Right-click the URL field in the browser and choose the Copy option to copy the URL into the Windows clipboard.Golf Clubs RSS URL

      The RSS page does offer a link Subscribe to this feed but it tends to react differently in different browsers. In Internet Explorer is will add the RSS feel to the Windows Common Feed List but with the Microsoft Edge browser it reacts differently. Manually copying the URL and pasting it into Outlook offers a more consistent method. Note that you can configure Outlook to automatically sync with the Windows Common Feed List but it is not configured that way by default.

    4. Switch to the Outlook window or open one if it is not already open, and right-click the RSS Feeds folder and choose the Add a New RSS Feed... option.Outlook add RSS Feed menu
    5. Type Ctrl+V to paste the RSS URL into the New RSS Feed dialog field and click the Add button.RSS feed list link
    6. Click Yes on the Microsoft Outlook dialog verifying you want to add the RSS feed to Outlook.Outlook dialog
    7. Verify that Outlook shows the latest items from the list in the view of the RSS Feed.Outlook RSS View of golf clubs

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

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. Right-click the URL field in the browser and choose the Copy option to copy the URL into the Windows clipboard.Favorite Cars RSS URL

      The RSS page does offer a link Subscribe to this feed but it tends to react differently in different browsers. In Internet Explorer is will add the RSS feel to the Windows Common Feed List but with the Microsoft Edge browser it reacts differently. Manually copying the URL and pasting it into Outlook offers a more consistent method. Note that you can configure Outlook to automatically sync with the Windows Common Feed List but it is not configured that way by default.

    4. Switch to the Outlook window or open one if it is not already open, and right-click the RSS Feeds folder and choose the Add a New RSS Feed... option.Outlook add RSS Feed menu
    5. Type Ctrl+V to paste the RSS URL into the New RSS Feed dialog field and click the Add button.RSS feed list link
    6. Click the Yes button on the Microsoft Outlook dialog verifying you want to add the RSS feed to Outlook.Outlook dialog
    7. Verify that Outlook shows the latest items from the list in the view of the RSS Feed.Outlook RSS View of favorite cars
  5. 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. In the Microsoft Outlook dialog window that asks you "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 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

Open a List with Access

In this walk-through you will learn how to open a SharePoint list in Access as well as create and Access view. 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.
  2. Create an Access View for the Golf Clubs list.
    1. Click the Golf Clubs link in the Quick Launch menu to navigate to the list.
    2. Click the LIST tab to open the toolbar.Favorite cars list tab
    3. Click the Create View icon on the List tab toolbar.Favorite cars list tab with create view circled
    4. Click the Access View link from the new view selection page.Favorite cars list tab with create view circled
    5. In the Access dialog that opens click the Save button to save a local copy with the new view with the default file name.Access save file dialog
    6. In the Create Access View dialog of choices choose the Split Form template and click the OK button.Access view choices dialog
    7. Click into the Club Name field of the row labeled (New) and create a new entry in the Golf Clubs list by entering values for each of the fields. You can use the following screen capture for input data.Access view new entry

      Note that the fields above the datasheet are for viewing only, you cannot use them to enter data. Some of the other view templates have forms with editable fields or you can modify this view and change the fields to editable.

    8. Switch back to the Internet Explorer window and click the Golf Clubs link in the Quick Launch to refresh the list.
    9. Note that the entry from the Access view is automatically saved back to the SharePoint list.Golf Clubs list with new entry from Access view
    10. Close Access and click the Yes button to save the changes.
    11. Click the OK button on the Save As dialog for the form name.

      Note that the Access views do not list with other custom views in the browser but you can use it by opening the Access file created when the Access View was created.

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.
  2. Create an Access View for the Favorite Cars list.
    1. Click the Favorite Cars link in the Quick Launch menu to navigate to the list.
    2. Click the LIST tab to open the toolbar.Favorite cars list tab
    3. Click the Create View icon on the List tab toolbar.Favorite cars list tab with create view circled
    4. Click the Access View link from the new view selection page.Favorite cars list tab with create view circled
    5. In the Access dialog that opens click the Save button to save a local copy with the new view with the default file name.Access save file dialog
    6. In the Create Access View dialog of choices choose the Split Form template and click the OK button.Access view choices dialog
    7. Click into the Make field of the row labeled (New) and create a new entry in the Favorite Cars list by entering values for each of the fields. You can use the following screen capture for input data.Access view new entry

      Note that the fields above the datasheet are for viewing only, you cannot use them to enter data. Some of the other view templates have forms with editable fields or you can modify this view and change the fields to editable.

    8. Switch back to the Internet Explorer window and click the Favorite Cars link in the Quick Launch to refresh the list.
    9. Note that the entry from the Access view is automatically saved back to the SharePoint list.Favorite cars list with new entry from Access view
    10. Close Access and click the Yes button to save the changes.
    11. Click the OK button on the Save As dialog for the form name.

      Note that the Access views do not list with other custom views in the browser but you can use it by opening the Access file created when the Access View was created.