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    lt  gt     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

User · Answer

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  amp       amp  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  amp       amp  s fileName   End Function

User · Answer

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   lt  gt     Then     MsgBox  File exists   Else     MsgBox  File doesn t exist   End If  End Sub

User · Answer

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    gt  0 End Function

User · Answer

Function FileExists fullFileName As String  As Boolean     FileExists   VBA Len VBA Dir fullFileName    gt  0 End Function

User · Answer

Function FileExists fullFileName As String  As Boolean     FileExists   VBA Len VBA Dir fullFileName    gt  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    gt  0   End If End Function

User · Answer

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   lt  gt     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

User · Answer

based on other answers here I d like to share my one-liners that should work for dirs and files    Len Dir path    gt  0 or Or Len Dir path  vbDirectory    gt  0   version 1 -      lt  gt     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    gt  0 Or Len Dir path  vbDirectory    gt  0 End Function

User · Answer

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 amp 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

User · Answer

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  lt  gt     Then          If IsMissing Directory  Or Directory   False Then              File Exists    Dir  sPathName   lt  gt              Else              File Exists    Dir  sPathName  vbDirectory   lt  gt              End If      End If End Function

[excel] Check if the file exists using VBA

Examples related to excel

Examples related to vba