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    amp  File If Dir DirFile       Then   MsgBox  File does not exist  Else     Workbooks Open Filename  DirFile End If

User · Answer

A way that is clean and short   Public Function IsFile s      IsFile   CreateObject  Scripting FileSystemObject   FileExists s  End Function

User · Answer

Maybe it caused by Filename variable  File   TextBox1 Value   It should be  Filename   TextBox1 Value

User · Answer

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

User · Answer

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  amp       amp  Left ActiveWorkbook Name  InStr ActiveWorkbook Name       - 1   amp    v1    amp  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   amp    v   amp  index  amp       amp  ext       Debug Print fileName    Loop      ActiveWorkbook SaveAs  fileName  End Sub

User · Answer

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  lt  gt  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  lt  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                  amp   Error while opening workbook                 amp   PathName     amp  filePathName  amp  vbCrLf               amp   Error    amp  errorNum  amp        amp  Err Description  amp  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

User · Answer

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   lt  gt  vbDirectory  End Function

User · Answer

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

User · Answer

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    amp  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  amp    is invalid   vbCritical End If

[excel] VBA check if file exists

Examples related to excel

Examples related to vba

Examples related to file

Examples related to file-exists