[Client-side Workaround] SharePoint 2010 Excel Services cannot Automatically Refresh Data when using SharePoint Lists as a Data Source

So far as I can tell, Excel Services is not capable of automatic data refreshes when using SharePoint Lists as a data source. If you’re looking for this functionality, you have two choices: PowerPivot (best) or scheduled client-side data refreshing (ghetto).

In order for Excel Services to communicate with other data sources to do data refreshes without your intervention, it relies on SharePoint’s SSO Service, the Secure Store. There are three different methods of connecting – Unattended Service Account, Embedded Connections, & External Data Connections – and I tried all three of them in various configurations but kept running into Access Denied errors. I even tried some of that voodoo magic we were taught growing up and it didn’t even help.

I finally stopped trying after reaching a dead end on the Excel Services data refresh flowchart then further confirming said dead end within the ULS which informed me that there was an “Unauthorized attempt to access session by user <username>. Session belonged to user <username of user that started the session> (Event ID: 2011).” Seems that SharePoint does not like Excel Services asking to see its lists.

To confirm, I looked up the error in the SharePoint Technical Reference and it said

Excel Services Application maintains individual user sessions. Sessions maintain state related to workbook calculations, parameters that a user sets, interactions that a user has with a live workbook, and data returned from a data source as a result of a data refresh operation. Sessions are maintained per user per workbook, and can contain private data and information. Sessions are available only to the user that started the session. The issue in this case is that a user who did not start a session attempted to access data from that session.

(Emphasis mine)

FFFFFINNNNEEE, Microsoft. Until we get PowerPivot implemented, I’ll just make PowerShell do my dirty work. The script below, which you can schedule, assumes the following:

  • your workstation will be on most of the time
  • you have Excel 2010 Professional installed
  • you can use Windows Explorer view of libraries
  • versioning is turned on and limited to a certain number of versions
    (this makes 24 versions a day)
  • that you want EVERY Excel file in a library to be refreshed
  • you have write permissions to all excel files
  • you have access to do so and the files are checked in

So, it goes to the document library, searches recursively for all Excel files, opens them up, checks them out, opens them, refreshes the data connections, saves the file then checks them in with a new version. K, here goes:

For Document Libraries that require checking out files prior to editing them:

And this code is for libraries that do not require check-out:

Wanna schedule this script hourly? schtasks /create /tn RefreshData /tr “powershell -noninteractive -nologo -command C:\scripts\refresh.ps1” /sc HOURLY. Don’t forget to limit Versions in your library as this script creates 24 versions of each workbook per day.

Also, this script is 10x’s faster on Windows 7 if you disable the use of the Web Proxy Autodiscovery Protocol (WPAD). IE -> Tools -> Internet Options -> Connections -> LAN Settings -> Uncheck Automatically Detect Settings.

And finally, if you find that your files are opening Read-only, make sure all instances of Excel are closed, then delete your Microsoft Office cache in %userprofile%\local settings\temporary internet files\content.mso\.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain RealCajunRecipes.com in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in BI, PowerShell, SharePoint
10 comments on “[Client-side Workaround] SharePoint 2010 Excel Services cannot Automatically Refresh Data when using SharePoint Lists as a Data Source
  1. Misiel says:

    Hi
    Did you manage to refresh data using "View in browser" -> "Data" -> "Refresh All Connections" because i'm still getting error. (Secure Store is configured)

    • That behavior is expected. That type of refresh is a server-side refresh, not a client one. I know it seems possible because you're using the browser as a client, but Excel Services renders it on the Server side then presents it to you.

  2. Luís says:

    Hi Chrissy, thank you for this set of articles :)

    Did you manage to get PowerPivot working? I'm exactly at this point but I find the powershelll and schedule solution as a last resort, since. There are so many details to remember and care about in Sharepoint, that if I can provide a solution that gets this "out-of-the-box" then I'll not look back lol

    thanks

    • @cl says:

      It's up to the Farm Administrator to install and configure PowerPivot — I've done it in a lab, but not in production, as I am not the farm administrator (thankfully!)

  3. sunny says:

    Oh thank you so very much for this. It's a great way to work around the authentication / refresh issue.

  4. Amy Jo says:

    Will this solution work for Sharepoint Online 2013 Plan 2 and Excel 2013?

    • @cl says:

      I don't know if SharePoint Online supports WebDav, but if it does, this should work. I believe I have used the script in Excel 2013 with no issues (though it's been awhile)

  5. Kavi says:

    Can you please tell me how to this code to document library. I am new to this. Thanks in advance.

  6. Kavi says:

    As well as how to schedule in detail.

  7. Ishfaque says:

    I have tried this script and it runs without any error but without refreshing Excel 2013 Connection with SharePoint 2010 List. I have Web Client Service up and running so does Web Dav. Please suggest me what should i do to make it refresh my connection.

Leave a Reply

Your email address will not be published. Required fields are marked *

*