Primer on Sharepoint Excel DataRefresh Settings

Primer on SharePoint Excel Data Refresh Settings

V 1.2

There are different places where you can configure the Excel data refresh settings for external data when using it with SharePoint.

a. Microsoft Excel file Data Connection Properties
b. Excel Web Viewer refresh option – viewing files in Excel Web Access
c. In Central Admin > Excel Services > Trusted File Locations
d. SharePoint Excel Web Part Properties

I created this Excel Data Refresh Options Chart that I’ve used on different projects. It shows external data refresh option in standalone MS Excel file, Excel file in a SharePoint library, in Excel Web Access and in Excel Web Access Web Part. It also shows different roles around Excel in an organization. Excel Workbook Author in some organization could be different than the SharePoint Site Admin that sets the web parts.

Softvative – MS Excel External Data Refresh Options Chart

a. MS Excel Data Connection Properties

1. Open your MS Excel file that has the data. Click on Data Ribbon tab and then click Connections.

MS Excel Data Connections Menu

2. Select one of the existing connections in your workbook and then click on Properties button.

MS Excel Data Connections Dialog Box

3. Under the Usage tab, under Refresh Control section there are refresh data settings:

• Enable background refresh
• Refresh every n minutes
• Refresh data when opening the file
• Remove data from the external data range before saving the workbook

MS Excel Data Connection – Refresh Control Settings

b. Excel Web Viewer refresh option – viewing files in Excel Web Access

You need to have SharePoint Enterprise edition for Excel Services. Hover mouse over an Excel file in a library, click on drop down arrow on right side to bring the menu and choose ‘View in Browser‘.

SharePoint Excel File in a Document Library – View in Browser

 When MS Excel file opens up in Excel Web Access, click on Data menu. SharePoint users will see these two options:

• Refresh Selected Connection
• Refresh All Connections

SharePoint – Excel Web Access – Data Refresh Options

c. In Central Admin > Excel Services > Trusted File Locations

SharePoint – Excel Services Application

SharePoint – Excel Web Services – Trusted File Locations

6. Scroll down and review / update values as appropriate under External Data section > External Data Cache Lifetime section for automatic and manual refresh. Default setting for both is 300 seconds (5 minutes)

• Automatic refresh (periodic / on-open)
• Manual Refresh

SharePoint – Excel Services – External Data Cache Lifetime

d. SharePoint Excel Web Part Properties

1. Create a new page or using existing page. Edit the page by clicking on Page ribbon tab > Edit Page. (or Site Settings > Edit Page)

SharePoint – Edit Page

2. Click on ‘Add a Web Part‘ link
3. Click on ‘Business Data‘ under Categories and then click on ‘Excel Web Access‘ under Web Parts section. Click Add button on the right side of the screen.

SharePoint – Add Web Part – Categories > Excel Web Access

4. Excel Web Access – Select a Workbook web part will come up on your page. Click on link that says ‘Click here to open the tool pane‘

SharePoint – Excel Web Access – Select a Workbook

5. Excel Web Access Web Part Properties section comes up on the right side of the page. Select an Excel file under Workbook field.

SharePoint – Excel Web Access Web Part Properties – top

SharePoint – Excel Web Access Web Part Properties – bottom

The default refresh connection settings on that web part are:

You can leave the default setting for “Refresh Selected Connection, Refresh All Connections” to have that refresh option for your end users. Alternatively you can uncheck that option to remove refresh connection option for your users.

6. Optionally expand the Advanced property section of the web part and uncheck following options:
Allow Close
Allow Hide
Allow Editing in Personal View

SharePoint – Web Part Advanced Properties

7. Click Apply at the bottom right of the web part properties.
8. Review the Excel Web Access file and go to Data menu and see Refresh options.
9. Edit the web part properties again and uncheck the option “Refresh Selected Connection, Refresh All Connections“. Click Apply to save changes. Now review the Data menu and you will notice the refresh options are now removed.

SharePoint – Excel Web Access – Data menu

10. Excel Web Part property ‘Type of Toolbar‘ is by default set to full. That displays toolbar options based on settings. You can set that option to Summary, Navigation or none. These settings also hide or show refresh options in the Excel Web Access tool bar.

References:

External Data Cache Lifetime reference:
http://technet.microsoft.com/en-us/library/ff191194(v=office.14).aspx

Excel 2007 with Excel Services Reference
http://office.microsoft.com/en-us/sharepoint-server-help/refresh-external-data-in-excel-services-HA010105474.aspx

Excel Web Access Web Part custom properties (Sharepoint 2010)
http://office.microsoft.com/en-us/sharepoint-server-help/excel-web-access-web-part-custom-properties-HA010377893.aspx

Getting Started with Excel Services and Excel Web Access
http://office.microsoft.com/en-us/sharepoint-server-help/getting-started-with-excel-services-and-excel-web-access-HA010377881.aspx

Excel Services 2010 Data Refresh Flowchart: What is going on behind the scenes
http://blogs.technet.com/b/tothesharepoint/archive/2011/07/29/troubleshooting-excel-services-data-refresh.aspx

Excel Services data refresh flowchart
http://technet.microsoft.com/en-us/library/hh369968.aspx

Overview of connecting to (importing) data
http://office.microsoft.com/en-us/excel-help/overview-of-connecting-to-importing-data-HP010342748.aspx

Excel 2010 – Connection Properties
http://office.microsoft.com/client/helppreview14.aspx?AssetId=HA010342299&lcid=1033&NS=EXCEL&Version=14&tl=2&queryid=&respos=1&HelpID=22115

Excel 2010 – Refresh connected (imported) data
http://office.microsoft.com/client/helppreview14.aspx?AssetId=HP010342826&lcid=1033&NS=EXCEL&Version=14&tl=2&queryid=&respos=1&HelpID=199707

Configure Excel Services data refresh by using the unattended service account (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/hh525344(v=office.14).aspx

Configure Excel Services data refresh by using external data connections (SharePoint Server 2010)
http://technet.microsoft.com/en-us/library/hh525341(v=office.14).aspx

Keyword:

• SharePoint  BI
• Excel Web Access BI
• Excel Web Services BI
• SharePoint Business Intelligence
• Softvative SharePoint BI Data Refresh Chart
• Data Refresh Chart
• Excel Data Refresh Options
• Excel Data Refresh Settings
• Excel Automatic Data Refresh Options
• Excel Manual Data Refresh Options