Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing links (2000)

    I have one spreadsheet linked to another. When I choose "Edit LInks" my choices are to change the source file or open the source file. How can I remove the link?

    Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Removing links (2000)

    In addition to Hans' suggestion, you can change the source to the current file, which will also remove the links by pointing the relevent references to the cells in the current file.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing links (2000)

    Since I intend to use a macro for a template, I will not know where the source file is located.

    Is there no other way to remove a link other than to "change" the link to the current file?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Removing links (2000)

    The sourcefile is given by:
    <pre>activeworkbook.fullname</pre>


    What exactly is your goal for this macro?

    There may be other ways to do what you want/need.

    You can also change each linked cell to a value (as Hans suggested) or edit the linked cell to get rid of the reference (which is what changing the source does to all of them). There can be more than just cell links, links in names for instance can sometimes also "get you".

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing links (2000)

    Several people are using a template that creates links with another file. I want a macro that will remove all links from the file created by a template.The source file's location could be anywhere. Hence, I cannot use the "Change Links" opton nor can I use the add-on referred to in the other reponses because I would have to install this on everyone's computer. Ideally, there is a macro that will allow me to just delete the active links on an opened file.

    Thanks for your help.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing links (2000)

    If a cell contains a link to another workbook, the formula will contain brackets [ and ] around the workbook name. You can use this in a simple macro:

    Sub RemoveLinks()
    Dim oSheet As Worksheet
    Dim oCell As Range

    For Each oSheet In ActiveWorkbook.Worksheets
    For Each oCell In oSheet.UsedRange
    If oCell.HasFormula Then
    If InStr(oCell.Formula, "[") > 0 Then
    oCell = oCell
    End If
    End If
    Next oCell
    Next oSheet

    Set oCell = Nothing
    Set oSheet = Nothing
    End Sub

    This macro only breaks links in cell formulas, not in (for example) charts or text boxes.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Removing links (2000)

    Your mind is starting to go! You have forgot again about:
    <pre>SpecialCells(xlCellTypeFormulas)</pre>


    This code should be much faster

    <pre>Sub RemoveLinks()
    Dim oSheet As Worksheet
    Dim oCell As Range

    For Each oSheet In ActiveWorkbook.Worksheets
    For Each oCell In oSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
    If InStr(oCell.Formula, "[") > 0 Then _
    oCell = oCell
    Next oCell
    Next oSheet

    Set oCell = Nothing
    Set oSheet = Nothing
    End Sub</pre>


    It will eliminate the need to test for "hasformula" and also should minimize the number of cells that must be checked (the real time savings)

    Steve

  8. #8
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing links (2000)

    The sheets are linked not by cell reference but as a result of an "Edit, Move or Copy Sheet" command. The macro runs but does not remove the link.

    Any other suggestions?

    Thanks much.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing links (2000)

    Oops! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing links (2000)

    I don't understand that, but I've just learned that my mind is starting to go... <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23>

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Removing links (2000)

    The links are probably in the names.

    The easiest thing to do is just delete the names with links:

    Sub DeleteLinkedNames()
    Dim nm As Name
    For Each nm In ActiveWorkbook.Names
    If InStr(nm.RefersTo, "[") <> 0 Then _
    nm.Delete
    Next
    End Sub

    Steve

  12. #12
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing links (2000)

    I tried it. The macro ran but no dice on removing the linked spreadsheet.

  13. #13
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing links (2000)

    Macro ran but failed to remove the links.

  14. #14
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Atlanta, GA
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing links (2000)

    I tried to copy and paste special values. I'm not sure what you mean. Should I copy the spreadsheet somewhere else or just copy all the suspected cells and use the paste special command. I tried the latter and it did not work.

    Thanks.

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing links (2000)

    What I meant was copy, then paste special in place, without changing the selection. But since the macro didn't work, this will not work either. It seems you have no formulas with external links.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •