Excel fails to download SharePoint List data

Federico Coppola 1,181 Reputation points
2020-10-29T19:38:13.877+00:00

Hello,
I have an Excel file that has got a query.
This query permit to access to a List of SharePoint and download data from this list.
In this way accounting employees can easly read data from this list using a simple Excel document.

It worked fine for a long time, but not now.
In fact user receive this error when they try to get data from the SharePoint List using Excel File:

You do not have permission to view the entire list because it is larger than the list view threshold enforced by the administrator

I have checked SharePoint List and I have seen that this List has got more than 5000 elements

36108-immagine.png

After that I have tried to create a filter of this list but without success.

Is there a way to collect just the last 500 records (for example) inside the Excel File and don't receive the error message?
I think that Excel File try to download all SharePoint List records.

This is the query inside Excel File:

36046-immagine.png

How can I solve this issue?
Thanks so much for your help
Federico

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,617 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,640 questions
0 comments
Accepted answer
  1. Federico Coppola 1,181 Reputation points
    2020-11-17T09:30:41.527+00:00

    Hi all,
    I have read again all your answare to support me and I have found the solution!

    1) I went on SharePoint list and Edit settings of SharePoint List
    At the moment I have a "main view" of this SP List and this view has got filters.

    2) I have created a new view called "ExcelExport" starting from the existing "main view"

    40240-image.png

    3) I removed all filters and I checked that all variabiles has got default values
    40372-image.png

    4) I open in my browser the list using this view so I can catch query for excel file
    40392-image.png

    I deleted old query from Excel file and I imported the new query that use the new view (dedicated to Excel export). My file works again!

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Elsie Lu_MSFT 9,761 Reputation points
    2020-10-30T09:04:50.61+00:00

    Hi @Federico Coppola ,
    Per my test, I cannot reproduce your issue. I created a new test list with 5800 pieces of data, however I opened it in excel successfully. You can try to create a new view to limit the number of items. Please go to the list settings>Create view, then create filters according to your needs and limit the number of returned items to less than 5000. Then export it again after setting it as the default to see if it works.
    36319-10302.jpg36322-10304.jpg
    In order not to cause misunderstanding, you can set the new view as the default.
    36303-10305.jpg

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Federico Coppola 1,181 Reputation points
    2020-10-30T18:51:52.483+00:00

    Hi @Elsie Lu_MSFT ,
    thanks for your reply.

    I have checked SharePoint List configuration and I have noted that there are different views of this List.

    36471-immagine.png

    Query that is present inside Excel File "talks" about a <VIEWGUID>{8E9FBF4A-40AD-4A27-A5C0-4280FB9XXXX}</VIEWGUID>

    Is it possible to know the correspondence between ID of the viewgui and the name of the SharePoint List view?

    36453-immagine.png

    Finally I have seen that all SharePoint List View has got "Items Limit" as 30 as you can see here (this is the main view of this SP List for example):

    36463-immagine.png

    Thanks so much
    Federico

    0 comments
  3. Federico Coppola 1,181 Reputation points
    2020-11-01T20:45:03.15+00:00

    Any ideas?

    Thanks so much in advanced
    Federico

  4. Federico Coppola 1,181 Reputation points
    2020-11-03T15:04:03.217+00:00

    Dear @Elsie Lu_MSFT ,
    Thanks so much for your help.

    I will check it as you suggest

    I will keep you updated.

    Thanks so much
    Federico

    0 comments