"Unlink" another sheets with macro

Creating a macro - Writing a Script - Using the API

"Unlink" another sheets with macro

Postby ludoman » Fri Feb 22, 2008 11:00 pm

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
ludoman
 
Posts: 5
Joined: Fri Feb 22, 2008 9:40 pm

Re: "Unlink" another sheets with macro

Postby Villeroy » Fri Feb 22, 2008 11:17 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Unlink" another sheets with macro

Postby ludoman » Sat Feb 23, 2008 12:46 am

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

Code: Select all   Expand viewCollapse view
=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
ludoman
 
Posts: 5
Joined: Fri Feb 22, 2008 9:40 pm

Re: "Unlink" another sheets with macro

Postby Villeroy » Sat Feb 23, 2008 12:59 am

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: "Unlink" another sheets with macro

Postby ludoman » Sat Feb 23, 2008 3:05 am

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.. :-)
ludoman
 
Posts: 5
Joined: Fri Feb 22, 2008 9:40 pm

Re: "Unlink" another sheets with macro

Postby Villeroy » Sat Feb 23, 2008 4:00 am

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests