How to rectify Excel error “We couldn't get the data from table in the workbook…”

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:

The data table in my case is in the same file as the pivot table, there is no external connection.

When I look for any connection to the earlier (old) file in my new Excel file, no connections are found.

edited May 21 '18 at 7:46

fixer1234

18.2k144681

asked May 21 '18 at 1:44

Ralph

112

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

– ygaft
Jun 5 '18 at 13:27

add a comment |

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:

The data table in my case is in the same file as the pivot table, there is no external connection.

When I look for any connection to the earlier (old) file in my new Excel file, no connections are found.

edited May 21 '18 at 7:46

fixer1234

18.2k144681

asked May 21 '18 at 1:44

Ralph

112

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

– ygaft
Jun 5 '18 at 13:27

add a comment |

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:

The data table in my case is in the same file as the pivot table, there is no external connection.

When I look for any connection to the earlier (old) file in my new Excel file, no connections are found.

edited May 21 '18 at 7:46

fixer1234

18.2k144681

asked May 21 '18 at 1:44

Ralph

112

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:

The data table in my case is in the same file as the pivot table, there is no external connection.

When I look for any connection to the earlier (old) file in my new Excel file, no connections are found.

microsoft-excel microsoft-excel-2016 pivot-table office365

edited May 21 '18 at 7:46

fixer1234

18.2k144681

asked May 21 '18 at 1:44

Ralph

112

edited May 21 '18 at 7:46

fixer1234

18.2k144681

asked May 21 '18 at 1:44

Ralph

112

edited May 21 '18 at 7:46

fixer1234

18.2k144681

edited May 21 '18 at 7:46

fixer1234

18.2k144681

edited May 21 '18 at 7:46

fixer1234

18.2k144681

asked May 21 '18 at 1:44

Ralph

112

asked May 21 '18 at 1:44

Ralph

112

asked May 21 '18 at 1:44

Ralph

112

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

– ygaft
Jun 5 '18 at 13:27

add a comment |

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

– ygaft
Jun 5 '18 at 13:27

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

– ygaft
Jun 5 '18 at 13:27

add a comment |

2 Answers
2

active

oldest

votes

The possible reason for the problem you are facing:

The pivot cache is corrupted that the pivot table is linked to.

Solution 1, Auto Refresh:

Right-click any cell in the pivot table.

Click Pivot Table Options,then click the Data tab.

In Pivot Table Data section, add a check mark to "Refresh Data When
Opening the File".

Finish with OK.

Solution 2, Clear & Refresh Pivot Cache across multiple worksheets:

Private Sub Workbook_Open()

    Dim xPt As PivotTable

    Dim xWs As Worksheet

    Dim xPc As PivotCache



    Application.ScreenUpdating = False



    For Each xWs In ActiveWorkbook.Worksheets

        For Each xPt In xWs.PivotTables

            xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone

        Next xPt

    Next xWs

    For Each xPc In ActiveWorkbook.PivotCaches

        On Error Resume Next

        xPc.Refresh

    Next xPc



    Application.ScreenUpdating = True

End Sub

answered May 21 '18 at 7:03

Rajesh S

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?

– Ralph
May 22 '18 at 1:39

@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.

– Rajesh S
May 22 '18 at 6:25

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'

– Ralph
May 22 '18 at 21:05

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.

– Rajesh S
May 23 '18 at 9:05

add a comment |

Writing this down because this has happened to me twice now and both times I have not been able to find this answer anywhere and nothing else has worked for me. Both times I have resolved this issue via the Data tab > Connections, where I can remove the stale connection.

answered Dec 20 '18 at 0:44

meggie

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.

– Scott
Dec 20 '18 at 2:28

add a comment |

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1324521%2fhow-to-rectify-excel-error-we-couldnt-get-the-data-from-table-in-the-workbook%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

The possible reason for the problem you are facing:

The pivot cache is corrupted that the pivot table is linked to.

Solution 1, Auto Refresh:

Right-click any cell in the pivot table.

Click Pivot Table Options,then click the Data tab.

In Pivot Table Data section, add a check mark to "Refresh Data When
Opening the File".

Finish with OK.

Solution 2, Clear & Refresh Pivot Cache across multiple worksheets:

Private Sub Workbook_Open()

    Dim xPt As PivotTable

    Dim xWs As Worksheet

    Dim xPc As PivotCache



    Application.ScreenUpdating = False



    For Each xWs In ActiveWorkbook.Worksheets

        For Each xPt In xWs.PivotTables

            xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone

        Next xPt

    Next xWs

    For Each xPc In ActiveWorkbook.PivotCaches

        On Error Resume Next

        xPc.Refresh

    Next xPc



    Application.ScreenUpdating = True

End Sub

answered May 21 '18 at 7:03

Rajesh S

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?

– Ralph
May 22 '18 at 1:39

@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.

– Rajesh S
May 22 '18 at 6:25

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'

– Ralph
May 22 '18 at 21:05

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.

