使用 VBA 检查文件是否存在

Check if the file exists using VBA

提问by Dinesh Goel

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

In this when i pickup the text value from the input box, it doesn't work. If however, if remove "the sentence"from If Dir()and replace it with an actual name in the code, it works. Can somebody help?

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

回答by Cylian

Note your code contains Dir("thesentence")which should be Dir(thesentence).

请注意您的代码包含Dir("thesentence")哪些应该是Dir(thesentence).

Change your code to this

将您的代码更改为此

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

回答by ErikE

Use the Office FileDialogobject to have the user pick a file from the filesystem. Add a reference in your VB project or in the VBA editor to Microsoft Office Libraryand look in the help. This is much better than having people enter full paths.

使用 OfficeFileDialog对象让用户从文件系统中选择一个文件。在您的 VB 项目或 VBA 编辑器中添加一个参考Microsoft Office Library并查看帮助。这比让人们进入完整路径要好得多。

Here is an example using msoFileDialogFilePickerto allow the user to choose multiple files. You could also use msoFileDialogOpen.

这是一个msoFileDialogFilePicker允许用户选择多个文件的示例。您也可以使用msoFileDialogOpen.

'Note: this is Excel VBA code
Public Sub LogReader()
    Dim Pos As Long
    Dim Dialog As Office.FileDialog
    Set Dialog = Application.FileDialog(msoFileDialogFilePicker)

    With Dialog
        .AllowMultiSelect = True
        .ButtonName = "C&onvert"
        .Filters.Clear
        .Filters.Add "Log Files", "*.log", 1
        .Title = "Convert Logs to Excel Files"
        .InitialFileName = "C:\InitialPath\"
        .InitialView = msoFileDialogViewList

        If .Show Then
            For Pos = 1 To .SelectedItems.Count
                LogRead .SelectedItems.Item(Pos) ' process each file
            Next
        End If
    End With
End Sub

There are lots of options, so you'll need to see the full help files to understand all that is possible. You could start with Office 2007 FileDialog object(of course, you'll need to find the correct help for the version you're using).

有很多选项，因此您需要查看完整的帮助文件以了解所有可能的内容。您可以从Office 2007 FileDialog 对象开始（当然，您需要为您使用的版本找到正确的帮助）。

回答by amackay11

Correction to fileExists from @UberNubIsTrue :

更正来自 @UberNubIsTrue 的 fileExists ：

