Share via

Mac Excel - Break a link to external file (doesn't work)

Anonymous
2019-08-30T19:05:42+00:00

Hi,

I'm trying to break link in my excel file to an external link which doesn't exist anymore in Mac, and followed instructions but it doesn't work. Everytime when open/edit the file it just spin forever.... 

Please help!!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2019-09-10T09:09:15+00:00

    Hi OssieMac,

    Sorry for my late response.

    I still cannot get confirmation from the customer, but I've tested the Excel worksheet without these links on a Mac and found it can work normally.

    I would like to share the steps I've tried to break the link in the specific Excel worksheet of the customer, since it may be helpful for other community members.

    1.  Check whether the Data Validation is cleared.

    Step1: Click Edit  -> Find -> Go To -> Special, select Data Validation, select All to find all cells with data validation and click OK.

    Step2: An Alert pop box with message "No cells were found", verified that no data validation are set.

    1. Try to remove the broken link.

    Step1: Click Data -> Edit Links, selcet the external link(found this link refer to a file in sharpoint) and click Break Link.

    Step2: Click Break Links in the Alert Panel, but the Alert panel disappered with neither an error message nor the external link being breaked.

    1. Try to delete the externalLinks folder in the xl folder of the changed .zip file.

    (*Backup this Excel file to avoid the possible data loss)

    Step1: Rename the copied Excel file, change file name extension from .xlsx to .zip.

    *You may need to show extentions in Windows 10 via open File Explorer -> View -> select the File name extensions option.

    Step2: Open the .zip file and delete the externalLinks folder in the xl folder.

    Step3: Change file name extension from .zip to .xlsx.

    Step4: Open the Excel worksheet but find the link still exists.

    1. Try to delete the <extLst> </extLst> code segment in the sheet.xml file in worksheets folder in the xl folder of the changed .zip file.

    Step1: Rename the copied Excel file, change file name extension from .xlsx to .zip.

    Step2: Open the .zip file and open the sheet.xml file one by one in the worksheets folder in the xl folder.

    *I choose open these sheet.xml files via Notepad.

    Step3: Press Ctrl + F and input sharepoint in the Find box then click Find Next. Eventually I found there are many sharepoint addresses in sheet2.xml file, there sharepoint addresses are wrapped by a  <extLst> </extLst> code segment.

    Step4: Drag the sheet2.xml to desktop and open it via Notepad. Delete the <extLst> </extLst> code segment and the contents wrapped in it and save.

    Step5: Drag the sheet2.xml from desktop to replace the sheet2.xml in worksheets folder in the xl folder of the .zip file.

    Step6: Change file name extension from .zip to .xlsx and open the Excel worksheet. Found the link disappeared.

    I had completed the first two integral steps on Mac and managed to complete the last two integral steps on Windows.

    Per my test, the external link won't appeared in the sheet.xml normally and we can break a link at most via delete the externalLinks folder in the xl folder of the changed .zip file.

    I'm not sure what specific settings the customer did in this Excel file, which caused the <extLst> </extLst> code segment appeared in the sheet.xml.

    It would be great if someone can reproduce this issue and he can try to delete the <extLst> </extLst> code segment to see if he can delete the external link successfully.

    Please kindly point out if what I said included some incorrect information.

    Appreciating for your efforts and understanding.

    Best Regards,

    Hugo

    0 comments

13 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jim G 134K Reputation points MVP Volunteer Moderator
    2019-09-01T14:18:51+00:00

    In Mac Excel use the Edit menu and choose Find > Go To to display the Go To dialog.

    1 person found this answer helpful.
    0 comments
  2. OssieMac 48,001 Reputation points Volunteer Moderator
    2019-09-01T11:39:10+00:00

    Not sure if the following method applies to Mac but with PC if any data validation has ben set up using link data then the link cannot be removed until after the Data Validation is cleared.

    Guidelines in PC to locate data validation cells and remove the links. I assume it is similar for a Mac.

    • On Home ribbon select "Find & Select"
    • Select Goto Special -> Validation and OK.
    • All Data validation cells should be selected.
    • Check each validation and if using the link data then use Clear all to delete the validation.
    • Break the links
    • Need to Save the workbook and close and re-open before the links disappear.

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2019-09-04T01:24:24+00:00

    Hi All,

    Thank you for your responses/suggestions. I followed the suggestions (& even remove all the validation / reset to Any value)

    1st Pass

    1.1 Select the whole sheet

    1.2 Edit menu and choose Find > Go To to display the Go To dialog > click on Special > select Data Validation

    1.3 it get to cells with data validation setup, and changed to Any Value

    1.4 close the file

    1.5 exit Excel

    Follow the same process and after 1.1 it shows no cells were found. ==> Verified that no data validation were set

    However when I reopen the file I'm still seeing the below Security Warning

    And still seeing the reference to external files exists

    Under Data > Edit Links

    Still cannot break this links to external files. 

    The big problem is anytime I edit anything, excel keep spinning and took a long time to save or do any action....

    Please let me know what else I should try.

    Thanks!

    0 comments
  4. Anonymous
    2019-09-03T11:01:03+00:00

    Hi Customer,

    Have you tried the suggestions provided above?

    Did you follow this instruction: Fix broken links to data to remove a broken link?

    Please tell us if you need more help.

    Best Regards,

    Hugo

    0 comments