Nov 6 2024, 12:55 PM, updated 2y ago
Hi guys,
Recently I have encountered this error when trying to break the links to the server by going to Data-Workbook Links. The name manager is blank, though.
If you search all over the internet, it will end up wasting your time for no solution, but nonsense everywhere. Even Microsoft has no idea on it!
Then I found the solution. It sounds like not practical but at least it can be solved in less than 5 minutes. No need to start the work from the scratch!
Solution No.1:
1. Close the problematic excel file. Let say it is filename.xls
2. Rename it to filename1.xls
3. Open the filename1.xls
4. Create New Blank Workbook(new excel file)
5. Save the new workbook to the same file format, and name it to filename.xls.
6. From the filename1.xls, Select All Sheets, then copy it to filename.xls. Click YES for all the error prompts.
7. Close the filename1.xls and delete it.
8. On filename.xls, go to Data-Workbook Links, you are able to break the links now. Remember to click refresh all to ensure it works.
Solution No.2:
1. Open the problematic excel file
2. Right click the worksheet and select move or copy..., a box will appear.
3. On to book, select (newbook). Click create a copy.
4. Click yes to all errors that pop out. Click yes to all if name errors pop out.
5. Go to Data-Workbook Links, you are able to break the links now. Remember to click refresh all to ensure it works.
6. Now replace it to the problematic excel worksheet file. Remember to click refresh all in workbook links to ensure it works.
Note: No need to save the newbook. Just close it without saving will do. You just save the problematic file will do. All should be good now.
Solution No.3:
This need some basic codding knowledge but don't worry, I will show you step by step so that you know how to do it.
Before deleting the external links, you may want to see what it is.
1. Open the problematic excel file
2. Press the ALT-F11 on the keyboard. The VBA will pop out.
3. Click Insert
4. Select Module. A white box will pop out on the right pane
5. Copy and paste this code in the white box: -
CODE
Sub GetLinks()
Dim links As Variant
links = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(links) Then
Sheets.Add
For i = 1 To UBound(links)
Cells(i, 1).Value = links(i)
Next i
Else
MsgBox "No external links are found.", vbInformation, "Find Links"
End If
End Sub
Dim links As Variant
links = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(links) Then
Sheets.Add
For i = 1 To UBound(links)
Cells(i, 1).Value = links(i)
Next i
Else
MsgBox "No external links are found.", vbInformation, "Find Links"
End If
End Sub
6. Press F5 on the keyboard. You will be able to see the external links in the new worksheet(sheet 1), then delete it.
7. Now you can delete the external links by run this code: -
CODE
Sub DeleteExternalLinks()
Dim objDefinedName As Object
Dim iCount As Long
For Each objDefinedName In ActiveWorkbook.Names
If InStr(objDefinedName.RefersTo, "[") > 0 Then
objDefinedName.Delete
iCount = iCount + 1
End If
Next objDefinedName
End Sub
Dim objDefinedName As Object
Dim iCount As Long
For Each objDefinedName In ActiveWorkbook.Names
If InStr(objDefinedName.RefersTo, "[") > 0 Then
objDefinedName.Delete
iCount = iCount + 1
End If
Next objDefinedName
End Sub
8. Click End if '1004' error pop out
9. Refresh all in workbook links to ensure it works. If it is still appear, you should be able to break the links easily.
I hope those ppl facing the problem can reach here for the solution.
This post has been edited by NSR: Nov 8 2024, 05:45 AM
Attached thumbnail(s)
Attached image(s)
Quote
0.0138sec
0.54
6 queries
GZIP Disabled