Excel 2016 Phantom External Links

[gavs73]

Hi,

Does anyone please have a fool proof method for finding External Links in Excel 2016 Workbooks ?. I've tried searching for them in cells, I've checked conditional formatting, names ranges etc, and yet one still persists.

Thanks in advance for your help.

Thanks
Gav

 

[Beyond_avarice]

You should also check for Pivot table links and data validation links.

Run this to see if you have hidden Names

Sub bulkHiddenNameRemove()


Dim nm As Name


i = 0


Sheets.Add(After:=Sheets(Sheets.Count)).Name = "TXT"    'Comment out when ready to delete
'
For Each nm In ActiveWorkbook.Names
    Debug.Print nm.Name         '   Name has to preclude the sheet name?
'
   If Worksheets("TXT").Range("B1").Offset(i, 0).Value = "x" Then
            nm.Delete


    End If


   
   '' should probably redo this to include the Worksheet name. As the TXT only reveals Named Ranges and not the deleted worksheet
  


   Worksheets("TXT").Range("A1").Offset(i, 0).Value = nm.Name   
'
 i = i + 1




    
  Next nm


End Sub

 

[grd]

I too am having trouble removing a link from a Workbook using Excel 2016.

I've tried the normal Break Link and I've deleted all names, including hidden names but still I have a link which I cannot find.

The link sort of points to a live path except that it is on the wrong drive so I recreated the file with the "correct" path and I can open the source but cannot change the source from within Data > Edit Links. Either way, I don't want the link in any case.

I've tried copying all tabs to create another workbook without success.

I've tried running File > Info > Check for Issues > Inspect Document and I've cleared everything except for links which cannot be removed automatically.

I've tried running macros to break the link and also the other macros from here https://www.mrexcel.com/forum/excel...ll-external-links.html?highlight=hidden+links

One small success was the Hidden Name Remove which identified "_xlfn.IFERROR" in a created worksheet called TXT. Unfortunately, It does not identify where it is although I have now located the sheet by deleting each worksheet in turn until the link disappeared and I still cannot locate it although that sheet is used as a lookup table from other worksheets.

I'm coming to the conclusion that there is no solution but thought I would ask the question before finally giving up.

So, please, if anyone has any suggestions, i would be very grateful.

Thank you

 

[Beyond_avarice]

Be careful about what hidden names you select for removal. Some names may be required for addins to connect to servers(eg Oracle JE templates).

Names like:

_xlfn.COUNTIFS
_xlfn.IFERROR
_xlfn.SUMIFS

These names are created by MS as a means of compatibility between Excel 2007+(.xlsx) and earlier versions(.xls). Just leave them alone.

The link you provided address scripts I wrote to fix some problems with broken links. Many hidden names are linked to XLL and DLL files.

Until I get around to rewriting the "bulkHiddenNameRemove" script. Simply run the script. Put an x in column "B" of each respective hidden name you wish to have deleted(refer to caution above). Comment out the:
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "TXT"
Worksheets("TXT").Range("A1").Offset(i, 0).Value = nm.Name

Rerun the script. If you run into an item that cannot be deleted; manually step past that name and continue with the rest.

Since the posting of the link you provided, I have updated the "condFormLinkBreak"

Sub condFormLinkBreak()


Dim cRule As String, cAp2 As String      'ConditionalFormat Rule and Applies to address
Dim aWS As Worksheet




Application.ScreenUpdating = False




Set aWS = ActiveSheet


Sheets.Add(After:=Sheets(Sheets.Count)).Name = "TXT"


aWS.Activate


Z = 1


For i = ActiveSheet.Cells().FormatConditions.Count To 1 Step -1
    On Error Resume Next
     cRule = Cells().FormatConditions().Item(i).Formula1
     cAp2 = Cells().FormatConditions().Item(i).AppliesTo.Address
               
   


    Sheets("TXT").Range("A" & Z).Value = cAp2
    Sheets("TXT").Range("B" & Z).Value = """" & cRule & """"
    
    Z = Z + 1
    
    On Error Resume Next
    
            If InStr(1, cRule, "[") > 0 Then
                  aWS.Cells().FormatConditions().Item(i).Delete        'delete anything with "["
            End If
    
Next
   
Application.ScreenUpdating = True


End Sub

Let me know if you are still having issues.