Check if the file exists using 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

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?

score 152 · Accepted Answer · answered Jul 20 '12 at 06:31

152

Note your code contains Dir("thesentence") which should be 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

answered Jul 20 '12 at 06:31

Cylian

10,970
4
42
55

1

It helped me partially I think when I use this code compiler doesn't consider "thesentence" as a directory so I had used just one more line of code before If: Dim Path as String Dim Directory as String Path = InitialPath & "\" & Date$ Directory = Dir(Path, vbDirectory) when I used ** Directory ** variable string under If Dir(Directory) <> "" then it worked well – muhammad tayyab Jan 09 '17 at 07:46
This is my go-to method for checking if a file (or path) exists. Consider adding error handling or ignore errors for `Dir(thesentence)` since invalid characters in the path will generate an error. – ChrisB Sep 20 '22 at 21:26
@ChrisB: `Dir` function itself will handle the error checking...did you checked? – Cylian Sep 29 '22 at 06:57
@Cylian: You can trigger run-time error 52 with this code: `Dir("*\")` However unlikely an error is to occur, I prefer to handle it. In this case, I use `On Error Resume Next` to ignore errors. Then I assign a String variable a value using `Dir()`. Finally I use `On Error Goto...' to resume error handling. Depending on the intended purpose it may be desirable for the program to display the error but I prefer to handle the error with code. – ChrisB Sep 30 '22 at 17:15

ErikE · Answer 2 · 2013-05-01T20:33:12.037

Use the Office FileDialog object 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 Library and look in the help. This is much better than having people enter full paths.

Here is an example using msoFileDialogFilePicker to allow the user to choose multiple files. You could also use 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).

+1 Much better, as this is excel Application.FileDialog(msoFileDialogOpen) — Alex K., Jul 20 '12 at 09:19

amackay11 · Answer 3 · 2013-05-31T12:29:21.780

20

Correction to fileExists from @UberNubIsTrue :

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

edited May 31 '13 at 12:29

answered May 29 '13 at 19:14

amackay11

719
1
10
17

2

Why does the code test the same file name multiple times (once for every file in s_directory)? There is no point in repeating this test. You will notice that the loop variable (obj_file) is not used by the loops code. – grantnz May 29 '13 at 20:08
1

@grantnz Indeed! I had done the lazy copy/paste and tweaked until it worked. Shortened version is above. Thanks. – amackay11 May 31 '13 at 12:31
Has anyone noticed that sometimes this test reports a false positive, i.e. it returns ´True´ even when a file does not exist in the file system. In my application I checking the existence of a file in a server on my network, if that gives you a hint. – pablete May 14 '14 at 16:27

whytheq · Answer 4 · 2014-09-23T16:35:31.953

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

I've never heard of "speech marks" before. Strange. Kind of a misnomer in my opinion. Plenty of things require quotes but aren't speech. — ErikE, Jul 21 '12 at 16:06
@ErikE +1 for nice observation ... [Pictures Here](https://www.google.com/search?q=speech+marks&hl=en&rlz=1C1CHRG_en&prmd=imvns&tbm=isch&tbo=u&source=univ&sa=X&ei=UtQKUPS-IMqM0wWg-cyyCg&ved=0CHoQsAQ&biw=1280&bih=685) — whytheq, Jul 21 '12 at 16:11
Apparently "speech marks" is an informal version of "quotation marks". — ErikE, Jul 21 '12 at 16:23

score 8 · Answer 5 · answered Oct 22 '15 at 11:12

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.

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

Old post, but it looks like `dir("")` gives you the name of the first file in the current directory. In your case it was a file called `connection.odc`. — Corey, Sep 25 '17 at 02:57

score 5 · Answer 6 · answered Jun 14 '15 at 02:09

5

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

answered Jun 14 '15 at 02:09

Ronnie Royston

16,778
6
77
91

Dan · Answer 7 · 2014-01-21T06:17:59.497

4

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:

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

edited Jan 21 '14 at 06:17

answered Jul 20 '12 at 06:31

Dan

45,079
17
88
157

1

This will give return true if you give a path and don't know if it is a directory or not. If you want to test if a path is a file only, this won't work 100%. `? dir$("C:\Users\Chloe\AppData\Local\Temp\")` will give the first file in that directory, and it will not equal "", therefore, it will return true even if you leave `Directory` argument off or set it to false. – Chloe Jan 21 '14 at 02:05
@Chloe I guess it's assuming you will specify a file extension along with the file name so the ambiguity of being a directory doesn't really apply in that case. But sure, it could be more robust. It just depends on how deep a solution you need. But it certainly does work for the case specified by the OP – Dan Jan 21 '14 at 06:16

score 3 · Answer 8 · answered Dec 19 '18 at 03:55

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 0 each time. (Edit: this was in response to Roy's answer, but I accidentally made it a regular answer.)

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

Andreas Covidiot · Answer 9 · 2019-08-12T10:41:23.503

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)

as PathExists(path) function:

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

Check if the file exists using VBA

9 Answers9

Linked

Related