使用VBA检查文件是否存在

Question

Sub test()

thesentence = InputBox("Type the filename with full extension", "Raw Data File")

Range("A1").Value = thesentence

If Dir("thesentence") <> "" Then

    MsgBox "File exists."

Else

    MsgBox "File doesn't exist."

End If

End Sub

在这种情况下，当我从输入框中提取文本值时，它不起作用。但是，如果"the sentence"从If中删除Dir()并将其替换为代码中的实际名称，则它可以工作。有人可以帮忙吗？

Answer 1

请注意，您的代码Dir("thesentence")应包含Dir(thesentence)。将您的代码更改为此Sub test()thesentence = InputBox("Type the filename with full extension", "Raw Data File")Range("A1").Value = thesentenceIf Dir(thesentence) <> "" Then&nbsp; &nbsp; MsgBox "File exists."Else&nbsp; &nbsp; MsgBox "File doesn't exist."End IfEnd Sub

Answer 2

从@UberNubIsTrue对fileExists的更正：Function fileExists(s_directory As String, s_fileName As String) As Boolean&nbsp; Dim obj_fso As Object, obj_dir As Object, obj_file As Object&nbsp; Dim ret As Boolean&nbsp; &nbsp;Set obj_fso = CreateObject("Scripting.FileSystemObject")&nbsp; &nbsp;Set obj_dir = obj_fso.GetFolder(s_directory)&nbsp; &nbsp;ret = False&nbsp; &nbsp;For Each obj_file In obj_dir.Files&nbsp; &nbsp; &nbsp;If obj_fso.fileExists(s_directory & "\" & s_fileName) = True Then&nbsp; &nbsp; &nbsp; &nbsp; ret = True&nbsp; &nbsp; &nbsp; &nbsp; Exit For&nbsp; &nbsp; &nbsp; End If&nbsp; &nbsp;Next&nbsp; &nbsp;Set obj_fso = Nothing&nbsp; &nbsp;Set obj_dir = Nothing&nbsp; &nbsp;fileExists = ret&nbsp;End Function编辑：缩短版本' Check if a file existsFunction fileExists(s_directory As String, s_fileName As String) As Boolean&nbsp; &nbsp; Dim obj_fso As Object&nbsp; &nbsp; Set obj_fso = CreateObject("Scripting.FileSystemObject")&nbsp; &nbsp; fileExists = obj_fso.fileExists(s_directory & "\" & s_fileName)End Function