Function fileExists(s_directory As String, s_fileName As String) As Boolean

  Dim obj_fso As Object, obj_dir As Object, obj_file As Object
  Dim ret As Boolean
   Set obj_fso = CreateObject("Scripting.FileSystemObject")
   Set obj_dir = obj_fso.GetFolder(s_directory)
   ret = False
   For Each obj_file In obj_dir.Files
     If obj_fso.fileExists(s_directory & "\" & s_fileName) = True Then
        ret = True
        Exit For
      End If
   Next

   Set obj_fso = Nothing
   Set obj_dir = Nothing
   fileExists = ret

 End Function

EDIT: shortened version

编辑：缩短版

' Check if a file exists
Function fileExists(s_directory As String, s_fileName As String) As Boolean

    Dim obj_fso As Object

    Set obj_fso = CreateObject("Scripting.FileSystemObject")
    fileExists = obj_fso.fileExists(s_directory & "\" & s_fileName)

End Function

回答by Joachim Brolin

Function FileExists(fullFileName As String) As Boolean
    FileExists = VBA.Len(VBA.Dir(fullFileName)) > 0
End Function

Works very well, almost, at my site. If I call it with "" the empty string, Dir returns "connection.odc"!! Would be great if you guys could share your result.

几乎在我的网站上工作得很好。如果我用“”空字符串调用它，Dir 将返回“ connection.odc”！！如果你们能分享你的结果，那就太好了。

Anyway, I do like this:

无论如何，我喜欢这样：

Function FileExists(fullFileName As String) As Boolean
  If fullFileName = "" Then
    FileExists = False
  Else
    FileExists = VBA.Len(VBA.Dir(fullFileName)) > 0
  End If
End Function

回答by whytheq

just get rid of those speech marks

去掉那些语音标记

Sub test()

Dim thesentence As String

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

This is the one I like:

这是我喜欢的一个：

Option Explicit

Enum IsFileOpenStatus
    ExistsAndClosedOrReadOnly = 0
    ExistsAndOpenSoBlocked = 1
    NotExists = 2
End Enum


Function IsFileReadOnlyOpen(FileName As String) As IsFileOpenStatus

With New FileSystemObject
    If Not .FileExists(FileName) Then
        IsFileReadOnlyOpen = 2  '  NotExists = 2
        Exit Function 'Or not - I don't know if you want to create the file or exit in that case.
    End If
End With

Dim iFilenum As Long
Dim iErr As Long
On Error Resume Next
    iFilenum = FreeFile()
    Open FileName For Input Lock Read As #iFilenum
    Close iFilenum
    iErr = Err
On Error GoTo 0

Select Case iErr
    Case 0: IsFileReadOnlyOpen = 0 'ExistsAndClosedOrReadOnly = 0
    Case 70: IsFileReadOnlyOpen = 1 'ExistsAndOpenSoBlocked = 1
    Case Else: IsFileReadOnlyOpen = 1 'Error iErr
End Select

End Function    'IsFileReadOnlyOpen

回答by Dan

I'm not certain what's wrong with your code specifically, but I use this function I found online (URL in the comments) for checking if a file exists:

我不确定您的代码具体有什么问题，但我使用我在网上找到的这个函数（评论中的 URL）来检查文件是否存在：

Private Function File_Exists(ByVal sPathName As String, Optional Directory As Boolean) As Boolean
    'Code from internet: http://vbadud.blogspot.com/2007/04/vba-function-to-check-file-existence.html
    'Returns True if the passed sPathName exist
    'Otherwise returns False
    On Error Resume Next
    If sPathName <> "" Then

        If IsMissing(Directory) Or Directory = False Then

            File_Exists = (Dir$(sPathName) <> "")
        Else

            File_Exists = (Dir$(sPathName, vbDirectory) <> "")
        End If

    End If
End Function

回答by Ronnie Royston

Function FileExists(fullFileName As String) As Boolean
    FileExists = VBA.Len(VBA.Dir(fullFileName)) > 0
End Function

回答by Word Nerd

Very old post, but since it helped me after I made some modifications, I thought I'd share. If you're checking to see if a directory exists, you'll want to add the vbDirectory argument to the Dir function, otherwise you'll return 0each time. (Edit: this was in response to Roy's answer, but I accidentally made it a regular answer.)

很旧的帖子，但由于我做了一些修改后它对我有所帮助，所以我想我会分享。如果要检查目录是否存在，则需要将 vbDirectory 参数添加到 Dir 函数，否则0每次都会返回。（编辑：这是对 Roy 的回答的回应，但我不小心将其作为常规答案。）

Private Function FileExists(fullFileName As String) As Boolean
    FileExists = Len(Dir(fullFileName, vbDirectory)) > 0
End Function

回答by Andreas Dietrich

based on other answers here I'd like to share my one-liners that should work for dirs and files:

基于这里的其他答案，我想分享我的单行代码，它应该适用于目录和文件：

• Len(Dir(path)) > 0 or Or Len(Dir(path, vbDirectory)) > 0  'version 1 - ... <> "" should be more inefficient generally
  

  • (just Len(Dir(path))did not work for directories (Excel 2010 / Win7))

• CreateObject("Scripting.FileSystemObject").FileExists(path)  'version 2 - could be faster sometimes, but only works for files (tested on Excel 2010/Win7)
  

• Len(Dir(path)) > 0 or Or Len(Dir(path, vbDirectory)) > 0  'version 1 - ... <> "" should be more inefficient generally
  

  • （只是Len(Dir(path))不适用于目录（Excel 2010 / Win7））

• CreateObject("Scripting.FileSystemObject").FileExists(path)  'version 2 - could be faster sometimes, but only works for files (tested on Excel 2010/Win7)
  

as PathExists(path)function:

作为PathExists(path)功能：

Public Function PathExists(path As String) As Boolean
    PathExists = Len(Dir(path)) > 0 Or Len(Dir(path, vbDirectory)) > 0
End Function