Dde links to writer from calc

[thor2]

I have a Calc spreadsheet with more than 12 Writer reports linked to the spreadsheet using DDE paste link. Works great! Gives me automatically updated reports whenever the Writer doc. is opened.

However, if I move/copy the folder which contains the spreadsheet and reports (e.g. To another computer), all links are broken, causing all sorts of havoc!

I know that in Excel (cringe!), paste links are kept within the folder, so moving the folder has no effects on the links, but DDE links obviously use the full folder address to locate the data!

I’m thinking about putting the folder directly on C:\ drive, and then copying to C:\ drive on another computer, as needed, although this would only solve part of the problem, as I would really like to be able to run the whole thing from a thumb drive on occasions.

I’d love to hear if anybody can suggest a usable workaround that keeps the links within the folder?

[oweng]

This appears to be bug fdo#47223 however a possible workaround (using file:../filename notation) is provided. Note, that I cannot get the notation mentioned in the bug to work as described, but have had some success with file:./filename notation, which would seem more correct in any case.

I have done some rudimentary testing (TDF LO v4.0.2.2) using the DDE function, Edit > Paste Special… > Link / DDE Link (which I presume the question is referring to), and inserting OLE objects. The Save URLs relative to file system option that @mariosv mentions does not always appear to prevent a relative URL being transformed into an absolute one. All examples below are performed with the Save URLs relative to file system option checked.

Paste ODT text into ODS cell

Edit > Paste Special… > DDE Link menu / dialog. This is the default link created when pasting a heading from an ODT into a cell of an ODS (heading is displayed as expected):

=DDE("soffice","/home/oweng/temp/LO_test/text.odt","__DdeLink__60_870516294")

An edited version of the pasted link (heading is displayed as expected):

=DDE("soffice","file:./text.odt","__DdeLink__60_870516294")

An edited version of the pasted link (results in #N/A):

=DDE("soffice","./text.odt","__DdeLink__60_870516294")

An edited version of the pasted link (results in #N/A):

=DDE("soffice","text.odt","__DdeLink__60_870516294")

The behaviour above is the same if linking (using this method) from one spreadsheet to another e.g., to a single cell value.

Paste separate ODS file (cell) into another ODS file (cell)

Edit > Paste Special… > Options > Link menu and dialog. Admittedly this is not making use of the DDE function. This is the default link created when pasting a cell from one ODS into a cell of another ODS (cell value from the source sheet is displayed as expected):

='file:///home/oweng/temp/LO_test/values.ods'#$Sheet1.B2

This is the displayed link after being edited to ='file:./values.ods'#$Sheet1.B2 (naturally, cell value remains unchanged):

='file:///home/oweng/temp/LO_test/values.ods'#$Sheet1.B2 

This is the displayed link after being edited to ='./values.ods'#$Sheet1.B2 (naturally, cell value remains unchanged):

='file:///home/oweng/temp/LO_test/values.ods'#$Sheet1.B2

This is the displayed link after being edited to ='values.ods'#$Sheet1.B2 (naturally, cell value remains unchanged):

='file:///home/oweng/temp/LO_test/values.ods'#$Sheet1.B2

It is not possible to paste text content (e.g., from an ODT) via the Edit > Paste Special… > Options > Link method.

[mariosv]

Hi Oweng, if I am not wrong changing the option to relative, the path is saved relative, even you see an absolute path in the formula.

[oweng]

For the cases listed above using the Edit > Paste Special… > Options > Link menu and dialog this does not appear to be the case. What I am seeing displayed is also in the XML. I will admit that editing this links is difficult as they contain surrounding curly brackets {} that disappear after editing. Have I made an error or gone about this incorrectly?

[mariosv]

I think they are treated as arrays, use Ctr+Shift+Enter instead of only Enter.

[oweng]

Thanks. That worked in terms of editing. The full path is still stored in the XML though. It is not really relevant to the question (my bit I mean) as it is not a DDE link. I included those examples for comparison.

[mariosv]

Hi Thor, take a look to the option in Menu/Tools/Options/Load-save/General - Save URLs relative to file system, maybe can help.

[thor2]

Thanks for the help guys. Sadly, this is beyond me, much as I tried!

I’ve run out of time, so I merged some of my reports, to reduce the number, and shortened the file path to C:\foldername\filename.ods with all reports in C:\foldername
I then made copies of my reports and modified the link to E:\foldername\filename.ods, F:\foldername\filename.ods etc.

Clunky, I know, but it works, and I have a choice of where I load the folder on other machines or on a USB drive.

[Markeee]

Hi Thor, maybe you could try putting the reports into sheets and using Calc formulas? Alternatively, maybe you can use LibreOffice formulas in Writer to link the reports to the spreadsheet, avoiding DDE calls?

I know Calc reports may not be as free-flowing as with Writer. Some of the facilities that occur to me are word-wrapping, cell merging, sheet hiding and protecting, and the usual varieties of text enhancement such as font, size, bold, italics, etc. I assume the reports are much the same, with some data changes, each time they’re produced.

Hope these suggestions are useful, because I’ve never done what you’re doing.

I have decided that if possible I’d rather just use LibreOffice functions because (1) they are independent of the OS (2) I have had a lot of reliability problems with MS products.

Markeee.