Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Break Link thru' VBA (VBA/MS Excel/97)

    Does anyone have a way to programmatically 'break' a link? The need arises when an Excel-based report is created that has multiple links to other workbooks containing current standards. The document is 'signed off' and the information that it contains in the form of links needs to be broken to represent a true history of the standard at the time the document was created and it does not need to have an auditing path shown.

    I have explored 'ActiveWorkbook.LinkFormat' but this only referred to shape objects.

    Any other suggestions? TIA, Leigh

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Break Link thru' VBA (VBA/MS Excel/97)

    (Disclaimer: I know next to nothing about Excel, and less about worksheet linking.) This method is arduous, and I have no idea how to limit it to cells that contain linked data, but I think you have to copy the linked cells and paste them back into place as values.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Break Link thru' VBA (VBA/MS Excel/97)

    Thanks Jefferson

    I have about thirty workbooks that can be used to create these reports. About 12 sheets in each have the links on them with between 20 and 400 cells actually linked.

    Those worksheets are protected so that users can only add information where allowed, therefore it would be not impossible to come up with some code that can find each link. The contents in each cell could be copied and then pasted as values only but I fear there are too many individual links and the users will get click happy whilst waiting for the sands to sift thru' the hourglass (thought; does shaking it make it run faster?)

    All suggestions most gladly considered.

    Leigh

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Break Link thru' VBA (VBA/MS Excel/97)

    Thank you Hans

    I had heard of that link finder XLA but had not explored it. Now that you have reminded me, I'll look into it.

    BTW, other VBA afficionados will appreciate the site that Hans mentioned. It has some very useful links ('scuse the pun) and information for VBA and MS Excel (most versions). Now if there was a VBA command to break any links in workbook and replace them with the value I would be the happiest person this side of the Pacific. (I really must get a life, I think!)

  5. #5
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Break Link thru' VBA (VBA/MS Excel/97)

    Check out This post
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

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

    Re: Break Link thru' VBA (VBA/MS Excel/97)

    (Edited by HansV to update link to Excel MVP site)

    Bill Manville's FindLink utility may help you to find, list and/or delete links. You can download it from here.

Posting Permissions

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