Tuesday, March 10, 2009

Excel VBA: Determining Whether A File Exists Or Not

There are several ways to check whether a file exists. By using Microsoft Excel VBA statements and functions, or by using FileSystemObject (Microsoft Scripting Library). The following function returns True if a particular file exist, and return False if file does'nt exist. This function uses Dir function to check whether a file exists or not.

Function isFileExist(ByVal fname As String) As Boolean
 isFileExist = False
 If Dir(fname) <> "" Then isFileExist = True
End Function

Here's example how to use the function above:

Sub FunctionTest()
 If isFileExist("D:\SomeFile.txt") = False Then
     MsgBox "File not exists."
 Else
     MsgBox "File already exist."
 End If
End Sub

The next function do exactly as previous function, but this function uses FileSystemObject to check whether a file exist:

Function isFileExist2(ByVal fname As String) As Boolean
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    isFileExist2 = fs.FileExists(fname)
End Function
FIN.

Related posts:

---
If you like posts in this blog, you can to support me :)

2 comments:

  1. Here is a different approach basically the same though!

    http://vbaexcel.eu/vba-macro-code/determine-if-file-or-directory-exists

    ReplyDelete
  2. I am not a very expert in VBA but i have a little knowledge about the same
    and i see that this is one of best and easiest way to find the file if its existing or not
    good...

    ReplyDelete