Unable export to excel from Sharepoint List using "Export to Excel" Built-in function?

[ca_rem]

Hi.

I have a question abount the "Export to Excel" built in function in SharePoint List.

I download query.iqy file from this Button.

I use Excel to Open it.

But I enable and this error appear: An unexpected error was occurred. Changes to your data cannot be saved

I checked my credential of Office Application and It's the same with the account using sharepoint list, in the image below it is "MS 365 account 1". But only the source when I install this application from the "MS 365 account 2" but 2 account from the same organization.

How can I fix this error?

[#2]

Hi @ca_rem - I've been running into this a lot lately. My workaround is to

1. Go to the Site Assets Library on the same site
2. Create an Excel file
3. Data > Get Data
4. Connect to SharePoint Online list

Hope that helps!

[#3]

@Kelly_Edinger 

Follow your instruction. I do:

1. Done

2. Done

3,4. You mean I open Excel Application on Window and go to Data > Get Data > Connect to SharePoint Online list. Right? My steps is below.

But what's next?

I go back again to the "query.iqy". But nothing changes. The error's still there.

Can you give me some advices?

[#4]

Hi @ca_rem - when you select your list, it should populate the Excel. What's happening after you select your list?

[#5]

@Kelly_Edinger 

When I choose a list and load this. It will open a new Spreadsheet with some column of my items, but I see many columns like system data column like: FileSystemObjectType, ServerRedirectedEmbedUrl, GUID

I have 2 option here, If I choose Load, It opens a new spreadsheet:

If I choose Transform Data, I open a new Excel Window - Power Query Editor (Something like Access):

I don't know what you see when you open the file query.iqy is the same what I see with this method? My goal is to display the columns in the All Item View of the List or in a specific view if it could through open the query.iqy file.

[#6 · Accepted solution]

Hi @ca_rem - the initial load does put in several columns that you may not be interested in. all you have to do is hide or delete those columns. whenever you open and refresh the data, it will keep those columns hidden. I've used this successfully several times. I have one spreadsheet that is connected to 3 lists and then have built the pivot tables on new tabs in the same workbook.

[#7]

@Kelly_Edinger 

Can you explain why I can't open the file "query.ipy" directly.

Sometimes I need to share the file to someone.

[#8]

Hi @ca_rem - no, I can't explain that. That's why I use the workaround of creating the Excel first and keeping it as an Excel. I open up the file and refresh the data and bypass the iqy altogether. Sorry I couldn't help more.

[#9]

Hi @ca_rem,

Just found your issue on the web. Since im facing the same issues as well. Hopefully this helps but i found a work around for this. If you need to export to excel, switch to classic experience and select export to excel. You should be able to open the excel file now. Hope this helps. 

Thanks.

Xander

[#10]

Landed on this thread today with a similar problem.  I was not able to open my downloaded query files in an excel spreadsheet. All I did was to sign in to my excel through the office and click my autosave on. Make sure you are also signed-in to excel via office The query files data showed up perfectly!

[#11]

Is there a solution for this one already?? Im also looking for this. Thanks

[#12]

I had same issue and managed to resolve it by two simple steps.
First I tried to add new key into registry according MS documentation: https://learn.microsoft.com/en-us/office/troubleshoot/excel/cannot-export-to-excel

then I completely sign-out all accounts from Excel and tried it again.

Hope this will help to someone :)