Linking between two separate spreadsheets result in Err:509.

Dear All,

Whenever I try to link to a cell in a different spreadsheet, the following
error results:
Err:509.

The following formula appears automatically as a result of my attempt to
link the two spreadsheets.
='file:///e:/dox/pc's documents/street address.ods'#sheet1.e4

The two spreadsheets have been created using LibreOffice 3.3.3 / OOO330m19
(Build:301) / tag libreoffice-3.3.3.1.

I understand that there is a workaround using DDE. But that may not be an
easy option for a general user who has limited understanding about how links
work in LibreOffice.

Further, spreadsheets created using MS Office and which have links leading
to cells in other Excel files would also require that such links be replaced
with the DDE syntax. An exercise which is cumbersome in many cases where
spreadsheets run into dozens in a single file.

Yours respectfully,
Fruitjam

The apostrophe in pc's is the culprit.
Encoding the spaces and the apostrophe may work:
='file:///e:/dox/pc%27s%20documents/street%20address.ods'#sheet1.e4
If not, remove the apostrophe from the folder name.

Worked! Thanks!

But how do I go about encoding several scores of links in a given
spreadsheet? Do I have to manually edit all of those? Or is there a
shortcut?

I note that blank space, if present, in the sheet name need not be encoded.

Yours respectfully,
Fruitjam*

You can use find&replace to fix all your formulas.
Programmers, administrators and web-designers use to avoid special
characters in path names.
Apart from that, it is *always* a bad idea to split sheets across documents.
The target document keeps a copy of the referenced data anyway, so why not
store all the sheets in a single document?

Hi :slight_smile:
Is it possible to use Camel Case instead of using spaces? Spaces do seem to
cause confusions in some places but not others.
IFindNamesLikeThisReasonablyEasyToFollow = CamelCase
http://en.wikipedia.org/wiki/CamelCase
It's also quite commonly used in wiki pages to quickly link to pages

Alternativessuchasthis are almost impossible to read and although i like
Things-like-this or
Underscores_between_words
they sometimes create problems too. CamelCase seems to work everywhere tho.

Regards from
Tom :slight_smile:

How do I use find & replace to fix all the formulas which have such lengthy
reference statements? For starters, each cell would have to be checked for
cell references, and then again checks will have to be conducted to identify
those with apostrophes and spaces within the formulas.

The problem is that I do not choose how my users manage their data across
documents. My advice may not be heeded simply because the spreadsheets and
their data come from varied sources. And therein lies the crux of the
problem.

Yours respectfully,
Fruitjam

Because the primary doc may be used by an employee and that doc
generates information the employee should have no access to. The second
doc has the private data.

This is useful when the primary doc uses a macro to generate the data
for the second doc so the primary doc does NOT have a copy of the output
of the macro.

Well, you are in the ugly trap of "document security" which simply does not
exist. Once the document is loaded, all referenced data are freely
accessible by means of formulas, macros or using your web browser
(content.xml).
In case of URL links the data are embedded in the document, which is why all
formulas continue working even when the referenced files are unavaillable.
In case of DDE links the referenced documents are loaded invisibly.

A simple database could solve all of your problems. I learned this lesson 12
years ago when my Excel/VBA solution caused considerable financial damage
and a database developer developed a solution within 3 days where I spent
hundreds of hours with VBA and interrelated spreadsheets.

Converting the data into database is not an option that users in my
workplace are keen to explore. Spreadsheets are quick, less time-consuming
when it comes to presenting reports on the fly for most.

I had the notion that LibreOffice supports MS Office 2010 spreadsheet links.
But I was taken aback when it was pointed out that references to external
workbooks created using MS Office 2010 do not even show up when opened in
LibreOffice Calc. Can you help me explain as to why that can possibly
happen?

For example, a cell containing a ROUND function with reference to a cell in
another workbook shows up as only ROUND(,2) when the spreadsheet is opened
in LibreOffice Calc.

Yours respectfully,
Fruitjam

fruitjam wrote:

How do I use find & replace to fix all the formulas which have such
lengthy
reference statements? For starters, each cell would have to be checked for
cell references, and then again checks will have to be conducted to
identify
those with apostrophes and spaces within the formulas.

menu:Edit>Find&Replace...
Search: 'file:///e:/dox/pc's documents/street address.ods'

Replace: 'file:///e:/dox/pc%27s documents/street address.ods'
or with 2 single quotes: 'file:///e:/dox/pc''s documents/street address.ods'
or with whatever file_URL you are going to use. I suggest file names without
special characters.

[More Options]
Regular Expressions =OFF
Search in: <Formulas>