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

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 :frowning:

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

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

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

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

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

@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

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?

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"));"")

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.

@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 :slight_smile:

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"));"")

@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 :slight_smile: