4

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.
2
  • 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, 2018 at 13:27
  • 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.
    – icj
    Aug 23, 2019 at 16:11

10 Answers 10

4

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.

Connections Tab full of connections, one of which was broken

1

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.

1

I experienced this error while working in a workbook hosted in SharePoint. The filename initially contained brackets []. Renaming the file without brackets fixed the issue.

1
  • Would've never thought about that, many thanks for sharing, saved the day for me.
    – Samwise
    Dec 1, 2023 at 17:43
0

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
4
  • 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, 2018 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. May 22, 2018 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, 2018 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. May 23, 2018 at 9:05
0

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.

1
  • 1
    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. Dec 20, 2018 at 2:28
0

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

0

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 Sub
0

I am assuming that your data resides on the same Workbook.

Here are the steps:

  1. Give a name to the data range using Ribbon->Formulas->Name Manager
  2. 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.
0

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

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
  • Not sure this adds anything that isn't covered by the existing top-voted answer that says the same thing in more detail
    – Alex M
    May 8, 2020 at 20:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .