12. Collaborate with Office programs by using SharePoint

In this chapter

Edit documents in Office

Share OneNote notebooks with SharePoint

Import data from and export data to Excel spreadsheets

Work with SharePoint content in Outlook

Integrate Access with SharePoint

Practice files

For this chapter, use the practice files from the SP2016SBSCh12 folder. For practice file download instructions, see the introduction.

Microsoft SharePoint Server 2016 provides the collaborative backbone to the Microsoft Office 2016 programs, Office Online apps, and mobile Office apps. Both Office Online apps and mobile Office apps provide a subset of the capabilities and functionality of Office programs. However, if you need to quickly view content or give a presentation by using your smartphone or tablet, you should consider using the platform-specific Office mobile apps.

This chapter guides you through procedures related to editing documents by using Office, sharing OneNote notebooks, importing and exporting data in Microsoft Excel spreadsheets to and from SharePoint lists, connecting Microsoft Outlook with SharePoint lists and libraries (thereby storing SharePoint content as a local copy that is available offline), using SharePoint with Outlook and Skype for Business, and using Microsoft Access with SharePoint.

The combination of SharePoint and Office reduces your dependency on using your browser to complete tasks. Together, SharePoint and Office provide a variety of methods for collaborating, whether you are co-authoring a spreadsheet, commenting on the contents of a document, processing a business plan by using a workflow, or categorizing your documents by using metadata stored in document library columns. When you store your files within SharePoint, Office provides your team with a similar experience in Microsoft Word, Excel, PowerPoint, Visio, and OneNote, and provides a Save To SharePoint option in each of these programs.

The DIP has been removed from Office 2016 programs. To view and edit the metadata within Word 2016, Excel 2016, and PowerPoint 2016, you can use the Info page that is available when you select the File tab. In Word, you can also embed metadata in the body of the document by using quick parts, also known as property controls. You can use quick parts to track and edit metadata as you work in a document; the metadata is then saved into the document library columns when the document is saved.

To add a quick part to a Word document

1. Place the cursor in the document where you want to add a quick part.

2. On the Insert tab, in the Text group, select Quick Parts.

3. Select Document Property, and then select the name of the property you want to add.

To update a Word document property by using a quick part

1. In the document, select the quick part control that references the property you want to change.

2. Change the information.

To update a Word document property by using the Properties section of the Info page

1. In Word, select the File tab to display the Info page.

2. If all of the properties are not listed under Properties on the right side of the page, at the bottom of the right side, under Related Documents, select Show All Properties.

3. Point to the right of the property you want to update, and make your change.

The co-authoring functionality in each Office product is similar: however, there are some differences, as detailed in the following table. Only when you use those Office products listed with a “Yes” in the respective SharePoint column can you co-author documents.

Office programs and SharePoint are designed to minimize any performance or scalability issues that might be associated with co-authoring documents; therefore, only when more than one author is editing the document does the Office program send or download co-author information from the server. Office programs are designed to reduce the frequency of synchronization actions related to co-authoring when the server is under a heavy load or when a user is not actively editing a document.

In the status bar of the Office 2016 programs—that is, not the Office Online products—you might see a message that says Updates Available which means that if you save the document now, it will be updated with the changes that others have saved.

You can configure whether others can see your changes by using the Share pane, which is displayed when you select Share in the upper-right corner (in Word, you can also configure this in the Word Options dialog box, on the General Tab).

The Automatically Share Changes options are:

Always Others who have agreed to share their changes will always be able to see yours.

Ask Me If this is the first time you have used real-time co-authoring, you will be asked whether you want to automatically share your changes as they happen. Select Yes to always allow automatic sharing. (Clear the Don’t Ask Me Again check box if you don’t want to be asked about this the next time you open a document.)

Never Only you will be able to see your changes until you save your document, and you will not be able to see other users’ changes; how-ever, you will be able to see who has the document open.

When a user edits a shape, a person icon is added to the upper-right corner of the shape, to inform you that someone else is editing the shape. Select the person icon to display the name of the person. Shapes that have been deleted by someone else display an exclamation mark in a square box along with a small red cross in the lower-right corner of the box.

Visio does not stop multiple users from editing the same shape; therefore, a more correct name for this feature would be collaborative authoring.

OneNote 2016, OneNote 2013, and OneNote 2010 use the same file format (.one); therefore, if you have notebooks that were created in previous versions of OneNote, you can open and edit them in any of these versions. However, the newer notebook format is not backward-compatible; for example, you cannot use a notebook you created in OneNote 2016, OneNote 2013, or OneNote 2010 in OneNote 2007 or OneNote 2003.

