Does anyone know how to write a macro that breaks the links in the SAME way as the button in the figure? If the button does this, then the code exists to break the DDELinks. Thank you.
Interrompí collegamento = Broke connection
You have to use some other type of link. When you break a DDE link, save and reload the client file, the DDE formula returns #N/A.
Ok, thanks Villeroy for the contribution, but is it possible to delete the list of links automatically through a macro? Sometimes LibreOffice crashes if the links are wrong (i.e. source file has been deleted) and I would like to prevent this automatically because the user gets angry.
When you enter a DDE formula, the formula will be evaluated and generate a DDE link. You have to remove or replace the formulas, otherwise you get new links every time the document is recalculated.
I agree with you Villeroy if the formulas are static, few and the end user is capable. If instead formulas are as many as these = VLOOKUP ($ B2: $ B500; DDE (“soft”; filename; “$ sheet1. $ D $ 1: $ K $ 1048576”); 2; 0) where filename is a value of a cell that can change continuously, I will have a DDELinks list that grows out of all proportion and must be managed automatically.
Well, then handle it automagically. But first you’ve got to find out what to do with all these formulas with invalid references.
The following will replace DDE formulas with the formula string:
menu:Find&Replace…
[X] All sheets
[Other Options]
Search in: Formulas
[X] Regular expressions
Find: ^=.*DDE\(
Replace: '&
This should find everything starting with an equal sign and having “DDE(” somewhere. This string will be replaced with a leading apostrophe and the matched string itself, effectively adding a leading apostrophe. The cell’s text will be the formula without the leading apostrophe.
In current versions the resulting text will include the leading apostrophe (if the remainder couldn’t be converted to numeric). Starting with 7.5 all input in a cell not formatted as Text will have a leading apostrophe removed and the remainder set as text content, see ReleaseNotes/7.5 - The Document Foundation Wiki .
Btw, the search expression needs to escape the opening parenthesis: ^=.*DDE\(
1 Like
It is not needed. In fact, filename is the name of an area or cell (for example C32) where the name of the source file is inserted. I just have to change the incorrect value present in the reference cell filename (C32) and everything goes back to normal if it were not for the DDELinks list which has now grown.
I’m making a summary after 4 years:
I have formulas like:
=VLOOKUP(…;DDE(“soffice”;FiledbProdotti;…);…)
where FiledbProdotti is the name of a cell that contains the path of the source file.
When the path changes:
- LibreOffice creates new DDE links.
- The old DDE links are not removed.
- Both old and new links appear in Edit → External Links (above all if are wrongs).
- When the spreadsheet is opened, Calc also tries to update the old links and displays warning messages.
What I verified
-
The DDE formulas continue to work after manually breaking the old links.
-
Both the old and the new file paths are present inside the ODS
content.xml. -
The entries shown in Edit → External Links come from XML
table:dde-linkdefinitions. -
There does not seem to be an obvious LibreOffice Basic API that can perform the equivalent of “Break All Links”.
Ideal Solution
A macro or command that:
- Reads the list shown in Edit → External Links.
- Executes Break Link on every entry automatically.
This would be the cleanest solution, but I have not yet identified the UNO API used by that dialog.
Solution Most Likely to Work
Modify the ODS file directly:
- Open the ODS as a ZIP archive.
- Edit
content.xml. - Remove all
<table:dde-link>nodes. - Save the ODS.
Expected result:
- The External Links list becomes empty.
- All DDE formulas remain unchanged.
- When Calc recalculates the document, only the links that are actually required by the current formulas are recreated.
But this is too cumbersome.
Replace DDE functions with external references like
='file:///path/name.xls'#$Sheet1.$D$1:$K$999
DDE TEST.zip (185.8 KB)
Instructions: Save the various ce_DBProdotti.ods in a c:\temp\ folder. Try closing and saving the main document several times with references to the various CE_dbProdotti. Thanks for your patience.
Contrary to DDE, which works with reference strings, the external reference is a true relative, mixes or absolute reference. You can edit one formula and drag down (or copy) the formula.
The referenced data are stored in a hidden cache within the document. The links keep on working even if the source file is unavailable.
Thank you for your suggestion.
I agree that external references are probably a more robust solution than DDE, especially because of the built-in cache and the fact that they can continue to work even when the source file is temporarily unavailable.
However, in my case the issue is slightly different. The workbook already contains several DDE-based formulas where the file path is not hardcoded but provided dynamically through a cell value:
DDE(“soffice”;FiledbProdotti;…)
When the content of FiledbProdotti changes, Calc creates new DDE links for the new path, but it seems that the old DDE link definitions remain stored in the document. As a result, the list in Edit → External Links accumulates obsolete entries and Calc tries to update them when the document is opened, producing warning messages.
I verified that after manually breaking an obsolete link, the current formulas continue to work correctly using the new path, which suggests that those old links are no longer actually used by any formula.
At the moment I’m therefore trying to find a way to remove or rebuild the stale DDE link definitions stored in the ODS file, rather than replacing the DDE formulas themselves.
Thanks again for pointing out the alternative approach with external references. It may indeed be worth considering for future development.
In fact, it’s not possible to break DDE links using macro code because they’re not set on Calc UI level but in C++ core level.
It would be interesting to know the context in which you need to establish a connection to a Calc cell. Depending on the case, it is possible to establish a connection to one or more cells in a way that is significantly better than a DDE link connection.
Here’s a simple sample of how to connect to a cell in an external Calc project without messing with DDE link hell. Download all three projects and place them in the same directory.
Server.ods (9.8 KB)
CalcClient.ods (14.8 KB)
WriterClient.odt (15.2 KB)
Test: Open one of the client projects, select Macro from the menu bar and click Run…
This system works foolproof! I almost got mad, the site didn’t allow to update fixed projects at first
… continuation of the previous one:
Using your imagination and code ideas, you can build a system that communicates bidirectionally between client and server that beats the DDE link system at any time.
DualBindingDynamicGridForDialog.ods (20.7 KB)