1

I have 2 SP Lists exported to an Excel Workbook, which I saved in a library and added an Excel Web Access. The EWA is basically combining data from 2 lists, with. The workbook data connection is set to refresh on open. I also set the EWA to periodically refresh. Tried the EWA menu to Refresh All Connections but the data in EWA didn't refresh.

Is there a way to automatically refresh the EWA upon accessing the SP page? I found this link but its for SP 2010. I can't find the 'Excel Services' in the SP Admin Center.

2

SharePoint online use Excel Online Web App instead of using Excel service.

Follow the steps below to add connection to SPO list instead of using “Export to Excel” function, see if the refresh works:

  1. Create a excel file in client.
  2. Get external data from ODate Date Feed. enter image description here
  3. Link to https://tenant.sharepoint.com/yoursite/_vti_bin/listdata.svc.
  4. Select the lists which you want and click Next.
  5. Enter the information such as file name for this data connection and click Finish.
  6. View this data as a table in the workbook.
  7. Edit this data connection properties, set the refresh control to refresh data when opening the file or other refresh options as you want.

    enter image description here

  8. Save the file and upload the file to SPO site.

Per my test results, the data in the workbook will refresh automatically if I has enabled the refresh control.

  • Sorry for the late response and thank you. I have been away but finally able to try this out. Is there a way I can connect to a list view rather than the entire list? I only want certain data. – Tamras Merin Oct 30 '17 at 21:29
  • I used the data connection (as instructed above) then created a new query from this connection. But my EWA fails to refresh the query. I can't seem to find any solution online regarding this. – Tamras Merin Oct 31 '17 at 16:37
  • Ive tried several times, now whenever I open the excel workbook in SPO, it keeps giving me an error message: "We found a problem with some contents in 'Book1.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes." I repaired and saved the workbook with the same result. Same result with new workbook with OData Data Connection. – Tamras Merin Oct 31 '17 at 18:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.