Exporting a SharePoint list to excel is one of the many great functions of office 365. Whether you just want to save a snapshot of your list for archival purposes, or you just want to pull the data into a familiar format for more detailed offline inspection; exporting lists as excel files can come in handy in a lot of situations.
This seamless integration of the Office tools that have become critical to our daily lives is what makes the office 365 platform so valuable to businesses of all sizes. Unfortunately, with all the background processes, updates to different versions of office and all of the different browser settings, seamless isn’t always the first word that comes to mind.
The other day I got an interesting support case where a user that was previously able to export SharePoint lists as excel files “seamlessly” was now getting a mysterious error message.
Because of the aforementioned bits and pieces in motion, it can be hard to find the exact cause of the problem so your best bet is to start with the most likely causes and work your way to a solution via process of elimination. With just a bit of patience you can get back to exporting your lists in no time!
Yourself or one of your users used to be able to export a SharePoint list as an excel file but now receives the following -not all that helpful- error message.
You’re going to want to start by getting some basic information together so you can eliminate some other possible causes.
- Does this effect multiple lists and libraries?
- Has their version of Office been updated recently?
- What browser using when they attempted to export?
- Does the issue persist regardless of browser?
- What version of office is currently in use?
- What type of system is the user’s computer running (32bit/64bit)?
- Does the user have correct permissions to read/write at both the list and site level?
Once we have narrowed down that the issue is most likely not coming from SharePoint, we have a pretty clear idea of where we need to go to solve this.
- The problem exists on all lists and libraries, not just one
- No other error message is present
- The user is never asked to authenticate with log in credentials
- The user has full permissions to read and write at both the site and list level
- Issue only present in Internet explorer
- User recently updated office
If you answered yes to the previous questions or if any of those symptoms seemed familiar, then one of the subsequent solutions just might do the trick!
Solution 1: The best bet
Everyone loves the automatic updates that come with Office 365, but every now and again something can go wrong in the background. Sometimes you might have two different versions of office running that could cause problems or any other number of things. Lucky for you, repairing office is generally a painless chore.
I would recommend trying this first as it is the most common cause of this type of error and will rule out a great deal of potential problems before you move on to the other steps.
To repair office, make sure you are signed in as an administrator on your machine and all office programs are closed (including skype for business).
1) Navigate to >Start >Control Panel >Programs >Programs and Features
2) Find your version of office. If you downloaded office as a one-time purchase it should be there under Microsoft Office + your year of office (ex: Microsoft Office 2016). If you’re a cool guy like me, it will be in there as office 365.
3) Select Change
4) Select Repair Online
Follow the steps in the wizard, it may prompt you for some interaction and you may have to reboot your computer for the changes to take effect. Go back to the SharePoint list and try again! If it worked you can thank me later, if it didn’t then let’s try something else.
Solution 2: Force Authentication
A potential cause of this problem could be that excel isn’t getting the proper authentication credentials dynamically from SharePoint when you try to open the file on your desktop. We can try to force excel to make us log in to ensure that this isn’t what is causing the issue.
1) Open a new instance of excel
2) Go to >File >open >add location
3) Select “Office 365 SharePoint”
4) It will ask you for an email and password, Use the login credentials for the site you are trying to export a list from
5) You will be presented with a “tree view” of the sites within that tenant and their respective lists.
6) Select the target list
7) If it asks you to login again, do so with the same credentials as before
8) Click cancel but keep excel open!
9)) In a new browser session, navigate to the list you would like to export
Try to export the list to excel again. Still not working? Then perhaps your browser is the culprit, read on if you are using internet explorer!
Solution 3: I should have known IE was to blame
As with all things windows, the root of all evil is most often internet explorer. Sometimes through updates or user errors, crucial add-ins that are needed for internet explorer to interact with many of the features in SharePoint may have been disabled.
1) Open internet explorer, then click Tools (cog wheel)
2) Select Manage Add-Ons
3) In the drop down on the left hand side, set it to Show: All Add-ons
4) Under the section for Microsoft Corporation, scroll through the list of add-ons and verify that all that are related to SharePoint or Office are enabled. Most importantly, you want to make sure that the OpenDocuments class is present and enabled.
Miscellaneous Problem Points
Hopefully at this point your issue has been resolved. If one of these methods doesn’t get you to the outcome you were looking for, it’s time to start looking at more creative causes/solutions. Here is a brief overview of some questions that might get you started in the right direction
How many items are being rendered in the view you want to export?
When you export a SharePoint list as an excel file, you are not exporting the whole list. Rather, you are exporting whichever specific view of the list you are currently rendering.
Try changing the view to something more filtered or create a new one that returns only a few list items as a test.
What kind of columns are in the view?
Many times, excel can have trouble converting Person/group or lookup columns in to fields in the spreadsheet. Try making a view that doesn’t show any of these fields to test if that is your problem.
What kind of internal names do the columns have?
Another common issue is when excel tries to make a column out of a field with a poorly conceived internal name. For example, My cool & awesome column!! might sound like a sweet column to have (it is pretty sweet), but the internal name created will look something like:
If you have tried all else and you are still banging your head against the wall, take a look at some of the internal names of the fields to determine whether or not that could be an issue!