– Rajesh S
May 23 '18 at 9:05

add a comment |

The possible reason for the problem you are facing:

The pivot cache is corrupted that the pivot table is linked to.

Solution 1, Auto Refresh:

Right-click any cell in the pivot table.

Click Pivot Table Options,then click the Data tab.

In Pivot Table Data section, add a check mark to "Refresh Data When
Opening the File".

Finish with OK.

Solution 2, Clear & Refresh Pivot Cache across multiple worksheets:

Private Sub Workbook_Open()

    Dim xPt As PivotTable

    Dim xWs As Worksheet

    Dim xPc As PivotCache



    Application.ScreenUpdating = False



    For Each xWs In ActiveWorkbook.Worksheets

        For Each xPt In xWs.PivotTables

            xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone

        Next xPt

    Next xWs

    For Each xPc In ActiveWorkbook.PivotCaches

        On Error Resume Next

        xPc.Refresh

    Next xPc



    Application.ScreenUpdating = True

End Sub

answered May 21 '18 at 7:03

Rajesh S

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?

– Ralph
May 22 '18 at 1:39

@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.

– Rajesh S
May 22 '18 at 6:25

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'

– Ralph
May 22 '18 at 21:05

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.

– Rajesh S
May 23 '18 at 9:05

add a comment |

The possible reason for the problem you are facing:

The pivot cache is corrupted that the pivot table is linked to.

Solution 1, Auto Refresh:

Right-click any cell in the pivot table.

Click Pivot Table Options,then click the Data tab.

In Pivot Table Data section, add a check mark to "Refresh Data When
Opening the File".

Finish with OK.

Solution 2, Clear & Refresh Pivot Cache across multiple worksheets:

Private Sub Workbook_Open()

    Dim xPt As PivotTable

    Dim xWs As Worksheet

    Dim xPc As PivotCache



    Application.ScreenUpdating = False



    For Each xWs In ActiveWorkbook.Worksheets

        For Each xPt In xWs.PivotTables

            xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone

        Next xPt

    Next xWs

    For Each xPc In ActiveWorkbook.PivotCaches

        On Error Resume Next

        xPc.Refresh

    Next xPc



    Application.ScreenUpdating = True

End Sub

answered May 21 '18 at 7:03

Rajesh S

The possible reason for the problem you are facing:

The pivot cache is corrupted that the pivot table is linked to.

Solution 1, Auto Refresh:

Right-click any cell in the pivot table.

Click Pivot Table Options,then click the Data tab.

In Pivot Table Data section, add a check mark to "Refresh Data When
Opening the File".

Finish with OK.

Solution 2, Clear & Refresh Pivot Cache across multiple worksheets:

Private Sub Workbook_Open()

    Dim xPt As PivotTable

    Dim xWs As Worksheet

    Dim xPc As PivotCache



    Application.ScreenUpdating = False



    For Each xWs In ActiveWorkbook.Worksheets

        For Each xPt In xWs.PivotTables

            xPt.PivotCache.MissingItemsLimit = xlMissingItemsNone

        Next xPt

    Next xWs

    For Each xPc In ActiveWorkbook.PivotCaches

        On Error Resume Next

        xPc.Refresh

    Next xPc



    Application.ScreenUpdating = True

End Sub

answered May 21 '18 at 7:03

Rajesh S

answered May 21 '18 at 7:03

Rajesh S

answered May 21 '18 at 7:03

Rajesh S

answered May 21 '18 at 7:03

Rajesh S

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?

– Ralph
May 22 '18 at 1:39

@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.

– Rajesh S
May 22 '18 at 6:25

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'

– Ralph
May 22 '18 at 21:05

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.

– Rajesh S
May 23 '18 at 9:05

add a comment |

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?

– Ralph
May 22 '18 at 1:39

@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.

– Rajesh S
May 22 '18 at 6:25

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'

– Ralph
May 22 '18 at 21:05

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.

– Rajesh S
May 23 '18 at 9:05

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?

– Ralph
May 22 '18 at 1:39

@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.

– Rajesh S
May 22 '18 at 6:25

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'

– Ralph
May 22 '18 at 21:05

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.

– Rajesh S
May 23 '18 at 9:05

add a comment |

answered Dec 20 '18 at 0:44

meggie

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.

– Scott
Dec 20 '18 at 2:28

add a comment |

answered Dec 20 '18 at 0:44

meggie

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.

– Scott
Dec 20 '18 at 2:28

add a comment |

answered Dec 20 '18 at 0:44

meggie

answered Dec 20 '18 at 0:44

meggie

answered Dec 20 '18 at 0:44

meggie

answered Dec 20 '18 at 0:44

meggie

answered Dec 20 '18 at 0:44

meggie

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.

– Scott
Dec 20 '18 at 2:28

add a comment |

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.

– Scott
Dec 20 '18 at 2:28

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.

– Scott
Dec 20 '18 at 2:28

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Super User!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Gfrktyl