Villeroy wrote: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?
Last edited by CapCrockett on Fri Jun 11, 2021 10:39 pm, edited 1 time in total.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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.
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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Villeroy wrote: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.
"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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Villeroy wrote:"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.
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice