Archive for the ‘MSOffice’ Category

Primer on Sharepoint Excel DataRefresh Settings

Sunday, June 1st, 2014

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

Follow these steps to review / configure the Excel Services Refresh Settings. 

1. Go to SharePoint Central Admin Website 
2. Click on ‘Application Management‘ on left navigation
3. Click on ‘Manage Service Applications‘ link and then click on your Excel Service Application name
4. Click Trusted File Location

SharePoint – Excel Services Application

5. Hover mouse over one of your trusted file location and from drop down menu choose Edit.

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:

Section Option Default
Toolbar Menu Command Refresh Selected Connection, Refresh All Connections Checked
Interactivity Periodically Refresh if Enabled in Workbook Unchecked

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.


External Data Cache Lifetime reference:

Excel 2007 with Excel Services Reference

Excel Web Access Web Part custom properties (Sharepoint 2010)

Getting Started with Excel Services and Excel Web Access

Excel Services 2010 Data Refresh Flowchart: What is going on behind the scenes

Excel Services data refresh flowchart

Overview of connecting to (importing) data

Excel 2010 – Connection Properties

Excel 2010 – Refresh connected (imported) data

Configure Excel Services data refresh by using the unattended service account (SharePoint Server 2010)

Configure Excel Services data refresh by using external data connections (SharePoint Server 2010)


  • 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

Sharepoint 2013 Project Server 2013 Office 2013 Resources V 2.0

Monday, July 16th, 2012

Microsoft have released the Sharepoint 2013, Project Server 2013, Project Professional 2013, Office 2013, Visio 2013, Office Web Server 2013, Lync 2013 and Exchange 2013 today. Here are some of the Sharepoint 2013 resources.

    1. Sharepoint 2013 Site
    2. System Requirements for Sharepoint 2013
    3. Sharepoint 2013 SDK
    4. Download SharePoint Foundation 2013 with SP1
    5. Download Microsoft SharePoint Server 2013
    6. Sharepoint Designer 2013
    7. MS Project Server 2013 Download
    8. MS Project Professional 2013 Download
    9. Sharepoint 2013 in Download Center
    10. Sharepoint 2013 Composite Handbook
    11. SharePoint 2013 Upgrade Process
    12. SharePoint 2013: presentation: IT pro training
    13. SharePoint 2013 training for IT pros
    14. Sharepoint 2013 Developer Training
    15. Sharepoint 2013 – Developer training for Office, SharePoint, Project, Visio, and Access Services
    16. Installing SharePoint 2013 – Yasir Butt (Jerry)
    17. SharePoint 2010 UI VS SharePoint 2013 UI – Yasir Butt (Jerry)
    18. How to Install Sharepoint 2013
    19. Try Sharepoint 2013, Office 2013, Office WebApp 2013, ProjectServer 2013, Project 2013, Visio 2013, Exchange 2013, Lync 2013
    20. Install and manage apps for SharePoint 2013
    21. Test Lab Guide: Demonstrate Intranet and Team Sites with SharePoint Server 2013
    22. Test Lab Guide: Configure SharePoint Server 2013 in a Three-Tier Farm
    23. Use Office Web Apps with SharePoint 2013
    24. Traditional topologies for SharePoint 2013
    25. Downloadable eBook: Deployment guide for SharePoint 2013
    26. Language Packs for SharePoint Foundation 2013
    27. Language Packs for SharePoint Server 2013
    28. Language Packs for Microsoft Office Web Apps Server Preview
    29. SharePoint 2013 Technical Library in Compiled Help
    30. Databases that support SharePoint 2013
    31. Design Sample: Corporate Portal with Host-named Sites for SharePoint Server 2013
    32. Duet Enterprise for Microsoft SharePoint 2013 and SAP 2.0
    33. Back up and restore: SharePoint Server 2013
    34. Services on server install workseet for traditional SharePoint Server 2013 topologies
    35. Design Sample: Extranet with Dedicated Zones for Authentication for SharePoint 2013
    36. Search architectures for SharePoint Server 2013
    37. Enterprise search architectures for SharePoint Server 2013
    38. Design Sample: Corporate Portal with Path-based Sites for SharePoint Server 2013
    39. Microsoft Office 2013 Audit and Control Management Server
    40. Back up and restore planning worksheet



Security Enhancements in Office 2003

Wednesday, September 7th, 2005

Microsoft Office 2003 comes with a lot of security enhancements. To lower the security threats through office applications one should know what security features are available in office 2003 & how they work.

Office 2003 security technologies are divided in three groups:

a. Digital certificates and signatures
Digital Signatures
Code Signing
ActiveX Controls
Smart Tags
Macro Security Settings
Trusted Sources

b. Data protection and recovery
File Access Controls
Digital Signatures & Encryption
Personal Information Removal
Data Recovery with Auto Recovery
Anti Virus API
Information Rights Management (with RMS)
Permission Policies
Sharepoint Services

c. Application-specific features
Document Protection

With these security features, you can control access to confidential documents and lower security threats. Implementation of Office 2003 security requires better understanding of Office 2003 application, Windows platform security & other security services / products available from Microsoft like RMS, Certificate Services, GPO etc.

At the end don’t forget to update your Office applications with latest service packs & patches. That is a separate discussion (Patch Management) but you can use Office Update button on the home of Microsoft Office site to check & update your office applications with latest patches.