Remove links from Excel Sheet

Is it possible to remove the links without removing local sheet formulas?
Some formulas refer to several different remote links in the same formula.
IE: if a formula requires the values from the linked Master Data we copy&paste values in the cell if not leave the formula as is
AMixMasterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
Found it from a previous file that I was working on:

Set wbk = Workbooks.Open(Filename:=CurrFile _
            , UpdateLinks:=3)
with wbk
   arrLinks = .LinkSources(xlExcelLinks)
   If Not IsEmpty(arrLinks) Then
      For i = LBound(arrLinks) To UBound(arrLinks)
         .BreakLink arrLinks(i), xlLinkTypeExcelLinks
      Next i
   End If
end With

Open in new window

This was written for Excel 2003 so might need a tweak, try it and see if it works.

Thanks
Rob H
0
 
FarWestCommented:
using find and break links ... check this link
http://www.exceltrick.com/how_to/how-to-find-external-links-or-references-in-excel/

also you can use vba to automate search and replace
0
 
Katie PierceCommented:
I just want to follow this question to see what other solutions might be suggested.  This would be immensely helpful if it can be done.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
SimonCommented:
Data/Edit links/Break Link - leave the current result of the calculation in place. This was option 2 in the link that fryezz posted.
0
 
Katie PierceCommented:
I think the author is looking to keep the structure of the formula, but replace link values within the formula with those from the external source, e.g.:

If the formula was =C35*SUM('C:\Users\jdoe\Documents\Budgets\[Master_Budget.xlsx]Data'!BF44:BF48)

You'd want breaking the links to leave =C35*462.23

462.23 being my arbitrary result of SUM('C:\Users\jdoe\Documents\Budgets\[Master_Budget.xlsx]Data'!BF44:BF48)


Is this summary correct, AMixMaster?
0
 
Rob HensonFinance AnalystCommented:
Using Katie's example:

=C35*SUM('C:\Users\jdoe\Documents\Budgets\[Master_Budget.xlsx]Data'!BF44:BF48)

If you highlight the Bold Italic section within the cell and press F9 it will convert it to the number; pressing enter then leaves the number rather than going back to formula.

Not sure how you are doing what Katie is suggesting using copy & paste. Highlight within the cell and copy will copy the formula. Copy whole cell & paste values will convert all to value, not just the linked part.

BTW - How is this different to your other question?

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28581094.html

Thanks
Rob H
0
 
Katie PierceCommented:
Thanks, Rob! That's a pretty handy trick!
0
 
AMixMasterAuthor Commented:
We are looking for a snipet that will remove the formulas if they contain external links and replace with the value.
If the formula does not contain external link leave it as is.
0
 
Rob HensonFinance AnalystCommented:
As Katie suggested for the linked part of the cell or for the whole cell?
0
 
Katie PierceCommented:
Oh, in that case, yes, Simon's comment is correct.
0
 
Rob HensonFinance AnalystCommented:
The following will break a specific link:

ActiveWorkbook.BreakLink Name:="Book1", Type:=xlExcelLinks

I believe you can create an array for the Name part of the statement but it can't be omitted.

Thanks
Rob H
0
 
AMixMasterAuthor Commented:
a few dim staments and it worked, Thanks!
0
 
AMixMasterAuthor Commented:
here are my small chages

Set wkbk1 = Workbooks.Open(Filename:=defaultFN, UpdateLinks:=3)
With wkbk1

    Dim arrLinks As Variant
    Dim i As Variant
   arrLinks = .LinkSources(xlExcelLinks)
   If Not IsEmpty(arrLinks) Then
      For i = LBound(arrLinks) To UBound(arrLinks)
         .BreakLink arrLinks(i), xlLinkTypeExcelLinks
      Next i
   End If
End With

Open in new window

0
 
Rob HensonFinance AnalystCommented:
My original probably had the Dim statements earlier in the script and I only copied the relevant bit, apologies.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.