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

[redwdc]

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.

[PYS]

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

[Lupp]

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]

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.

[redwdc]

@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.

[robleyd]

Try replacing the apostrophe with %27 - see URL Basics - Apache OpenOffice Wiki

[redwdc]

@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.

[Lupp]

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.)

[redwdc]

@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.

[Lupp]

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.