How to rectify Excel error "We couldn't get the data from table in the workbook..."

Question

I am dealing with the following problem using MS Excel 2016:

• The Excel file contains a huge data table and several pivot tables in separate sheets.
• The current Excel file was saved under a new file name from an earlier file version.
• Now, when trying to 'refresh all' pivot table data in Pivot Table tools Analyze ribbon, the following error message appears, referring to the earlier file's filename:

We couldn't get the data from 'Table1' in the workbook 'Oldfile.xlsx' Open this workbook in Excel and try again.

The strange thing is:

1. The data table in my case is in the same file as the pivot table, there is no external connection.
2. When I look for any connection to the earlier (old) file in my new Excel file, no connections are found.

Solution

Additional detail to @meggie's response above. I suspect this has to do with corrupt Data Model connections.

1. Pay attention to the error message – it will list the name of the broken connection in quotes
2. Data -> Queries and Connections to open the sidebar
3. Click on Connections tab
4. Hover over each item and look for one that matches the name in the error message. Delete.

Comments

[ygaft]

Check the "data source" while you are on a PivotTable, go to Analyze ribbon and find 'Change Data Source", check to where your pivot is connected

[icj]

my best advice is to recreate the pivot tables from scratch. After pulling my hair out for hours, that was the only solution. classic excel.

[Ralph]

Hi Rajesh, I applied solution1 and it seems that the same setting is active for all other Pivot Tables which makes it easier for me since I have approx. 40 of them. Also, I think this is in any case a helpful setting, so appreciate that! I also added the macro, reopened the file to let it run and also ran it again manually. The error message keeps popping up. Is there any area in the depths of Excel where I can somehow directly search for the connection / old file reference shown? Do you have any other idea what I could possibly try in order to solve this issue?

[Rajesh Sinha]

@Ralph, You mean to say that Solution 1 is working but not the 2nd One? Actually the VBA solution is quit capable to handle the Cache issue with as many PT & WKSTs. Give me some time to find the alternative method.

[Ralph]

Hi Rajesh, sorry for not being specific enough: both solutions don't rectify the problem, i.e. the error message keeps popping up when I click on 'refresh all'

[Rajesh Sinha]

May possible you have some different issue. I've suggested both solutions on the basis of the question you have posted and are the most appropriate to the issue.

[Scott - Слава Україні]

Nice to know that you solved your problem.  Can you provide some detail regarding what you do and how you do it? … … … … Please do not respond in comments; edit your answer to make it clearer and more complete.

[Alex M]

Not sure this adds anything that isn't covered by the existing top-voted answer that says the same thing in more detail