Lỗi excel 2023 we couldnt open that file rename

You might have more enteries, select the one where status us unknown and Break Link button. You can also press Check Status and it will give you the same message box.

But where is the cell where I have this reference? I don't think I have a reference, it seems to be a bug perhaps.

You can search for [*] or [] because all references in Excel are in square brackets. This may return a large number so you can narrow down search like []test where test is the file name.

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.

fixer1234

27.2k61 gold badges76 silver badges119 bronze badges

asked May 21, 2018 at 1:44

2

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.

answered Jul 12, 2019 at 20:16

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.

answered Jun 8, 2022 at 5:53

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.

answered Apr 28 at 1:46

1

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, 2018 at 7:03

Rajesh SinhaRajesh Sinha

9,0876 gold badges15 silver badges36 bronze badges

4

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, 2018 at 0:44

1

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.

music2myear

41k46 gold badges86 silver badges127 bronze badges

answered Feb 15, 2021 at 18:17

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

answered Mar 10, 2021 at 9:11

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.

answered Apr 22, 2021 at 10:21

Joshua KanJoshua Kan

3073 silver badges6 bronze badges

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.

answered Mar 16, 2022 at 17:38

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