Solved

Remove links from Excel Sheet

Posted on 2014-12-15
14
158 Views
Last Modified: 2014-12-15
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
0
Comment
Question by:AMixMaster
  • 5
  • 4
  • 3
  • +2
14 Comments
 
LVL 12

Expert Comment

by:FarWest
ID: 40500676
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
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40500712
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
 
LVL 18

Expert Comment

by:Simon
ID: 40500781
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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40500802
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40500817
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
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40500835
Thanks, Rob! That's a pretty handy trick!
0
 

Author Comment

by:AMixMaster
ID: 40500862
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40500869
As Katie suggested for the linked part of the cell or for the whole cell?
0
 
LVL 7

Expert Comment

by:Katie Pierce
ID: 40500879
Oh, in that case, yes, Simon's comment is correct.
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40500880
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
 
LVL 32

Accepted Solution

by:
Rob Henson earned 500 total points
ID: 40500907
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
 

Author Closing Comment

by:AMixMaster
ID: 40501043
a few dim staments and it worked, Thanks!
0
 

Author Comment

by:AMixMaster
ID: 40501045
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40501120
My original probably had the Dim statements earlier in the script and I only copied the relevant bit, apologies.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question