Changing and Deleting "Links" in an Excel Spreadsheet

Greetings EE Pros,

I have a  WB that I have built on top of other WBs I have done.  I do not have a need for any "Link Updates" to occur when I import a WS.  How do I disable WS/WB Link Updates in my primary WB?  I am running Excel 2010.

Much thanks,

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
[ ]

If the worksheet being "exported" (saved within an external workbook) refers to a cell, a range of cells, a worksheet, or a named range within the original workbook, then there will be a link back to the original workbook.

In order to resolve this, the "exported" worksheet will need to replace any in-cell formulae with the explicit values (i.e. the result of any formulae calculations) during the "export" process, by selecting the entire contents of the worksheet (or just the cells that contain formulae), copying to the clipboard, then pasting "as values" back over the same cells.
Bright01Author Commented:

Understand....and still troubleshooting this Import/Export WS.  I have removed all of the formulas that are associated with the WS and it Exports fine.  When it imports, it is looking to Update Links.  When I choose "Update" it takes me into an Edit mode on the 4 links..... none of which are still valid links (links to very old WBs).  What I'm trying to do is to get rid of the links in the main WB before exporting.

Make sense?

[ fanpages ]IT Services ConsultantCommented:
Just to clarify if you did understand what I meant...

The links that are trying to be qualified when the worksheet is exported, then imported, exist within the worksheet being exported; not other worksheets linking to the worksheet being exported.

Check the contents of the worksheet being exported for any formulae that refers to worksheets that are retained within the original workbook (i.e. not exported).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exploring SQL Server 2016: Fundamentals

Learn the fundamentals of Microsoft SQL Server, a relational database management system that stores and retrieves data when requested by other software applications.

Bright01Author Commented:
Is there a way to find the links, within the WS without having to go cell to cell?  Could old Range Names be part of the links it's trying to re-establish?  I've removed all of the formulas.

Rob HensonFinance AnalystCommented:
I recall an Add In for earlier versions called DELLINKS.XLA

If you do a search for that it might help.

Rob H
Bright01Author Commented:
Rob and Fanpages,

Thanks!!!  I've downloaded the add in and will attempt now to check it out.

Appreciate the direction.

[ fanpages ]IT Services ConsultantCommented:
You're welcome.

Good luck.
Bright01Author Commented:
Fanpages,  can you take a look at my open question that we have been working with Ryan on?  I'm still having trouble with the import.

1.) It continues to drop the ActiveX control

and more disturbing,

2.) When I import it, it changes the Sheet number so other Macros are thrown off.  I need the macro to simply replace the existing sheet with the one saved.


[ fanpages ]IT Services ConsultantCommented:
Ryan has already replied, but I have just responded to your point 2):

[ ]
[ fanpages ]IT Services ConsultantCommented:
Hi again,

Further to Rob H's response above, saurabh726 has just posted an alternate suggestion (within another thread) to locate "links" in workbooks:

[ ]
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.