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.
9 Answers
Additional detail to @meggie's response above. I suspect this has to do with corrupt Data Model connections.
- Pay attention to the error message – it will list the name of the broken connection in quotes
- Data -> Queries and Connections to open the sidebar
- Click on Connections tab
- Hover over each item and look for one that matches the name in the error message. Delete.
I had this problem and mangaged to solve it by changing the table`s name back to the original name, using "Table Name" field under the "Table Design" Tab. It seems Excel changes the name of a table if you modify a table e.g. add a column, then when the data model tries to update it cant find the original name.
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 SubWriting 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.
1I had a very similar situation: same error, but it turned out to be a different root cause. The data was being referenced internally from a table, but that table couldn't be found by Excel, even though it was obviously open already.
The issue ended up being that the newer version of the file had square brackets in the filename, which are not illegal characters in Windows but do cause problems.
By renaming the file and removing these square brackets the issue was resolved.
If you regularly revise workbook names and wish to change ALL Pivot source names then this if the type of thing that may work for you. Its not great but gets the job done.
The script loops through every Pivot Table in the active worklbook and removes any external file references from the Pivot Source by searching for the "!" mark.
Sub ChangePivotSource() Dim wb As Workbook Dim ws As Worksheet Dim pt As PivotTable Dim wsPT As Worksheet Dim PTCount As Long ' Pivot Table Counter Dim strSD As String ' Used to store the Origional Pivot Source Dim strRevised_Source ' Used to store the Revised Source On Error Resume Next Set wb = ActiveWorkbook ' Count the number of Pivot Tables For Each wsPT In wb.Sheets If wsPT.PivotTables.Count Then PTCount = PTCount + 1 End If If PTCount > 0 Then Exit For ' Just need to know if at least one Next wsPT ' If No Pivots then Exit If PTCount = 0 Then Exit Sub End If strRevised_Source = "" ' Loop through all worksheets in the workbook For Each ws In wb.Worksheets ' Loop through each Pivot Table of the worksheet For Each pt In ws.PivotTables strSD = pt.SourceData ' Correct the Source if it contains an Exclamation Mark If InStr(strSD, "!") > 0 Then 'We have an External File Reference strRevised_Source = Right(strSD, Len(strSD) - InStr(strSD, "!")) pt.SourceData = strRevised_Source End If strSD = "" strRevised_Source = "" Next pt Next ws End SubI am assuming that your data resides on the same Workbook.
Here are the steps:
- Give a name to the data range using Ribbon->Formulas->Name Manager
- Use the name (say Log!LogData) inside the query directly as shown below:
let source = Excel.CurrentWorkbook(){[Name="Log!LogData"]}[Content], Misc Notes regarding other aspects of Power Query:
- It is useful to remove blank rows afterwards.
- Next, you can promote the 1st row to become the header row.
- Then, you can select the columns which you want using the following code
#"My columns" = Table.SelectColumns(previousTable,{"column A","name B"}, - To replicate index-match functionality, you can merge queries together.
Here's a method that worked for me:
- Select the pivot table that won't update
- Go to "Change Pivot Table Source"
- In the "Table/Range" box, select & copy only the "name of the file" within the link.
- Close the excel file > rename the file, pasting the name you copied into the file name. > Reopen file and refresh data.
I fixed the problem by removing the connections that were failing. Click Data - > Queries and Connections - > Under the connections tab - remove the connections that are giving you the error
1
