[Solved] Macro to break all links

[CapCrockett]

Edit: Split from [Solved] Need expert on DDE to fix problems with ThinkorSwim since this is a new question for a different forum.

Sorry, I was wrong about the multi-selection listbox. But you can break them all one by one. When the [Break] button has the focus, hit the space bar and confirm with the Enter key.
The DDE formulas remain on the sheets and when you save-reload the file, the existing links appear in the list again. I just tried with OpenOffice and a test document with 3 DDE links.

Could a defined number of loops macro be written that could go down the list and break each link?

[Villeroy]

There is no API method to do that. The links are removed automatically when you remove the linking formulas with DDE(...) or with 'file:///...'.
One could loop through the lists of area links and DDE links and use a search macro to find all occurrences of the significant formula parts. Thiis returns a collection of ranges. Then use rg.setDataArray(rg.getDataArray()) for each range in order to convert formulas to values.

Edit: and this would require that you follow all references that are used in a formula recursively, as in =DDE(A1;B1;C1)

----------
In case of sheet links created via menu:Insert>Sheet from File... a macro can switch the SheetLinkMode to css.SheetLinkMode.NONE
In case of database links, a macro could remove the linked database range or its ImmportDescriptor.

[Villeroy]

The following Python macro seems to do the job. It undiscriminatingly searches for all formula cells on all sheets having DDE( and/or 'file:/// anywhere in the formula and converts all these ranges into constant data.
It can convert DDE links, area links or both on one go.

Code: Select all

def convertFormulas(doc, s):
    from com.sun.star.sheet.CellFlags import FORMULA
    eSh = doc.Sheets.createEnumeration()
    while eSh.hasMoreElements():
        sh = eSh.nextElement()
        fmlrgs = sh.queryContentCells(FORMULA)
        dsc = sh.createSearchDescriptor()    
        dsc.SearchRegularExpression = False
        dsc.SearchType = 0
        dsc.setSearchString(s)
        rgs = fmlrgs.findAll(dsc)
        if rgs:
            e = rgs.createEnumeration()
            while e.hasMoreElements():
                rg = e.nextElement()
                rg.setDataArray(rg.getDataArray())

def unlink_DDELinks(**args):
    doc = XSCRIPTCONTEXT.getDocument()
    convertFormulas(doc, 'DDE(')

def unlink_AreaLinks(**args):
    doc = XSCRIPTCONTEXT.getDocument()
    convertFormulas(doc, "'file:///")

def unlink_DDE_and_AreaLinks(**args):
    unlink_DDELinks()
    unlink_AreaLinks()
    
g_exportedScripts = unlink_DDE_and_AreaLinks, unlink_AreaLinks, unlink_DDELinks

Edit: Same in StarBasic

Code: Select all

Sub convertFormulas(doc, s)
    FORMULA = com.sun.star.sheet.CellFlags.FORMULA
    eSh = doc.Sheets.createEnumeration()
    while eSh.hasMoreElements()
        sh = eSh.nextElement()
        fmlrgs = sh.queryContentCells(FORMULA)
        dsc = sh.createSearchDescriptor()    
        dsc.SearchRegularExpression = False
        dsc.SearchType = 0
        dsc.setSearchString(s)
        rgs = fmlrgs.findAll(dsc)
        if Not (rgs Is Nothing) then
            e = rgs.createEnumeration()
            while e.hasMoreElements()
                rg = e.nextElement()
                rg.setDataArray(rg.getDataArray())
            wend
        endif
    wend
End Sub

Sub unlink_DDELinks()
    convertFormulas(ThisComponent, "DDE(")
End Sub

Sub unlink_AreaLinks()
    convertFormulas(ThisComponent, "'file:///")
End Sub

Sub unlink_DDE_and_AreaLinks()
    unlink_DDELinks()
    unlink_AreaLinks()
End Sub

[CapCrockett]

The following Python macro seems to do the job. It undiscriminatingly searches for all formula cells on all sheets having DDE( and/or 'file:/// anywhere in the formula and converts all these ranges into constant data.
It can convert DDE links, area links or both on one go.

I appreciate your effort, but this macro doesn't perform the same function as Edit -> Links... -> Break Link (window opens with a list of links).

As you described, it converts the link into "constant data." The Break Link function doesn't do that. It removes the DDE link from the list in the Edit Links window, but doesn't remove (or convert) the DDE formula from the cell.

[Villeroy]

"Break Link" does not break anything as long as the linking formulas remain on the sheet. The API does not offer anything to remove links other than removing the linking formulas which also removes the link from the dialog.

[CapCrockett]

"Break Link" does not break anything as long as the linking formulas remain on the sheet.

Correct. However, my options tracker spreadsheet changes the DDE calls with each use, but the old links still exist after the cell in which they exist is updated with a new DDE call for a new option. The "old" links are not saved in the list unless I save the spreadsheet. I must save some of them, but not others. Over time the links accumulate and affect the loading time for those that I must save.

The Break Links function performs a "housekeeping" or "clean up" function.

I think I can accomplish the same thing with an external (to Calc) macro recorder that records keystrokes and mouse movements and clicks.

Thank you for your help. Please do not spend any more of your time on this.

[Villeroy]

If the DDE links you want to remove all return errors, the macro works with a little modification in the working routine:

Code: Select all

def convertFormulas(doc, s):
    from com.sun.star.sheet.FormulaResult import ERROR
    eSh = doc.Sheets.createEnumeration()
    while eSh.hasMoreElements():
        sh = eSh.nextElement()
        fmlrgs = sh.queryFormulaCells(ERROR)
        dsc = sh.createSearchDescriptor()   
        dsc.SearchRegularExpression = False
        dsc.SearchType = 0
        dsc.setSearchString(s)
        rgs = fmlrgs.findAll(dsc)
        if rgs:
            e = rgs.createEnumeration()
            while e.hasMoreElements():
                rg = e.nextElement()
                rg.setDataArray(rg.getDataArray())

This does the same trick but only with formula cells that return an error.

[CapCrockett]

If the DDE links you want to remove all return errors, the macro works with a little modification in the working routine:

Thank you!