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

Answer 1

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

Answer 2

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

Answer 3

A way that is clean and short:

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

Answer 4

Maybe it caused by Filename variable

File = TextBox1.Value

It should be

Filename = TextBox1.Value

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 ^_^

Answer 6

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()

Answer 7

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