4

I have an excel file that have a data connection with sharepoint list. I want my excel file to be automatically update when something changes on my Sharepoint list. So in excel I choose:

Data > Connection Properties

and there I saw option 'Refresh every x minutes` grayed out.

  • How can I turn it on?

  • Or maybe there is another possibility to auto refresh excel file when item is changed or created on my SharePoint list?

Edit: To create my excel file I exported list from my SharePoint site using option: 'Export to Excel' and it created a new excel file with connection

5
  • In Excel's options there should be "Refresh data when opening the file option"-option. Whether this is applicable for you or not, having it enabled disables the "Refresh every x minutes" option.
    – moe
    Jul 13, 2017 at 11:51
  • 'Refresh data when opening the file option' is disabled. And 'refresh every' is still grayed out
    – Daniel
    Jul 13, 2017 at 11:54
  • Which version of Excel you are using - 2010, 2013, 2016?
    – moe
    Jul 13, 2017 at 12:00
  • My version is 2013
    – Daniel
    Jul 13, 2017 at 12:02
  • Please edit your question to provide the specific steps you used to create the data connection. did you use powerpivot? PowerQuery? Or is this an OData connection?
    – Mike2500
    Jul 13, 2017 at 13:43

1 Answer 1

1

Follow the bellow steps:: Here i am using Excel 2013

Step1:: Open new excel file in system.

Step2:: Choose 'Data' tab -> Choose 'From Other Sources' tab -> choose 'From OData Data feed'

Step3:: Provide link URL like - site url(your list existed site) + "/_vti_bin/listdata.svc"; EX: https://nagababuSPD.com/TestSite/_vti_bin/listdata.svc

Step4:: Provide logon credentials(Username, password) and click on Next.

Step5:: Choose required list from popup and click on Next and click on Finish.

Step6:: Now we got 'Import Data' pop up, here click on Properties button. Here 'Connection Properties' pop up showing, here check the option 'Refresh Every' and provide required minutes. Click OK and again click OK on 'Import Data' pop up. File filled with SharePoint list data. Here you got extra columns(predefined columns of list). Hide unnecessary columns in excel file and save it. From now on wards every X minutes(specified refresh time) it's refresh data automatically.

Hope this helps you. If this is helpful to you Up-vote my answer :).

4
  • That helped me to use 'refresh every' option. Thanks :) However now I see that this option refresh file only when it is opened. Is it possible to refresh an excel file that is in SharePoint library without opening it? So I need: excel file on sharepoint site (in library) that will be refreshed automatically without being opened
    – Daniel
    Jul 14, 2017 at 7:58
  • 1
    In Office 365 it is possible but in SharePoint 2013 on premise it's not possible directly. Power pivot should be installed on SharePoint Server to refresh excel file in document library. For more info see this blog blogs.technet.microsoft.com/… Jul 14, 2017 at 9:59
  • I guess that using Power pivot for SharePoint is only available for admin of server?
    – Daniel
    Jul 14, 2017 at 11:15
  • I guess, for configuration of Power Pivot we need Farm admin credentials, after configuration done, normal SharePoint permissions should work... Jul 14, 2017 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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