Solved

Remove links from Excel Sheet

Posted on 2014-12-15
14
151 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:SimonAdept
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
 
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 31

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 31

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 31

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 31

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 31

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

706 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now