"Unlink" another sheets with macro

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
ludoman
Posts: 5
Joined: Fri Feb 22, 2008 9:40 pm

"Unlink" another sheets with macro

Post by ludoman »

Hi!
I created one CALC document where the cells imports data from other documents of calc.
The path of the source document is not fixed.
To solve this problem, created a cell where you can tell where is the source document.
The problem is that: the document does not update old links... he add the new links generating errors.
I need a macro to delete all links when I open the document.
Is possible do this?
Thanks

OO version: 1.0 (I need use this old version)
System: Windows XP
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Unlink" another sheets with macro

Post by Villeroy »

How did you establish the links? Are ther forumlas or named references containing URLs like 'file:///path/name.sxc'#$SheetX.$A$1:$B$2 with corresponding hidden sheets named 'file:///path/name.sxc'#$SheetX ?
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
ludoman
Posts: 5
Joined: Fri Feb 22, 2008 9:40 pm

Re: "Unlink" another sheets with macro

Post by ludoman »

my sheets have more or less 500 cells with formulas like this:

Code: Select all

=DDE("soffice";$D$1;"BolEstVT.D18")+DDE("soffice";$D$2;"BolEstVT.D18")+DDE("soffice";$D$3;"BolEstVT.D18")
In the cells D1, D2, D3... has

D1: C:\plan\Jan.sxc
D2: C:\plan\Feb.sxc
D3: C:\plan\Mar.sxc

This code works, but if i change the cell D1 for D2: C:\plan2\Feb.sxc, the link for D2: C:\plan\Feb.sxc stay in document...

Thanks
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Unlink" another sheets with macro

Post by Villeroy »

Ah, DDE is the way you go. Whatever you unlink or remove in the collection of links, by macro or in dialog Edit>Links..., whenever you trigger recalculation of those formulas the links will spring into existance again. You've got to remove/disable the formulas which trigger the creation of links.
Find/Replace with options "regular expressions", "all sheets", "in formulas".
Search: ^=.*DDE\(
Replace: '&
This will comment out all formulas containing "DDE("

Another option you should try is: Set all DDE links to manual update (Edit>Links...).

Possibly you are using the wrong type of links. The sheet links I mentioned do not have problems with moved directory trees. They can save links to relative paths by embedding a hidden data-copy of the referenced sheet.
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
ludoman
Posts: 5
Joined: Fri Feb 22, 2008 9:40 pm

Re: "Unlink" another sheets with macro

Post by ludoman »

Possibly you are using the wrong type of links. The sheet links I mentioned do not have problems with moved directory trees. They can save links to relative paths by embedding a hidden data-copy of the referenced sheet.
How can i do this? Relative paths.... i look for this way before use DDE...
If relative paths works, my problem will be solucioned.... :-)

Thanks for the help.. :-)
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Unlink" another sheets with macro

Post by Villeroy »

Try this:
Open both documents
Start a formula with "=" and point to a cell in the other document and hit enter.
You'll get a formula like ='file:///path/doc.sxc'#$SheetX.$A$1
It looks as if it where absolute, like the URL you get when you link to a picture. But it is relative in fact. Close and copy both documents, so they are in the same relative position to each other. The link should keep on working when you refresh it (Menu:Edit>Links...[Update]) manually. At least it does with version 2.3.1.
Notice that reference ='file:///path/doc.sxc'#$SheetX.$A$1refers to a cell A1 on a hidden sheet named ='file:///path/doc.sxc'#$SheetX. When you update this type of link it re-reads the external sheet's data from disk into the hidden sheet. This is why it keeps on working even if the other file is not there (until you try to update the link).
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
Post Reply