How to alter macro to allow special characters in filename

[CarlB1]

Am using LibreOffice 6.3.01 x64 on Windows 10 64bit

The macro below doesn’t seem to allow for special characters in my filename (e.g. comma or single quote).

How do I need to change the macro so that it allows all valid filename characters please?

REM  *****  BASIC  *****
Option explicit

Function MYFILEEXISTS(sFullPath as string) as boolean

dim sFileUrl as string

   sFileURL = ConvertToUrl(sFullPath)   
   
   If FileExists(sFileUrl) then 
      MYFILEEXISTS = True
   else 
      MYFILEEXISTS = False
   end if
End function

[gabix]

In order to hardcode, you need to encode them as in URLs (such as %2C for a comma, see this). I don’t know how to do it in StarBasic, though, meaning, if there is a dedicated function.

[PYS]

Hi

I have just tested this procedure on windows and it works as expected with file names like a,b.html or a'b.html

Besides, I’m probably from an old world but I’m not completely sure it’s a good practice to name files like that

Regards

[anon73440385]

Hmm - I ask myself whether this is a question to be addressed to users of LibreOffice. The macro above relies on the Basic function ConvertToUrl and if this function cannot deal with filenames specific to an operating system, then this may be considered a bug of LibreOffice Basic (or at least an enhancement request may be required)

[CarlB1]

What does the “sFileURL = ConvertToUrl(sFullPath)” actually do? Is it needed at all given all my filenames are already full pathnames?

[anon73440385]

As far as i understand function FileExists allows two modes to pass a filename

• system notification – and –
• URL notification

(see here). And function ConvertToUrl - nomen est omen - converts the text string to URL type. According to FileExists your macro should also work, if you

-- dim sFileUrl as string
-- sFileURL = ConvertToUrl(sFullPath)  
-- If FileExists(sFileUrl) then 
++ If FileExists(sFullPath) then 

(Did not yet test)

[CarlB1]

@anon73440385 - I have changed macro and tested as suggested but it makes no difference. Perhaps the problem is with my formulae rather than the macro itself?

=IF(MYFILEEXISTS(C7018),IF(OR(K7018=“Y”,N7018="",N7018=“DVD”,N7018=“CD”),“not online”,HYPERLINK(C7018,“Click”)),“not exist”)

This returns “not exist” for any filename that contains a single quote. Can you suggest how I should alter please?

[anon73440385]

It got nothing to do with your formula but the interpretation of apostroph and single quote and the replacements performed by Calc, when you input a ' into Calc text and thus the filename in your sheet changes. See Tools -> AutoCorrect Options -> Tab: Localized Options. The option Replace in category Single Quotes is ticked by default, and this causes a change in the string of the filename. Try to untick and check again - during my test this made it working.

Note; Do not test with filename strings, where the change already has been occurred. Insert a new filename.