OneNote integrates with other Office programs, such as Outlook—you can flag a note in OneNote and use the Outlook Tasks command on the Home tab to turn it into an Outlook task, you can send a OneNote page as an email message, or you can send an entire notebook as a OneNote file or as a PDF file as an email attachment.

You can store OneNote notebooks to your computer’s local drive and to your personal OneDrive, to your OneDrive for Business and in SharePoint libraries, thereby sharing the information in the notebook with your team in a central place and allowing easy access to the notebook from any device.

When you or another user changes the content in a notebook, OneNote automatically saves and syncs the notebook on SharePoint. You can also sync notebooks manually. OneNote also maintains a separate offline copy of the notebook on each user’s computer, thereby allowing you to edit the notebook locally, even when you are disconnected from the network. The next time you connect to the shared notebook, OneNote merges your changes with everyone else’s.

To create a SharePoint library in which to save OneNote notebooks

1. In SharePoint, do one of the following to display the Your Apps page:

• On the Settings menu, select Add an app.

• On the Quick Launch, select Site Contents, and then select add an app.

2. On the Your Apps page, under Apps you can add, select Document Library to open the Adding Document Library dialog.

3. Select Advanced Options to display the New page.

4. In the Name box, enter a name for the library.

5. In the Document Template list, select Microsoft OneNote 2010 Notebook, and then select Create.

To create a OneNote notebook in a SharePoint library by using your browser

1. If you have Office Online Server installed and connected to your SharePoint server, or if you are using Office 365, go to the SharePoint library where you want to create the OneNote notebook.

2. Select new, and then select OneNote notebook.

3. In the Name text box, enter the name of your notebook, and then select OK.

Or

1. Go to the SharePoint library where you want to create the OneNote notebook, and do one of the following:

• If the document library is associated with a OneNote notebook document template, on the Files tab, select New.

• If a content type that is associated with a OneNote notebook document template has been added to the library, on the Files tab, select the New arrow, and then select that content type.

