Ask Your Question
0

Calc: Referencing a directory/file name with an aposthophe in it

asked 2016-12-14 23:07:47 +0100

redwdc gravatar image

I use 1 file per year with 14 28 day sheets (over lap of months). I'm trying to copy the data from one file to the next.

However, since the directory they are in is called RED's (with the apostrophe) I get Err.508 in the cell. On the formula bar I see:

=SUM('file:///d:/data/red's/misc_2016.ods'#$'16-12-26'.r30)

I tried putting double quotes " around the directory and file name i.e.

=SUM('file:///"d:/data/red's/misc_2016.ods"'#$'16-12-26'.r30)

But that didn't work. As a test I renamed the directory to REDs and it worked. Other programs/computers reference RED's so I'd rather not rename permanently it if possible.

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
1

answered 2016-12-15 17:03:36 +0100

pierre-yves samyn gravatar image

Hi

According to my tests it is enough to double the quote.

='file:///C:/Users/pierre-yves/Documents/Red''s/Untitled 1.ods'#$Sheet2.A3

Regards

edit flag offensive delete link more

Comments

Did you also try whether the external reference is correctly generated when selected with the mouse?
(It isn't! Is this a bug? Should a dev worry about it? Who should report it? I insist on the advice I gave in my answer. It's so simple.)

Lupp gravatar imageLupp ( 2016-12-15 17:14:53 +0100 )edit

BTW: I tested with V5.2.2.2 and could verify the "double-the-apostrophe" advice. A subsequent Ctrl+Z to look again on the previous state messed up the reference completely.

Lupp gravatar imageLupp ( 2016-12-15 19:24:38 +0100 )edit

@Lupp I'm sure the external reference is correct. Also it isn't a bug Pierre-Yves answer works. Also I'm using 5.2.3.3 sorry I forgot to put that in my question.

redwdc gravatar imageredwdc ( 2016-12-16 00:25:25 +0100 )edit
0

answered 2016-12-15 03:36:02 +0100

robleyd gravatar image

Try replacing the apostrophe with %27 - see https://wiki.openoffice.org/wiki/URL_...

edit flag offensive delete link more

Comments

@robleyd LO changes the %27 to a double apostrophe. However, I do thank you for this and will keep it in mind if I ever have similar problems in the future.

redwdc gravatar imageredwdc ( 2016-12-16 00:34:39 +0100 )edit
0

answered 2016-12-15 14:51:48 +0100

Lupp gravatar image

updated 2016-12-15 14:58:01 +0100

Ther best idea would surely be to omit the naming of folders, files and sheets not regarding any reasonable syntax.

A folder name like "Red's" (the apostrophe) will cause problems again and again. Just a little less awful are sheet name starting with digits and/or containing spaces and non-word characters ...
I wouldn't waste an hour to find a workaround.

A reasonable syntax for names you may use if you want to avoid the kind of problems should be:
(1) Start with a letter.
(2) Continue with zero or more characters from the set including letters, digits and the underscore.

The respective RegEx would be [a-zA-Z][a-zA-Z0-9_]*. This may look "old style". Old style is best with this respect. It is still the mandatory syntax for names in relevant programming languages. (Using RegEx in LibO Calc functions will not distinguish by upper/lower case.)

edit flag offensive delete link more

Comments

@Lupp I'm aware of naming nomenclature in Windows. This is the first time I'm had a problem with an apostrophe that I wasn't able to figure out on my own.

redwdc gravatar imageredwdc ( 2016-12-16 00:38:34 +0100 )edit

I know that Winows pretends to handle these things and so does LibO concerning sheet names. I wanted to emphasise that this is bad. I personally never use apostrophes, or even spaces in any names, and I don't start sheetnames with a digit, but I am reminded of the "marginal" problems now and then when I find a request for help. Yours surely wasn't the last one.

Lupp gravatar imageLupp ( 2016-12-16 09:15:49 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-12-14 23:07:47 +0100

Seen: 107 times

Last updated: Dec 15 '16