Solved: remove link to another spreadsheet

[gibbo1715]

I have a workbook where I have imported a spreadsheet from another workbook.
The imported worksheet contains automatic links to another workbook, I need to remove that link and try as I might I cant find where to remove it

Can anyone tell me how to do so please

[Jacob Hilderbrand]

Edit

Links...

Break Link

[gibbo1715]

i dont have the option to break link only update open or change?

[gibbo1715]

I would quite like a macro that leaves the attachment but doesnt prompt or create the link on open, is this possible Jake?

[johnske]

Hi gibbo,

Assuming the links are on the sheet and you want to leave the 'value' and remove the link; we can paste the value in an unused cell such as IV1, clear the contents and then replace the value like so:Sub DeleteLinks()
Dim Cell As Range, FirstAddress As String
With Range("A1:E500")
Set Cell = .Find("=*!", LookIn:=xlFormulas, searchorder:=xlByRows, _
LookAt:=xlPart, MatchCase:=True)
On Error GoTo Finish
FirstAddress = Cell.Address
Do
Range("IV1") = Cell.Text
Cell.ClearContents
Cell.Value = Range("IV1")
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End With
Finish:
End Sub

[gibbo1715]

Thanks but wont work for me as the sheet is password protected and I dont know the password, very useful piece of code though.

[gibbo1715]

I really need to know just how to turn off the message do you want to update links and auto answer no if thats possible?

[Jacob Hilderbrand]

Like this.

[gibbo1715]

Dont have that option available to me jake, maybe because the sheet is password protected ( I dont know the password and dont want to redo the whole sheet if I can help it, i also dont want to buy some password recovery software just for this small task)

Thats why im after a VBA solution

[Wizard]

Gibbo -

Excel sheet protection passwords are ridiculously easy to sidestep:

Make sure you have a blank sheet available (same workbook if it refers to other sheets in the workbook.... just makes it easier).

Select the entire protected worksheet, copy, switch to the blank sheet & paste at cell A1. You now have an unprotected duplicate of the protected original.

[gibbo1715]

Ok that is ridiculously easy and i now have a sheet with no password, still have the link though and still cant remove it?

[Wizard]

Assuming you've run the code above and/or tried a simple Copy->Paste Values, can you post a copy of the spreadsheet here?

[gibbo1715]

i ve done both, i cant give you the worksheet to look at as I cant zip it or email it from my work, I ll have to try and find another way i guess, would be interested if it is possible with vba to turn of the link request message box at startup set to no anyway?

[Wizard]

You could try Tools menu->Options->Calculation tab, uncheck "Update Remote References" or Tools menu->Options->Edit tab, uncheck "Ask to update automatic links".

Other than that, I really think I'd need to see the spreadsheet.

[Zack Barresse]

Hello,

I still hold firm that MS MVP Bill Manville's FindLink add-in is most superior to any I've found yet.

Found here: http://www.bmsltd.ie/MVP/Default.htm

HTH

[dcardno]

If you have used the VBA routine Jake provided, then there are no more "formula links" in the spreadsheet you are dealing with. You can also get the update prompt if you have formats that reference another worksheet - typically if a cell or range with a named format has been cut and pasted onto the sheet abnd the style name conflicts with one already defined for that sheet. Check the formatting styles (Format|Styles, and click on the dropdown box to choose styles) and see if there are any with names that include a sheet or workbook reference, like "Normal_Distribution" or "Comma_Sheet1" and delete those.

Cheers,

Dean

[gibbo1715]

Thankyou all. I ll give it a try tomorrow

[johnske]

Hi gibbo,

I Know nothing about the add-in Zack suggested and whether it'd handle the following; but it's also possible the link(s) aren't in any cells on the s/s - are there any pictures charts or such-like on the sheet? If there are, hover the mouse over them to see if they have links.

Regards,
John

[Zack Barresse]

The add-in will handle chart links as well. I highly suggest it. I've had tremendous luck with it.

[gibbo1715]

eventually managed to use the link remover program, worked like a charm, thankyou all for your assistance

[geekgirlau]

Great recommendation Zack! I'm definitely adding this one to my toolbox. It managed to detect and delete a hidden link to an invisible print area range, so I'm sold :thumb