You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
Somehow a link to a non-existent file has been added to my workbook. Lots of Googling later and I now know the name of the linked file, but cannot find the cell(s) which contains the link
I have tried searching for parts of the filename and the [ , but neither gives any results
I found a link to a wizard on an MS Support page that offers to get rid of these type of links, but it is obviously for an older version of excel and does not work
I could just 'break the link' and pray it does not impact anything (scary)
Can anyone give me any pointers
Pretty please 🙁
Is the link on a hidden sheet?
Any protection on the workbook?
what about the linked file - does the context of the data there give any clues?
If I remember rightly, breaking the link will leave you with the last values that were linked (so changes to the formerly linked file no longer have any effect). If it's your mileage claim this is obviously less of a problem than if the data includes the launch codes...
What version of excel are you running?
Does this help? http://office.microsoft.com/en-gb/excel-help/find-links-in-a-workbook-HP003056096.aspx
I should not have any hidden sheets (will check though) and I have not yet added any protection to the workbook (maybe I should have done before I asked someone else to do some updates.....)
In terms of importance, it's somewhere between a mileage for and the launch codes :wink:, but I could be up for a lot of hassle at work if I get it wrong 🙁
Thanks Benp1, but I have already tried that. I am running Excel 2010, but I dont have a specific version number. Amazingly I have just discovered I dont even know how to find the version number in this edition 😳
it may gave picked up a VBA module with an external reference while on someone elses computer.
Press Alt+F11 and have a look through any VBA scripts in the the open file and delete them.
I have tried searching for parts of the filename and the [ , but neither gives any results
How was it displayed when you did this? Values or formulae.
Other places links can be hiding is in range names, macros etc these won't show on a search.
There is a macro you can run to find all links, it's on this page:
http://www.extendoffice.com/documents/excel/953-excel-list-all-links.html
NB If you're really stuck you can email me the workbook and I'll find them for you....
Link hidden in cell formatting can also be a horror to find
There is a link object:
Dim aLinks As Variant
aLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
So they are quite easy to find via the OO model.....
NB Excel files can get corrupted eg I've seen styles defined which I can't delete as they're not properly formed. You have to export all the contents out and back into a fresh workbook to remove them (well leave them behind).
Thanks Chaps. A combination of comments from above meant I found that the problem is in the 'data validation' rather than in the cell itself (hence the search did not work).
I have just deleted all data validation which removes the error/links. Now I just need to recreate the data validation again which is a lot better than what I was hoping