VBA check if file exists

Question

I have this code. It is supposed to check if a file exists and open it if it does. It does work if the file exists, and if it doesn't, however, whenever I leave the textbox blank and click the submit button, it fails. What I want, if the textbox is blank is to display the error message just like if the file didn't exist.

Runtime-error "1004"

Dim File As String
File = TextBox1.Value
Dim DirFile As String

DirFile = "C:\Documents and Settings\Administrator\Desktop\" & File
If Dir(DirFile) = "" Then
  MsgBox "File does not exist"
Else
    Workbooks.Open Filename:=DirFile
End If

Cyrille

You haven't provided the problem part of the code (ie the Form that contains the Submit button). Can you share your file? – Ramose

the code above is the content of my submit button – Cyrille

Answer 1

something like this

best to use a workbook variable to provide further control (if needed) of the opened workbook

updated to test that file name was an actual workbook - which also makes the initial check redundant, other than to message the user than the Textbox is blank

Dim strFile As String
Dim WB As Workbook
strFile = Trim(TextBox1.Value)
Dim DirFile As String
If Len(strFile) = 0 Then Exit Sub

DirFile = "C:\Documents and Settings\Administrator\Desktop\" & strFile
If Len(Dir(DirFile)) = 0 Then
  MsgBox "File does not exist"
Else
 On Error Resume Next
 Set WB = Workbooks.Open(DirFile)
 On Error GoTo 0
 If WB Is Nothing Then MsgBox DirFile & " is invalid", vbCritical
End If

Ramose

This method is not 100% reliable since it does not differentiate file name from a folder name. – Mauldin

@Mauldin I've gone a step to test the filename is valid. – Ramose

Won't work when file name is in unicode. check my posted answer below. – Jedda

Answer 2

I use this function to check for file existence:

Function IsFile(ByVal fName As String) As Boolean
'Returns TRUE if the provided name points to an existing file.
'Returns FALSE if not existing, or if it's a folder
    On Error Resume Next
    IsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory)
End Function

Mauldin

Since you have On Error Resume Next, after your main line I would introduce On Error GoTo 0 just to prevent the error from hanging. Anyways, I like this approach as one can check existence of a file without accidentally checking the existence of a folder. – Aeschylus

Does this handle the case where fName is neither a file nor a directory? Seems like a combination of @Ramose and iDevlop's answers would be best: IsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory) And Len(Dir(DirFile)) <> 0 – Uncontrollable

Investigating further, it appears that GetAttr(fName) will raise exception 53 - FileNotFoundException, invoking Resume Next, and IsFile will keep its prior value (False). So your function does handle all cases. I probably won't test it, but it may also run faster than brettdj's since it doesn't invoke Dir, which looks suspiciously like the system command (?). From my C/C++ experience, invoking a system command takes around 1 second, and maybe another second to resume the executable. Excellent! I up-voted your answer previously. I don't see why this is not the top vote getter. – Uncontrollable

@Aeschylus I'll admit I'm not completely sure I understand VBA's On Error style error handling, simple as it appears to be. I think upon exiting the function, the err object is destroyed, cleared, or whatever the correct VBA terminology is. In addition, I'm thinking any on error statements in the function "go out of scope" upon exit. If that's true, then an On Error GoTo -1 statement just before End Function would have no effect. I have been worrying about these very issues, so please correct me if I'm wrong. – Uncontrollable

@Uncontrollable I could have put on error goto 0 at the end to cancel error handling, but that has no effect since current routine error handling does not affect callers (but could affect called subs - none here) – Mauldin

@Uncontrollable - I use this version (with On Error GoTo 0 at the end) myself in my projects. I have tested both approaches (with the line and without). From my testing the error object is not destroyed upon the end of this function if you don't instruct it to be destroyed, because this function is supposed to be called from another sub/function. So when this function ends, the code continues in the caller function, and you still have the error object there. To clear it one could also use something like If Err.Number = ... Then Err.Clear if the specific error number is known. – Aeschylus

@Mauldin - I have tested the case once again. if an error is received in this function, when we are back to the caller function, we still have that error. (Error 53: File not found) – Aeschylus

@Aeschylus then you can add an err.clear before the end function. Personnally I always clear err before code area where I'll really handle them. – Mauldin

@ZygD, Idevlop. Sorry for the slow response, and thanks for helping me get a handle on this. I did a little of my own "what if." The error object was reset upon return from the error handling block without any explicit reset instruction. But without an On Error GoTo 0 or On Error GoTo -1 or Err.clear before exiting the main body of the called function, the Err object was not reset. There is a lot of confusion on this topic, not only my own. It seems the affect of On_Error GoTo -1 differs depending on location: in the main body or in the error handler). – Uncontrollable

