VBA check if file exists
Solution 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
Solution 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
Solution 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
Solution 4
A way that is clean and short:
Public Function IsFile(s)
IsFile = CreateObject("Scripting.FileSystemObject").FileExists(s)
End Function
Solution 5
Maybe it caused by Filename variable
File = TextBox1.Value
It should be
Filename = TextBox1.Value
Josephine Bautista
Updated on July 09, 2022Comments
-
Josephine Bautista almost 2 years
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
-
AnyOneElse over 9 yearsThis 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.
-
lanartri over 8 yearsThis method is not 100% reliable since it does not differentiate file name from a folder name.
-
ZygD over 8 yearsSince you have
On Error Resume Next
, after your main line I would introduceOn 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. -
riderBill over 8 yearsDoes this handle the case where fName is neither a file nor a directory? Seems like a combination of @brettdj and iDevlop's answers would be best: IsFile = ((GetAttr(fName) And vbDirectory) <> vbDirectory) And Len(Dir(DirFile)) <> 0
-
riderBill over 8 yearsInvestigating 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.
-
riderBill over 8 years@ZygD 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.
-
lanartri over 8 years@riderBill 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) -
ZygD over 8 years@riderBill - 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 likeIf Err.Number = ... Then Err.Clear
if the specific error number is known. -
ZygD over 8 years@iDevlop - 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)
-
lanartri over 8 years@ZygD then you can add an
err.clear
before theend function
. Personnally I always clear err before code area where I'll really handle them. -
riderBill over 8 years@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).
-
brettdj about 8 years@iDevlop I've gone a step to test the filename is valid.
-
Cristian Buse almost 3 yearsThis approach fails with multiple
\
characters. Assuming that the function returnsTrue
for a file atC:\Test\Test.xlsx
, then the function will also returnTrue
forC:\Test\\Test.xlsx
orC:\Test\\\\Test.xlsx
. It seems that theGetAttr
function ignores doubled backslashes. This is a behaviour that I consider dangerous. -
Cristian Buse almost 3 yearsInterestingly, the
PathFileExistsA
API from the Shlwapi.dll also does the same logic. Path separators like\\
,\/
or even\/\/
are all considered valid. -
6diegodiego9 over 2 yearsThis 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.
-
6diegodiego9 over 2 yearsit returns True even if "s" is a folder name :-(
-
Jalal over 2 yearsWon't work when file name is in unicode. check my posted answer below.
-
Jalal over 2 yearsWon't work when file name is in unicode. check my posted answer below.
-
Jalal over 2 yearsWon't work when file name is in unicode. check my posted answer below.