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

microsoft excelmicrosoft-excel-2013

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.

We found a problem with some content in "file"

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

Repaired records

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.

Best Answer

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:

Connection Properties: Refresh data when opening the file

To enable this option after creating the connection:

  1. Click on the "Data" tab, then "Connections"

Data/Connections

  1. Select the appropriate connection (not the data model), click "Properties"
  2. Check "Refresh data when opening the file" (see screenshot above)
  3. Click "OK", then "Close"
Related Question