[Solved] How Do I Test For The Existence Of A File?

[CarlB1]

Have just upgraded from Apache OO to LOO.

I need to test for the existing of a file and used the macro below when I was using Apache OpenOffice but it doesn’t seem to work in LOO

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

[anon73440385]

For me it works (Linux, LibreOffice 6.2.5.2) - so please tell us about:

• Your operating system
• Your LibreOffice Version (LO version)
• Location where you stored the function (My Macros → Standard → Module1 / other location ?)
• Your settings of Macro Security - See Tools -> Options -> LibreOffice -> Security
• And error messges you may get

You may also want to check this one first

[CarlB1]

Windows 10 Home 64 bit (1903) OS Build 18362.239
LibreOffice 6.3.0.1 (x64)
Macro is loaded to LibreOffice Basic Macros → My Macros → Standard → MYFILEEXISTS → MYFILEEXISTS
Macro Security is Default (Very High)
Macro is being called from formula =IF(MYFILEEXISTS(C2)=“True”,IF(OR(K2=“Y”,N2="",N2=“DVD”,N2=“CD”),"",HYPERLINK(C2,“Click”)),"")
Cell C2 includes a valid URL i.e. “c:/users/carl/videos/attenborough/19521106 Animal, Vegetable, Mineral - The Pitt Rivers Museum, Dorset.mp4”
Cell K2=""
Cell N2=MP4
No error message
Appears that MYFILEEXISTS is returning “False”

Exact same setup works as planned in AOO

[anon73440385]

OK - Thanks for thre response. I cannot test since I cannot install release candidates / beta software ( LO 6.3 is not yet released and 6.3.0.1 is a RC1 - see here ) on my production windows system. I’d expect some functions not working in RC software.

I got no problem on LO 6.2.5.2 on Windows but one thing: In both my test I have reformatted the function to (If I copy & paste from your post, it throws an basic error)

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

[mikekaganski]

=IF(MYFILEEXISTS(C2)="True",IF(OR(K2="Y",N2="",N2="DVD",N2="CD"),"",HYPERLINK(C2,"Click")),"")

Why "True"? Why not TRUE(), or 1, or even just omit the comparison? I don’t see the function returning a string.

@anon73440385:

In both my test I have reformatted the function to (If I copy & paste from your post, it throws an basic error)

The original post has the function formatted “properly”; just OP didn’t bother to use proper formatting tools (available on the toolbar) to tell the system that the function is pre-formatted code.

[anon73440385]

@mikekaganski - good point, I just focussed on the macro working at all. Completely left the formula within the sheet due to OPs statement macro below … doesn’t seem to work in LO, and this made me just to test the macro, but not the formula and of course I got boolean TRUE and FALSE

[CarlB1]

I think the structure of the formulae is purely semantics as both the macro and the function work as intended in Microsoft Excel and AOO on the same machine - just not in LOO. Do I need to change the macro and/or the formulae to have them work in LOO?

[mikekaganski]

as both the macro and the function work as intended in Microsoft Excel and AOO on the same machine

No, MS Excel does not allow the equality of TRUE()=“True” (but yes, AOO does).

The correct formula is

=IF(MYFILEEXISTS(C2);IF(OR(K2="Y";N2="";N2="DVD";N2="CD");"";HYPERLINK(C2;"Click"));"")

[anon73440385]

No it isn’t semantic, because you defined the function as boolean and your formula contains a string comparison and string “True” is not boolean TRUE (test - and remove the quotation mark in your formula). And if the software tools you named work with the macro, then only due to the fact that they perform a type conversion.

[CarlB1]

@anon73440385 and @mikekaganski - Thank you both for your help, I have changed the formulae and everything now works OK. I’m sorry I wasn’t aware that I was relying on a “bug” in the other products for my formulae to work

[mikekaganski]

The function from the question works correctly. The problem is the formula with the user-defined function that is used in the spreadsheet (quoting from a comment by OP):

=IF(MYFILEEXISTS(C2)="True",IF(OR(K2="Y",N2="",N2="DVD",N2="CD"),"",HYPERLINK(C2,"Click")),"")

The formula includes a redundant comparison of the return value of MYFILEEXISTS, and the comparison is done wrong: it’s compared to a string "True". This works in AOO (for some unclear reason), but of course, it should not - and it does’t in LO, and also in MSO (at least in my testing with Excel 2016 with en_US UI on Russian OS - mentioning these details, since it could be locale-specific; if it is, then using that “equivalency” of a boolean value to a locale-specific string is wrong even more).

Since the function returns a boolean result, the correct way is to simply do

=IF(MYFILEEXISTS(C2);IF(OR(K2="Y";N2="";N2="DVD";N2="CD");"";HYPERLINK(C2;"Click"));"")

[CarlB1]

@anon73440385 and @mikekaganski - Thank you both for your help, I have changed the formulae and everything now works OK. I’m sorry I wasn’t aware that I was relying on a “bug” in the other products for my formulae to work