Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Remove links from Excel Sheet

Posted on 2014-12-15
14
Medium Priority
?
179 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 34

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 34

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 34

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 34

Accepted Solution

by:
Rob Henson earned 2000 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 34

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

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

916 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