Remove Link problem in EXCEL 1

[OOP]

Hi,

How do i remove the link in the dialog box under EDIT-->LINK

Cause my file is asking me to update the cells or not upon opening the file. Thanks in advance for your help^^

 

[KenWright]

Do you know where the links are? If so then just break them manually.

If not, then do you have the Break links option, in which case you can just hit that, though I don't like doing thatw ithout knowing what link i am breaking.

Personally I would use Bil Manville's free Findlink addin, available from

http://www.oaltd.co.uk/MVP/Default.htm

This will find your linbks no matter where they are hiding, and give you various options as to what you want to do, eg either just find them, or find then break etc

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[OOP]

Please allow me to elaborate more on the problem,

I know what is the file that the Excel wants, lets assume its linking up hello.xls in my C drive.
Everytime i opened the file, it prompts me a dialog to ask me if i want to link to hello.xls

My question is, how can i remove this dialog (i don't want to link up to the file, i want to delete the link to "hello.xls" in my file..)
I have tried looking for the string "hello.xls" in the cells inside my file but can't find any Can i delete this link inside the Excel program itself ?

Thanks for your links, Ken. I'm not sure if the link you provided will solve my problem but i'll give it a try if there's no other alternatives..

 

[xlbo]

If you have XP or higher, try the following

1: Start up "Find" by pressing Ctrl+F
2: Set the options to search in "WorkBOOK", look in "FORMULAS" and look for a "PARTIAL" match


If it finds nothing then the links are in "NAMES" rather than formulae. To find these, follow menu path Insert>Name>Define and scroll down the list of names until you see an external reference in the "refers to" box - that will be what is causing this

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

The link I provided will do nothing else but sort your problem. It trawls through your workbook looking for external links to other files (ie your hello.xls). It then flags these up to you and asks you whether you want to keep the link or break it. It will find it whether it is a formula in a cell, buried in a chart range or in a name somewhere.

Can be a really useful addin to have tucked away for this very reason. If I had your workbook here I would simply run this and tell you in literally a few seconds exactly where your link(s) is(are).

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[01Patrik]

If you still feel that you don't want the add-in (by some reason - really nice tool actually), then you can use the following code instead.

sub Remove_All_Links()

Dim Links As Variant

Links = ActiveWorkbook.LinkSources _
      (Type:=xlLinkTypeExcelLinks)

If IsEmpty(Links) Then Exit Sub

For linkcount = 1 To UBound(Links)

ActiveWorkbook.BreakLink _
      Name:=Links(linkcount), _
      Type:=xlLinkTypeExcelLinks
      
Next

End Sub

The code finds, identifies and removes all excel links in the active workbook.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[01Patrik]

...if you take it one step further, you can actually choose wether to remove a link or not:

Sub aa()
Dim Links As Variant

Links = ActiveWorkbook.LinkSources _
      (Type:=xlLinkTypeExcelLinks)

If IsEmpty(Links) Then Exit Sub

For linkcount = 1 To UBound(Links)

If MsgBox("Do you want to remove the link to " & Links(linkcount) & "?", vbYesNo) = vbYes Then

ActiveWorkbook.BreakLink _
      Name:=Links(linkcount), _
      Type:=xlLinkTypeExcelLinks

End If

Next

MsgBox "All Excel links listed."

End Sub

By the way, did you even try the "remove links" button under Edit | Edit links (requires XL2002 or later) ?



// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[KenWright]

Personally I wouldn't delete a darn thing until I knew EXACTLY what it was and why it was there in the first place.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[01Patrik]

...but that wasn't really the question now, was it? But in all fairness, I agree, and the addin is great. And, even better, mr OOP has a lot of choices, all equally solving the issue described...

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[OOP]

Thanks for the responses so far, guys^^

I have tried the Add-in and found that link, it is defined as a variable name (Bye = "C:\hello.xls"), no wonder i couldn't find hello.xls in the first place. Have deleted all the Bye(s) in the cells.

I have another question thou, how can i define a link as a name? And use the name in any cell to refer to the file (like the case i mentioned above)?

Would like to thank Ken once again for the Add-in^^

 

[01Patrik]

You can't define it as a name - you can create a static variable in VBA, but not reference a link as a name.

However, you can achieve the effect you're after by cathing the reference in a UDF (user defined function).

Function Link(Count As Integer)

Dim Links As Variant, LinkCount As Integer

Links = ActiveWorkbook.LinkSources _
      (Type:=xlLinkTypeExcelLinks)

If IsEmpty(Links) Then Exit Function

Blad = Links(Count)


End Function

Your syntax in a cell would be:

=link(1)

to get the first link as a textstring. (Returns 0 if there's no excel link in the workbook).

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[01Patrik]

Sorry - it should be

Link = links(count)


// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[KenWright]

You can define it as a name:-

Insert / Name / Define

Put in a name, eg MyLink, and then in the refere sto box at the bottom, just paste in the fuill link, eg

='F:\PROJECT\ABC\PROJECT RePrice\[Percentage Chart.xls]Modelling Percentages'!$D$17

You can then just use =MyLink to refer to that cell in that file. It's a good way of keeping down formula sizes when referring out to external files, as depending on the level of nesting within your folders, you can quite soon hit the maximum number of characters allowed within a formula.

Regards
Ken...............

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[OOP]

Thanks, Ken. Its really what i want .
One for star for you.

 

[01Patrik]

Ken: That names a link to a particular cell, it doesn't name the link (to the external source) itself.

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.

 

[KenWright]

True, but it can if you are happy to use it in conjunction with INDIRECT, BUT, the target workbooks have to be open, eg:-

With the name of my file enclosed in the required brackets, defined as 'MyLink', eg

="'[Percentage Chart.xls]"

and the rest of the filepath and reference etc in say cell A1 eg,

Modelling Percentages'!$A$2

then I can use

=INDIRECT(MyFile&A1)

to return whatever is in Cell A1 in the file

Dies if you close the workbook though because of the limitation with INDIRECT and closed workbooks.

But as you have rightly said, that doesn't give you a named link to just a file using the full filepath, that can be used in any formula.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[01Patrik]

Correct you are - that way it can be used. But you'll also have to define the name manually - the udf I provided automatically gives you the link, and allows you to use it within any cell just like a defined name would.

And one could, if one really wants to, combine our suggestions and define a name with the udf inside:

insert | name | define

MyLink
=link(1)

Then you'll get exactly what OOP wanted...

// Patrik
______________________________

To the optimist, the glass is half full. To the pessimist, the glass is half empty. To the IT Professional, the glass is twice as big as it needs to be.