This approach fails with multiple \ characters. Assuming that the function returns True for a file at C:\Test\Test.xlsx, then the function will also return True for C:\Test\\Test.xlsx or C:\Test\\\\Test.xlsx. It seems that the GetAttr function ignores doubled backslashes. This is a behaviour that I consider dangerous. – Interfluve

Interestingly, the PathFileExistsA API from the Shlwapi.dll also does the same logic. Path separators like \\, \/ or even \/\/ are all considered valid. – Interfluve

This solution failed to me giving False when testing "K:\path\filename.ext" and True with the equivalent "\\servername\folderK\path\filename.ext" on a PC (but not on another to be honest), where they both point to the same file and must be true (tested opening "K:\path" with Explorer and seeing the file there) Dir(FileFullPath) <> "" gives True to both instead so I used this solution. – Aba

Won't work when file name is in unicode. check my posted answer below. – Jedda

This one has the added advantage that it won't break your code if you're already looping through a folder using Dir(). – Hanoi

Answer 3

For checking existence one can also use (works for both, files and folders):

Not Dir(DirFile, vbDirectory) = vbNullString

_{The result is True if a file or a directory exists.}

Example:

If Not Dir("C:\Temp\test.xlsx", vbDirectory) = vbNullString Then
    MsgBox "exists"
Else
    MsgBox "does not exist"
End If

Aeschylus

Won't work when file name is in unicode. check my posted answer below. – Jedda

Answer 4

A way that is clean and short:

Public Function IsFile(s)
    IsFile = CreateObject("Scripting.FileSystemObject").FileExists(s)
End Function

Fleda

