Applying BreakLink Method for all sheets and cells

[cphenley]

I would like to use the breaklink method in VB for all cells in al
sheets. The code listed in VB help is only for one cell. Can someon
help me modify the code for all cells in all open sheets?

Thanks

' Define variables as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

' Break the first link in the active workbook.
ActiveWorkbook.BreakLink _
Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLink

 

[Dave Peterson]

Actually, that code is for one link--no matter how many cells it's used in.

Maybe this will work better:

Option Explicit
Sub UseBreakLink()

Dim astrLinks As Variant
Dim iCtr As Long

' Define variable as an Excel link type.
astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)

If IsArray(astrLinks) Then
For iCtr = LBound(astrLinks) To UBound(astrLinks)
ActiveWorkbook.BreakLink _
Name:=astrLinks(iCtr), _
Type:=xlLinkTypeExcelLinks
Next iCtr
End If

End Sub

 

[cphenley]

The code runs without an error but the links aren't broken afte
execution.

The "links" I would like to break are similar to: =ATGetAgg('SI
K1'!$D$1, "", "", 'SIC K1'!$A$8, 'SIC K1'!$A$9, "1h", 10, 0, "5m", 1
1040, 0, 0) and are for pulling data from another program.

Should this approach work to break these links? Or am I implementin
it wrong somehow?

My ideal goal is to have a macro that reduces all of the cells t
values only, just like a paste-special

 

[Dave Peterson]

=ATGetAgg() is a userdefined function (developed in house)?

I used a UDF that was contained in another addin and passed it the address of a
range in another workbook.

That code saw both the UDF and the address as external links.
It converted the cells with the UDF to values and then blew up on removing the
links for the other workbook (since they were gone--the udf to values got rid of
that link).

But you say the code worked ok, so I'm guessing that that UDF was located in
that same workbook.

In my testing, the cells with links were converted to values.

So I don't have a guess why yours didn't get changed to values.

But you could try Bill Manville's FindLink program:
http://www.bmsltd.ie/MVP/Default.htm

And see how that handles your stuff.