Open Office relative path vs. hard coded path

Mailman42 · General User Joined: 20 Oct 2005 Posts: 11

I'm running debian etch, and OO 2.0.1

In moving OO documents from a Linux computer to an Apple laptop. I've
found something odd, when trying to link info among spreadsheets.

On the laptop, I can put the "relative" path, using "./" to get the
linking to work, yet n this Linux computer I have to specify the full
path to get the linking to work.

Yet, when I open the same documents in OO 1.1.3 things work as they are supposed to.

I always "assumed" that this was not necessary, but just using the "./"
would suffice. Am I wrong or am I missing something?

Thanks

Villeroy · Posted: Sun Jun 04, 2006 11:52 am Post subject:

How do you link informations?
Hyperlinks (could be buttons or text)
Hyperlink-formulas
URL-links (formulas having 'file://path/document.ods'#sheet1.A1)
DDE-links (formulas having DDE("soffice";"path/document.ods";"sheet1.A1")
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

Mailman42 · General User Joined: 20 Oct 2005 Posts: 11

DDE-links (formulas having DDE("soffice";"path/document.ods";"sheet1.A1")

This is the way things are linked.

It works under Linx & MAC OS X with the 1.1.3/4 releases, but neither OS works with OO 2.0.*

Thanks

Villeroy · Posted: Sun Jun 04, 2006 12:51 pm Post subject:

My default template has this set of named formulas (Ctrl+F3)
cFilename: CELL("FILENAME")
=cFilename returnes 'file:///home/andreas/Documents/OOo/datatest.ods'#$Tabelle1 in current doc
FileURL: MID(cFilename;2;SEARCH("'#";cFilename)-2)
=FileURL returnes file:///home/andreas/Documents/OOo/datatest.ods in current doc (without the single quotes and without sheet name)
FileURLPath MID(cFilename;2;SEARCH("/[^/]+'#";cFilename)-1)
=FileURLPath returnes file:///home/andreas/Documents/OOo/ in current doc
After adding a new name
FilePath RIGHT(FileURLPath;LEN(FileURLPath)-7)
=FilePath returnes /home/andreas/Documents/OOo/ in current doc
=DDE("soffice";FilePath&"oooforum.ods";"max")
returnes the value of named cell max in file oooforum.ods in this file's directory.
On a windows system (backslash) you would use:
FilePath =SUBSTITUTE(RIGHT(FileURLPath;LEN(FileURLPath)-7);"/";"\")
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

Mailman42 · General User Joined: 20 Oct 2005 Posts: 11

This is what works under OO1.1.3;

=DDE("soffice";"./bar_receipts2006.sxc";"April.I39")

This does not work under OO 2.0.1

Villeroy · Posted: Sun Jun 04, 2006 1:43 pm Post subject: Re: this is what I have

Mailman42 · General User Joined: 20 Oct 2005 Posts: 11

I'm "stuck" using OO 1.1.* for the time being?

Villeroy · Posted: Sun Jun 04, 2006 2:19 pm Post subject:

Open your doc
Call Menu:Insert>Names>Define...
Name:cFilename
refers to: CELL("FILENAME")
Hit button "Add"
Name:FileURLPath
refers to: MID(cFilename;2;SEARCH("/[^/]+'#";cFilename)-1)
Hit button "Add"
Name: FilePath
refers to: RIGHT(FileURLPath;LEN(FileURLPath)-7)
Hit button OK

Test formula =FilePath with any cell.

If the cell returnes the right path:
Menu:Edit>Find/Replace
Find: DDE("soffice";"./
Replace: DDE("soffice";FilePath&"
If the cell does not return the right path post an example of your DDE-function

If you want to use this technique in other docs make a template with the defined names.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

Mailman42 · General User Joined: 20 Oct 2005 Posts: 11

Villeroy · Posted: Sun Jun 04, 2006 3:22 pm Post subject:

After you have added the first named reference (name, formula, add-button) you can reuse the already opened dialog. Next name, next formula, add-button.
Add the other two names, confirm the dialog and test the named calculations by typing them as formulas into three cells:
=cFileName
=fileURLPath
=filePath

The third one (the one you want to use) is derived from the second one which could be used with other kinds of links. The second one is derived from the first one.
If you get the current document's path from the third formula then you may change your DDE-function by replacing the ./ with filePath and the filename.
"./Linked.ods" --> filePath&"Linked.ods".
I guess OSX uses slash as path-separator like any other unix system.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

Mailman42 · General User Joined: 20 Oct 2005 Posts: 11

In my version of OO 2.0.1, when I get to Insert -> Names -> Define or (CTRL -F3). I don't see a way to upload a screen capture;

DEFINE NAMES
NAME [__________________________} OKAY
CANCEL
HELP

ADD \ greyed out
DELETE/

I don't see anything on this OO 2.0.1 that has the formula.

Under 2.0.1, I can specify the full path, and that'll work;

=DDE("soffice";"/home/rodney/waste/test/some_file2006.sxc";"April.I39")

yet

=DDE("soffice";"/some_file2006.sxc";"April.I39")

does not.

I know I'm repeating, but I don't see the functions you are mentioning.

Villeroy · Posted: Sun Jun 04, 2006 5:05 pm Post subject:

Just type a name and what it refers to and you are allowd to add this named reference.
Then you can refer to this name in any formula.

If you can't get your named refs to work:

Villeroy · Posted: Sun Jun 04, 2006 5:25 pm Post subject:

Just another way to define your names:
Get an unused range, I would suggest a blank sheet.
First cell: =CELL("FILENAME")
In the name box -on the formula bar, left of the formula box- type: cFilename
Second cell: =MID(cFilename;2;SEARCH("/[^/]+'#";cFilename)-1)
Name box: FileURLPath
3rd cell: =RIGHT(FileURLPath;LEN(FileURLPath)-7)
Name box: FilePath

Now the three names refer to the three cells. You should hide or protect these cells in order to prevent accidental deleting or overwriting.
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

noranthon · Super User Joined: 07 Jul 2005 Posts: 3318

Mailman42 · General User Joined: 20 Oct 2005 Posts: 11

I did try to insert the formulas into a blank spreadsheet, keep getting "err: 502" messages.

I've changed "filename" to my spreadsheet name.

Tha's the best I can do right now.

Thanks to all