2. In the dialog box that opens, select the option that allows OneNote to open. (The form of this dialog box and option depends on your browser. For example, if you are using Internet Explorer, select Allow; if you are using Google Chrome, select Launch Application; or if you are using Microsoft Edge, select OneNote or OneNote 2016. The OneNote program starts and opens the Unpack Notebook dialog box.

3. In the Name box, enter the name of your notebook, and in the path list, enter the web address of the SharePoint library.

4. Select Create.

To create a OneNote notebook in a SharePoint library by using OneNote

1. Open OneNote 2016, select the File tab, and then on the New page, select Other Web Location or Browse. Then do one of the following to open the Create New Notebook dialog box:

• Under Recent Folders, select a SharePoint library where you have previously created or opened other OneNote notebooks.

• Select Browse.

2. If the Create New Notebook dialog box does not display the contents of the SharePoint library where you want to store your team notebook, in the Notebook Name box, enter the web address of the SharePoint site, and press Enter to go to the document library on the SharePoint site.

3. In the Notebook Name box, enter a meaningful, descriptive name for the new notebook, and then select Create.

4. In the Microsoft OneNote dialog box, do one of the following:

• Select Invite people if you want to share the notebook.

• Select Not now to keep the notebook unshared for now.

To manually sync a notebook

1. In OneNote, select the notebook list, and then do one of the following to the notebook you want to sync:

• Right-click the notebook, and then select Sync This Notebook Now.

• Select the notebook and press Shift+F9.

• Right-click the notebook, select Notebook Sync Status, and in the Shared Notebook Synchronization dialog box, to the right of the notebook, select Sync Now.

• Select the File tab. On the Info page, select Settings to the left of the notebook, and then select Sync.

To configure notebook synchronization

1. In OneNote, do one of the following to open the Shared Notebook Synchronization dialog box:

• Select the File tab, and then on the Info page, select View Sync Status.

• Right-click the notebook, and select Notebook Sync Status.

2. Under How should we sync your notebooks, select either Sync automatically whenever there are changes or Sync manually.

The web query that exports the contents from the list or library creates an Excel table named Table_owssvr and maintains a connection between the content in the list or library and the content in the table, making the table an external table or linked object. Changes you make to data in your Excel worksheet do not synchronize with the list or library on your SharePoint site; that is, only a one-way synchronization occurs from the SharePoint site to Excel. Any changes you make to data in the Excel spreadsheet are lost when you next refresh the external table content.

Similarly, when you create a SharePoint list from within Excel, you have an option to create the data within Excel as an external table; after the SharePoint list is created, you cannot link the spreadsheet to the SharePoint list. Therefore, if you want to synchronize updates between the list and the spreadsheet, be sure to select the Create A Read-Only Connection To The New SharePoint List check box. When you use your browser to create a SharePoint list from Excel data, the option to create an external table in Excel is not available.

To create a SharePoint list from an Excel spreadsheet by using your browser

1. In SharePoint, on the Your Apps page, under Apps you can add, select Import Spreadsheet to display the New page.

2. In the Name box, enter a name for the list, and optionally enter a description of the list in the Description box.

3. Select Browse to open the Choose File To Upload dialog box, and go to the folder that contains your spreadsheet.

4. Select your spreadsheet, and then select Open.

5. On the New page, select Import.

Excel opens your spreadsheet and opens the Import To Windows SharePoint Services List dialog box.

6. In the Range Type list, select Table Range, Range of Cells, or Named Range, as appropriate, and then in the Select Range list, select the data you want to copy to your SharePoint list.

7. Select Import to create the list.

To create a SharePoint list from within Excel

1. If the data in your Excel spreadsheet is not already formatted as an Excel table, complete the following:

a. In Excel, select any cell within the data you want to create the list from.

b. On the Home tab, in the Styles group, select Format as Table, and then select a table style.

c. In the Format As Table dialog box, change the range of cells if necessary, and optionally select the My table has headers check box.

d. Select OK.

2. Select any cell within your Excel table if one is not already selected, so that the Table Tools Design tab is displayed.

3. In the External Table Data group, select the Export arrow, and then select Export Table to SharePoint List.

4. In the Export Table to SharePoint List – Step 1 of 2 dialog box, in the Address box, enter the name of the SharePoint site to which you want to export the data.

5. Select Create a read-only connection to the new SharePoint list if you want to create a one-way synchronization connection between the Excel table data and the data in the SharePoint list.

6. In the Name box, enter the name of the SharePoint list, and optionally, in the Description box, enter a description for the SharePoint list.

7. Select Next to open the Export Table To SharePoint List – Step 2 Of 2 dialog box.

8. Select Finish, and then select OK to close the Microsoft SharePoint Foundation dialog box that confirms that the table was successfully published.

To export the contents of a SharePoint list or library to an Excel spreadsheet

1. Go to the list or library, and on the List or Library tab, in the Connect & Export group, select Export to Excel.

You will see either a File Download dialog box or a notification message at the bottom of your browser that ask whether you want to open or save owssvr.iqy.

2. Select Open. Excel opens a new workbook that contains one worksheet named owssvr.

3. In the Microsoft Excel Security Notice dialog box, select Enable.

To refresh external table data

1. In Excel, select one cell within your external table, and then on the Table Tools Design tab, in the External Table Data group, select Refresh.

To modify the external table data refresh behavior

1. Select one cell within your external table, and then do one of the following to open the Connection Properties dialog box:

• On the Table Tools Design tab, in the External Table Data group, select the Refresh arrow, and then select Connection Properties.

• On the Data tab, in the Connections group, select Connections. In the Workbook Connections dialog box, select owssvr, if it is not already selected, and then select Properties.

2. Make your changes, and then select OK.

When you are using an Office program with a document stored in SharePoint, SharePoint Online in Office 365, or OneDrive for Business, if you point to the name of the author of the document or the name of a co-author, that person’s contact information is displayed. If you have Skype for Business installed, the contact information includes options to start an instant message, a call, or a video call, or send an email.

After you connect a list or library to Outlook, if you have permissions to edit list items in the SharePoint list, you can modify items from that list from within Outlook. Similarly, if you have permissions to view a document in a SharePoint library, you can view a preview of the contents of the file from the connected library in the Outlook reading pane. However, you do not edit the contents of a file within Outlook; to edit the file, you double-click the file in Outlook to open it in the appropriate program. When you do so, you will be modifying an offline copy of that document, known as a cached copy. When you have completed your modifications, you can choose to save the document to the SharePoint library.

You disconnect a list or library from Outlook by using a delete command. This does not delete the SharePoint list or library, but it does remove the related list or library from Outlook on all computers you use.

You can also share a connection with others by right-clicking the list or library name in Outlook and then selecting Share This Folder.

To connect a list or library to Outlook

1. Go to a list or library you want to connect to Outlook.

2. On the List, Library, or Calendar tab, in the Connect & Export group, select Connect to Outlook.

3. If an Internet Explorer - Security Warning dialog box appears asking whether you want to allow this website to open a program on your computer, select Allow.

4. If another Internet Explorer - Security Warning dialog box appears, telling you that a website wants to open web content using this program on your computer, select Allow.

5. A Microsoft Outlook dialog box appears, telling you that you should only connect lists from sources you know and trust. Optionally select Advanced to open a SharePoint List Options dialog box, and in the Folder Name box, enter a new name for the list as it will be shown in Outlook, and then select OK.

6. In the Microsoft Outlook dialog box, select Yes.

To disconnect a SharePoint list or library from Outlook

1. In Outlook, do one of the following:

• In the mail navigation pane, under SharePoint lists, right-click the SharePoint library or discussion board, and select Delete Folder.

• In the calendar navigation pane, under Other Calendars, right-click the SharePoint calendar list, and select Delete Calendar.

• In the contacts navigation pane, under Other Contacts, right-click the SharePoint contact list, and select Delete Folder.

• In the task navigation pane, under Other Tasks, right-click the SharePoint task list, and select Delete Folder.

2. In the Microsoft Outlook dialog box that appears, select Yes.

To display a connected SharePoint calendar list in Outlook

1. In Outlook, in the calendar navigation pane, select the check box to the left of the connected SharePoint calendar list.

To overlay calendars

1. In Outlook, in the calendar content area, select the left arrow on the calendar tab.

To copy a calendar item to or from a SharePoint calendar list

1. In the calendar navigation pane, display the source calendar and the SharePoint calendar.

2. Select the calendar item you want to copy, and drag the item onto the other calendar.

To move a calendar item to or from a SharePoint calendar list

1. Complete the copy procedure as described previously, and then delete the calendar item from the original calendar.

To copy contacts or tasks from Outlook to a SharePoint contacts or task list

1. In Outlook, do one of the following:

• In the contacts navigation pane, under My Contacts, select Contacts, and then select one or more contacts.

• In the task navigation pane, under My Tasks, select To-Do List or Tasks, and then select one or more tasks.

2. On the Home tab, in the Actions group, select Move, and then select Copy to Folder.

3. In the Copy Items dialog box, expand SharePoint Lists, select a SharePoint list you previously connected to Outlook, and then select OK.

To move contacts or tasks from Outlook to a SharePoint contacts or task list

1. In Outlook, do one of the following:

• In the contacts navigation pane, under My Contacts, select Contacts, and then select one or more contacts.

• In the task navigation pane, under My Tasks, select Tasks, and then select one or more tasks.

2. On the Home tab, in the Actions group, select Move and then select the respective connected SharePoint list.

Or

Do one of the following to open the Move Items dialog box:

• On the Home tab, in the Actions group, select Move, and then select Other Folder.

• Press Ctrl+Shift+V.

Expand SharePoint Lists, select a SharePoint list you previously connected to Outlook, and then select OK.

To move or copy an item from a SharePoint list to an Outlook folder

1. Do one of the following:

• In the contacts navigation pane, under Other Contacts, select the connected SharePoint contacts list, and then select one or more contacts.

• In the task navigation pane, under Other Tasks, select the connected SharePoint task list, and then select one or more tasks.

2. Follow the procedures described earlier in this section to move or copy items between lists and folders.

To add a SharePoint list RSS feed to Outlook

1. Go to the list or library whose RSS feed you want to subscribe to, and on the List or Library tab, in the Share & Track group, select RSS Feed.

2. Right-click Subscribe to this feed, and then select Copy Shortcut (or if you are not using Internet Explorer, the equivalent option in your browser).

3. Open Outlook, if it is not already open.

4. In the mail navigation pane, right-click RSS Feeds, and then select Add a New RSS Feed.

5. In the New RSS Feed dialog box, in the Enter the location of the RSS Feed box, press Ctrl+V to paste the shortcut that you copied in step 2.

6. Select Add.

7. In the Microsoft Outlook dialog box, optionally select Advanced to enter a meaningful name for the RSS feed as it will be shown in Outlook, and then select OK.

8. Select Yes to close the Microsoft Outlook dialog box.

To create a new SharePoint alert by using Outlook

1. Select the File tab, and then on the Info page, select Manage Rules & Alerts to open the Rules And Alerts dialog box.

2. Select the Manage Alerts tab, and then select New Alert.

3. In the New Alert dialog box, select a SharePoint site from the list under either Sources currently sending me alerts or Sources I have visited, or, in the Web site Address box, enter the name of the SharePoint site for which you want to create the alert, and then select Open.

4. On the New Alert page that opens in your browser, select one of the lists or libraries, and then select Next.

5. Complete the page so that you receive alerts as needed, and then select OK.

The level of server functionality integrated into Access 2016 increases when it is used in combination with an Access app hosted in SharePoint Server 2016 Enterprise Edition running Access Services, which was first introduced with SharePoint Server 2013 and Access 2013.

In Access 2016, as in Excel and previous versions of Access, you can export and import data both to and from SharePoint lists; however, if you do not want to maintain two copies of that data, one in Access and one in SharePoint, you can link to data that is physically located external to Access.

In an Access app, a table linked to a SharePoint list only provides a read-only connection, whereas a table linked to a SharePoint list in an Access desktop database provides two-way synchronization, so you can work with the SharePoint data offline and then synchronize the changes when you reconnect.

When you select an Access app, the Access app page is displayed. It includes a Back To Site link in the upper left of the page; when selected, this link displays the home page of the SharePoint site where the Access app was created. You can select the cog icon in the upper right of the page to customize the app in Access.

The Access app page is divided into several elements:

Tables Similar to the Quick Launch in your SharePoint team site, this area displays Access app tables.

Views This area displays a list of views associated with the table that is currently selected in the table selector element. Most tables include a List and a Datasheet view.

Filter text box This can be used to search and filter data displayed in the view. Access Services does not search for sequences of characters within a word.

Action bar This bar displays buttons that you can use to add, delete, edit, save, and cancel changes. The action bar is the same for each view; however, Access Services enables and disables the buttons depending on what actions are actually available for the view. For example, when the detailed pane is in view mode, the Add, Delete, and Edit buttons are enabled. When the item detailed pane is in edit mode, the Add, Delete, and Edit buttons are disabled, and the Save and Cancel buttons are enabled.

Item pane This pane displays two fields and an optional thumbnail image from an Image data type field. The values for the field are for the items stored in the table that is currently selected in the table selector element. The item pane displays a scroll bar if there are more items to display.

Item detailed pane This displays fields for the item that is currently selected in the item pane. The item detailed pane only displays those fields that are configured to be visible in the view you select in the view selector. Depending on the action bar button you select, you can view, create, modify, and delete properties of an item by using the item detailed pane.

Related items This area displays items from other tables that are related to the item displayed in the item detailed pane. A tab is displayed for each related table. When there are no related tables, this element is not displayed.

Edit documents in Office

Share OneNote notebooks with SharePoint

Import data from and export data to Excel spreadsheets

Work with SharePoint in Outlook

Integrate Access with SharePoint

1. Create a document library named Survival Guide.

2. Create a choice column named HR Region containing the choices Europe and UK, configured as a drop-down menu. Ensure that the HR Region column is added to the default view of the library.

3. Create a Word document in the library.

4. Open the document in Word and add a quick part to display the HR Region property.

5. In the quick part, select Europe, and then save your document.

6. Close Word. In your browser, you should see the value Europe in the HR Region column for the document.

7. Leave your browser open if you are continuing with the next set of practice tasks.

1. Create a OneNote notebook named Recruitment Tips & Tricks.

2. If you have the notebook opened in OneNote Online, open in the OneNote program and then create a page named Interview Questions. In the body of the page, enter What is your greatest strength?

3. Manually sync your notebook, and then close OneNote.

4. Leave your browser open if you are continuing with the next set of practice tasks.

1. Create a SharePoint list named FurniturePrice by using the Excel Furniture_Price.xslx spreadsheet from the practice file folder, with a description of This list contains the furniture items in stock together with their unit prices.

Select the Stock!FurniturePriceTable table range.

2. Change the list name to Furniture Price, and ensure that the list is displayed on the Quick Launch.

3. Export the Furniture Price list to Excel and add a new row that has the following values: Garden lounger, Bianca, wood, 5, 10.

4. Refresh the external table data. The spreadsheet is updated with a copy of the data from the Furniture Price list on the SharePoint site. Your changes to data in the Excel spreadsheet are lost. Discard the workbook.

5. Open the Sales_Figures.xlsx workbook from the practice file folder, and format the data in the workbook as a table.

6. Export the table to your SharePoint site as a read-only connection, with a list name of SalesFigures and a description of This list contains furniture sales for this year.

7. Open the new list in your browser, and review the data in the list.

8. Leave your browser open if you are continuing with the next set of practice tasks.

1. Connect your contact list to Outlook, and name the folder WideWorldImporters – Contacts.

2. Copy two contacts from your Outlook My Contacts address book to the WideWorldImporters – Contacts folder.

3. Move one of the contacts from the WideWorldImporters – Contacts folder to your My Contacts address book.

4. Get the web address of your contact list and add it in Outlook as a RSS Feed.