Excel when connected to external data: “We found a problеm with some content in [file]”

I have created a spreadsheet in Excel 2013 that gets data from a CSV file, then made a pivot table based on the raw data and a chart to visualise it. The connection was also added to the data model and was configured to refresh upon opening the spredsheet.

However, whenever I open the file I get the error message:

We found a problem with some content in [file]. Do you want us to try
to recover as much as we can? If you trust the source of this
workbook, click Yes.

After I click “Yes”, Excel tells me it “was able to open the file by repairing or removing the unreadable content”.

The data seems to update fine. When I save the file again, Excel goes through the “Save as” routine, but lets me overwrite the file anyway.

This process is repeated every damn time I open the file.

• If I unlink the raw data table from the CSV file, the problem disappears.
• If I reconnect to the CSV file, it problem re-appears.
• Even if I remove all other sheets, or start from a new file, and add the CSV file, the problem re-appears.

After much Googling, trial and error I finally stumbled across this thread on Microsoft Answers, where Sridhar suggests this workaround (emphasis mine):

This happens only if I “Add to data model” AND set the “Refresh data
when opening the file” during the creation of the connection.

If I dont set the “Refresh data when opening the file” and create the
table and later go to “Connections -> Properties” and it set it again
then this does not happen.

I can confirm that this solved the issue in my case too.

This is the checkbox to leave unchecked when adding the connection:

To enable this option after creating the connection:

1. Click on the “Data” tab, then “Connections”

3. Select the appropriate connection (not the data model), click “Properties”
4. Check “Refresh data when opening the file” (see screenshot above)
5. Click “OK”, then “Close”

I had the same issue on my Mac with Excel 2011. Don’t know what happened to face this issue. But anyway, I solved it this way:

1. Open file with 7-zip (no unpacking!)
2. Open folder ‘xl’ and delete file connections.xml (everything done in 7-zip app)
3. Close 7-zip

Now, my file could be opened without any error. Of course, I kicked all ext connections, but his is ok for me. I don’t know whether the file connections.xml could be edited to change only some of the links.

As an update on the last answer so that you retain your connections:

1. Open file with 7-zip (no unpacking!)
2. Open folder ‘xl’
3. Edit connections.xml
4. Change refreshOnLoad=”1″ to refreshOnLoad=”0″
5. Close and save, update in the archive.
6. Close 7-zip

The file should now open without errors. You can turn on the refresh on load and seems to work fine.

An excel file containing a MS SQL Query which returns no data (except for the headers) will also give you the same prompt when opening the file (“We found a problem with some content in [file]”). If you remove those queries, the prompt will stop.