Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Remove links from Excel Sheet

Posted on 2014-12-15
14
Medium Priority
?
176 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 33

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 33

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 33

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 33

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 33

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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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 describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
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.

715 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