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

Topic

New Reply

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

Reply | Quote

Replies

(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.

Reply | Quote

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

Reply | Quote

(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.

Reply | Quote

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!)

Reply | Quote

Check out This post

Reply | Quote