it returns True even if "s" is a folder name :-( – Aba

Answer 5

Function FileExists(ByRef strFileName As String) As Boolean
' TRUE if the argument is an existing file
' works with Unicode file names
    On Error Resume Next
    Dim objFSO As Object
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    FileExists = objFSO.FileExists(strFileName)
    On Error GoTo 0
End Function

To make the function run faster, objFSO can be made a global variable and the code can be modified and saved in a module like this:

Option Explicit
Dim objFSO As Object
Function FileExists(ByRef strFileName As String) As Boolean
' TRUE if the argument is an existing file
' works with Unicode file names
    On Error Resume Next
    If objFSO Is Nothing Then Set objFSO = CreateObject("Scripting.FileSystemObject")
    FileExists = objFSO.FileExists(strFileName)
    On Error GoTo 0
End Function

For strFileName to be a unicode string, you can, for example, either get it from a cell value or define it in a special way, as Excel's VBE doesn't save string constants in Unicode. VBE does support Unicode strings already saved in string variables. You're gonna have to look this up for further details.

Hope this helps somebody ^_^

Jedda

Answer 6

Speed of Various FileExists Methods

I needed to check file existence for many of my projects, so I wanted to determine the fastest option. I used the micro timer code (see Benchmarking VBA Code) to run the File Exist functions below the table against a local folder with 2865 files to see which was faster. Winner used GetAttr. Using FSO method for Test 2 was a bit faster with the object defined as a global than not, but not as fast as the GetAttr method.

------------------------------------------------------
% of Fastest                Seconds       Name
------------------------------------------------------
100.00000000000%             0.0237387    Test 1 - GetAttr
7628.42784145720%            1.8108896    Test 2 - FSO (Obj Global)
8360.93687615602%            2.0522254    Test 2 - FSO (Obj in Function)
911.27399562739%             0.2163246    Test 3 - Dir
969.96844814586%             0.2302579    Test 4 - Dir$
969.75108156723%             0.2302063    Test 5 - VBA.Dir
933.82240813524%             0.2216773    Test 6 - VBA.Dir$
7810.66612746275%            1.8541506    Test 7 - Script.FSO

Function FileExistsGA(ByVal FileSpec As String) As Boolean
  ' Karl Peterson MS VB MVP
  Dim Attr As Long
  ' Guard against bad FileSpec by ignoring errors
  ' retrieving its attributes.
  On Error Resume Next
  Attr = GetAttr(FileSpec)
  If Err.Number = 0 Then
    ' No error, so something was found.
    ' If Directory attribute set, then not a file.
    FileExistsGA = Not ((Attr And vbDirectory) = vbDirectory)
  End If
End Function

Function FSOFileExists(sFilePathNameExt As String) As Boolean
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    FSOFileExists = fso.FileExists(sFilePathNameExt)
    Set fso = Nothing
End Function

Function FileExistsDir(sFilePathNameExt As String) As Boolean
    If Len(Dir(sFilePathNameExt)) > 0 Then FileExistsDir = True
End Function

Function FileExistsDirDollar(sFilePathNameExt As String) As Boolean
    If Len(Dir$(sFilePathNameExt)) > 0 Then FileExistsDirDollar = True
End Function

Function FileExistsVBADirDollar(sFilePathNameExt As String) As Boolean
    If Len(VBA.Dir$(sFilePathNameExt)) > 0 Then FileExistsVBADirDollar = True
End Function

Function FileExistsVBADir(sFilePathNameExt As String) As Boolean
    If Len(VBA.Dir(sFilePathNameExt)) > 0 Then FileExistsVBADir = True
End Function

Public Function IsFileSFSO(s)
    IsFileSFSO = CreateObject("Scripting.FileSystemObject").FileExists(s)
End Function

I realize that this does not fully answer the OP, but is provides information on which of the answers provided seems to be most efficient.

Bloated

Answer 7

Maybe it caused by Filename variable

File = TextBox1.Value

It should be

Filename = TextBox1.Value

Thelma

This is not a bad answer. Using "File" or any other keyword as a variablename has caused trouble for a lot of people. Even though this is not a solution to the problem it is still a good point. – Cobwebby

Answer 8

I'll throw this out there and then duck. The usual reason to check if a file exists is to avoid an error when attempting to open it. How about using the error handler to deal with that:

Function openFileTest(filePathName As String, ByRef wkBook As Workbook, _
                      errorHandlingMethod As Long) As Boolean
'Returns True if filePathName is successfully opened,
'        False otherwise.
   Dim errorNum As Long

'***************************************************************************
'  Open the file or determine that it doesn't exist.
   On Error Resume Next:
   Set wkBook = Workbooks.Open(fileName:=filePathName)
   If Err.Number <> 0 Then
      errorNum = Err.Number
      'Error while attempting to open the file. Maybe it doesn't exist?
      If Err.Number = 1004 Then
'***************************************************************************
      'File doesn't exist.
         'Better clear the error and point to the error handler before moving on.
         Err.Clear
         On Error GoTo OPENFILETEST_FAIL:
         '[Clever code here to cope with non-existant file]
         '...
         'If the problem could not be resolved, invoke the error handler.
         Err.Raise errorNum
      Else
         'No idea what the error is, but it's not due to a non-existant file
         'Invoke the error handler.
         Err.Clear
         On Error GoTo OPENFILETEST_FAIL:
         Err.Raise errorNum
      End If
   End If

   'Either the file was successfully opened or the problem was resolved.
   openFileTest = True
   Exit Function

OPENFILETEST_FAIL:
   errorNum = Err.Number
   'Presumabley the problem is not a non-existant file, so it's
   'some other error. Not sure what this would be, so...
   If errorHandlingMethod < 2 Then
      'The easy out is to clear the error, reset to the default error handler,
      'and raise the error number again.
      'This will immediately cause the code to terminate with VBA's standard
      'run time error Message box:
      errorNum = Err.Number
      Err.Clear
      On Error GoTo 0
      Err.Raise errorNum
      Exit Function

   ElseIf errorHandlingMethod = 2 Then
      'Easier debugging, generate a more informative message box, then terminate:
      MsgBox "" _
           & "Error while opening workbook." _
           & "PathName: " & filePathName & vbCrLf _
           & "Error " & errorNum & ": " & Err.Description & vbCrLf _
           , vbExclamation _
           , "Failure in function OpenFile(), IO Module"
      End

   Else
      'The calling function is ok with a false result. That is the point
      'of returning a boolean, after all.
      openFileTest = False
      Exit Function
   End If

End Function 'openFileTest()

Uncontrollable

Answer 9

Here is my updated code. Checks to see if version exists before saving and saves as the next available version number.

Sub SaveNewVersion()
    Dim fileName As String, index As Long, ext As String
    arr = Split(ActiveWorkbook.Name, ".")
    ext = arr(UBound(arr))

    fileName = ActiveWorkbook.FullName

    If InStr(ActiveWorkbook.Name, "_v") = 0 Then
        fileName = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) & "_v1." & ext
    End If

   Do Until Len(Dir(fileName)) = 0

        index = CInt(Split(Right(fileName, Len(fileName) - InStr(fileName, "_v") - 1), ".")(0))
        index = index + 1
        fileName = Left(fileName, InStr(fileName, "_v") - 1) & "_v" & index & "." & ext

    'Debug.Print fileName
   Loop

    ActiveWorkbook.SaveAs (fileName)
End Sub

Grudge

Answer 10

You should set a condition loop to check the TextBox1 value.

If TextBox1.value = "" then
   MsgBox "The file not exist" 
   Exit sub 'exit the macro
End If

Hope it help you.

Caitlyncaitrin