Check if the file exists using VBA

小开

最佳答案

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

小开

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

小开

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

小开

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

小开

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

小开

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

小开

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

小开

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

小开

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