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 . Session belonged to user (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:

# This is the location of the document library that has the Excel files

You must have WebDAV enabled on the server (which is default, I think)

and the webclient service enabled and running on your workstation.

$library = "\\sharepoint.ad.local@SSL\DavWWWRoot\Shared Documents"

Start Excel (it will be invisible unless you do $excel.visible = $true)

$excel = new-object -comobject Excel.Application

Give Excel time to open or it errors inconsistently

Start-Sleep -s 3

$excelfiles = get-childitem $library -recurse -include "*.xls*"

foreach ($file in $excelfiles) { $workbookpath = $file.fullname

if ($excel.workbooks.canCheckOut($workbookpath)) { # open the worksheet and check it out $excelworkbook = $excel.workbooks.Open($workbookpath) $excelworkbook = $excel.workbooks.CheckOut($workbookpath)

# Don't ask cuz I don't know (yet). You have to open it again.
$excelworkbook = $excel.workbooks.Open($workbookpath)

# Refresh all the pivot tables with the new data.
$excelworkbook.RefreshAll()

# Save and Check it in
$excelworkbook.Save()
$excelworkbook.CheckInWithVersion()

} } $excel.quit()

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

# This is the location of the document library that has the Excel files

You must have WebDAV enabled on the server (which is default, I think)

and the webclient service enabled and running on your workstation.

$library = "\\sharepoint.ad.local@SSL\DavWWWRoot\Shared Documents"

Start Excel (it will be invisible unless you do $excel.visible = $true)

$excel = new-object -comobject Excel.Application

Give Excel time to open or it errors inconsistently

Start-Sleep -s 3

$excelfiles = get-childitem $library -recurse -include "*.xls*"

foreach ($file in $excelfiles) { $workbookpath = $file.fullname

# open the worksheet 
$excelworkbook = $excel.workbooks.Open($workbookpath)

# Refresh all the pivot tables with the new data.
$excelworkbook.RefreshAll()

# Save and Close
$excelworkbook.Save()
$excelworkbook.Close()

} $excel.quit()

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