在Excel中使用公式检查文件是否存在 [英] 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"),"")

我有这个公式，一旦我在"J10"中输入内容，它就会创建超链接，并创建指向路径"O6"和文件名"J10"的超链接.如果文件不存在，我不希望它创建超链接...我正在考虑将"AND"与"IF"一起使用，但是如何检查文件是否存在?

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?

推荐答案

您可以借助VBA用户定义函数来做到这一点.

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

1)打开Visual Basic编辑器，然后单击Insert -> Module **
2)粘贴以下代码***:

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

然后，您可以以与其他任何功能相同的方式使用FileExists.因此，正如您建议的那样:

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

应该工作.

**在功能区较新的Excel版本中，您可能必须启用开发人员"标签(File -> Options -> Customize Ribbon -> Check 'Developer')

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

***这段代码摘自此处，但修正了拼写错误(FileExists1 -> FileExists).

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

这篇关于在Excel中使用公式检查文件是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！