Check file existence with formula in excel

问题:

I have this requirement of creating hyperlink only if file exists at the location. as each file name will be different fetched from cells after entering data.

=IF(NOT(ISBLANK(J10)),HYPERLINK(O6&J10&".jpg", "Bill Available"),"")

I have this formula, which creates hyperlink as soon as i enter something in "J10" and creates hyperlink to path "O6" and file name "J10". Nut I dont want it to create hyperlink if file is not there... I'm thinking of using "AND" with "IF" but how to check file existence?

回答1:

You can do this with the help of a VBA User Defined Function.

1) Open the Visual Basic Editor and click Insert -> Module**
2) Paste the following code***:

Function FileExists(sPath As String)
    FileExists = Dir(sPath) <> ""
End Function

Then you can use FileExists in the same way as any other function. So, as you suggested:

=IF(AND(NOT(ISBLANK(J10)),FileExists(O6&J10&".jpg")),HYPERLINK(O6&J10&".jpg", "Bill Available"),"")

Should work.

** In newer versions of Excel, with the Ribbon, you may have to enable the 'Developer' tab (File -> Options -> Customize Ribbon -> Check 'Developer')

*** This code lifted from here, but with a typo corrected (FileExists1 -> FileExists).