HELP: Macro - To convert formulas only from COM Add-ins to Values

[birdmancpa]

Hi,

Can anyone help me create VBA code for converting formulas/links only relating to external program data sources (and leaving regular functions, links to worksheets and outside workbooks in tact)??

So far this is what I have written:
Sub Break_Links_To_External_Program_Links()
'Convert formulas that point to plugin data sources to values
'It will not convert other Excel formulas to values.
Dim xLink As Variant
xLink = Application.ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
Do Until IsEmpty(xLink)
Application.ActiveWorkbook.BreakLink Name:=xLink(1), Type:=xlLinkTypeExcelLinks
xLink = Application.ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
Loop
End Sub


If the external program functions are as such: =SMAcctBal($A37,$C$1,C$2,"A","P")

can I replace the 1 in [Name:=xLink(1)]
with SMAcctBal

Any help is greatly appreciated.

Thanks

 

[birdmancpa]

So I got this far:

Sub adjust()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Call FindFormula(ws)
Next
End Sub

Private Sub FindFormula(ws As Worksheet)
Dim formulaSearch()
Dim i As Integer
Dim cell As Range
formulaSearch = Array("SMAcctBal*")
For i = 0 To UBound(formulaSearch)
With ws.UsedRange.SpecialCells(xlCellTypeFormulas)
Set cell = .Find(formulaSearch(i), LookIn:=xlFormulas)
Do Until cell Is Nothing
cell.Value = cell.Value
Set cell = .FindNext(cell)
Loop
End With
Next i

End Sub



However, it is working in my personal macro workbook, but I am still getting a Run-time error '1004' at the end of it and it doesn't seem to work but only in the personal, please any help debugging?? Thanks