Options for Updating Excel displayed thru Excel Web Access ?

Options for Updating Excel displayed thru Excel Web Access ?

• TommyGL

  It is something we're looking at, though it's too early for me to give a timeline or a definite yes/no answer.

  I wouldn't think it would be a stretch to see that feature in upcoming versions though.

  
  
  

• Rudemusik

  I am having a similar problem. Here is the background:

  I have InfoPath forms that collect data and are then pushed up to a forms library on MOSS 2007. I promoted the appropriate fields from InfoPath into columns in the form library. I then selected the 'Actions/Export to Spreadsheet' command to get the document library's data into an excel spreadsheet as an external connection. I then exported the resulting data connection file to a 'Trusted Data Connection Library' in MOSS 2007. I then created some pivot tables based on the data received from those external connections. When I am in EXCEL locally I can refresh those connections fine. I then uploaded the excel workbook to a 'Trusted File Location' for Excel services and created a dashboard with Excel Web Parts to view the pivot tables.

  The problem: The Web Parts are not able to refresh the data from the SharePoint Library. Works fine in Excel, not in Excel Services. When I go through the Trace Logs I see something to the effect of : "Unsupported Provider Microsoft.Office.List.OLEDB.2.0" which is the provider that is used when you connect Excel to a SharePoint library.

  So the big question is this: Can Excel Services refresh an external data connection to a SharePoint Library

  
  

• wencey

  Starkman wrote:

  I am having a similar problem. Here is the background: I have InfoPath forms that collect data and are then pushed up to a forms library on MOSS 2007. I promoted the appropriate fields from InfoPath into columns in the form library. I then selected the 'Actions/Export to Spreadsheet' command to get the document library's data into an excel spreadsheet as an external connection. I then exported the resulting data connection file to a 'Trusted Data Connection Library' in MOSS 2007. I then created some pivot tables based on the data received from those external connections. When I am in EXCEL locally I can refresh those connections fine. I then uploaded the excel workbook to a 'Trusted File Location' for Excel services and created a dashboard with Excel Web Parts to view the pivot tables. The problem: The Web Parts are not able to refresh the data from the SharePoint Library. Works fine in Excel, not in Excel Services. When I go through the Trace Logs I see something to the effect of : "Unsupported Provider Microsoft.Office.List.OLEDB.2.0" which is the provider that is used when you connect Excel to a SharePoint library. So the big question is this: Can Excel Services refresh an external data connection to a SharePoint Library

  
  
  

• RLRTech

  You have a couple options:

  1. Use Excel as you mentioned... easy, but it adds an extra step to your workflow (open in Excel, edit manually, resave).
  2. Keep the data in a database (the SharePoint list scenario you mention). If you use SQL or AS this is all supported out of the box. If you use SharePoint lists you will need to write a UDF as connections to SharePoint lists are not supported on Excel Services and would require a little code which you're trying to avoid.

  I think #2 is your best bet here, especially if you can store your data in SQL. You can create a PivotTable coupled with a PivotChart on the data which means every time you refresh you'll get the latest stuff.

  If SQL/AS is out of the question and you aren't 100% averse to using code you can write a little code to implement SharePoint lists in Excel Services, and use the SharePoint list as your data source. See my blog for more info.

  
  
  

• tampa_dba

  I just found the appropriate answer in your blog: http://blogs.msdn.com/luisbeonservices/archive/2006/09/28/consuming-sharepoint-lists-in-excel-services.aspx.

  Are there plans to add this functionaity to Excel Services The environment that my solution is being developed on prohibits custom code.

  
  

• Stephen Westlake

  Ian,

  Did the above post answer your question

  Let us know if there's more info needed, otherwise I would greatly appreciate if you marked the post as Answered so we can get it off the radar.

  Thanks!

  
  
  

• ImGivingUp

  Luis,

  If those are the only two options, then I guess it did, thanks. I was hoping there would be a third option that didn't involve an intermediate database as this would be a bit beyond the scope of power users. Excel is ok, but it's not as good a data entry environment as InfoPath.

  Thanks

  Ian

  
  
• Options for Updating Excel displayed thru Excel Web Access ?