Test if file exists using VBA or Excel without "DIR"

Question

I am stepping into a directory of folders and looking into the folders to get particular data each time I find a specific file.

If I go to open the file I am looking for in a folder that it is not in, I get a debug error. So clearly I need to test the directory to see if the file is there and not try to open it if it is not (that is fine, not all the folders have the subject file).

The DIR function is the hands on favorite to test a file's presence, but because I am using the DIR function to step through the folders, my testing has shown that I cannot use the DIR to test for a specific file in the middle of my code because VBA gets confused where I was in the stepping through the folders.

How can I test for the existence of a known filename without using DIR? I tried the function I found:

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

Unfortunately, I have not figured out how to actually write this into the SUB that I am building. The sample I found (and others similar), but the usually do not make clear what is needed for an aspiring novice. In this case don't know what would go in LEN and DIR and why the fullfilename would be needed in what looks like a DIM statement (because of the As Boolean). I also find that if I try to put this function within my Subroutine, VBA is just not happy.

What can I try that will work? I just want an IF statement around a function that will tell me if the file exists or not.

As long as you don't need it to work on Mac you can use a Scripting.FileSystemObject msdn.microsoft.com/en-us/library/x23stk5t(v=vs.84).aspx — Tim Williams
– Tim Williams, Commented Mar 1, 2016 at 1:28
Tim, thanks for your reference. I pulled what was there but did NOT get expected results, so I enhanced the script a little bit to try to help: Sub FileExistence()Dim fso Dim Folder As String Folder = Dir("c:\users\", vbDirectory) MsgBox Folder Set fso = CreateObject("Scripting.FileSystemObject") If (fso.FileExists("c:\users\")) Then MsgBox " exists." Else MsgBox " doesn't exist." End If End Sub — Excelling
– Excelling, Commented Mar 1, 2016 at 14:13
Sorry, new to this. Tim, thanks for the reference, but it does not give me the expected results. I added DIR & MSGBOX to confirm the directory exists: Sub Test_File_Existence() Dim fso Dim Folder As String Folder = Dir("c:\users\", vbDirectory) MsgBox Folder Set fso = CreateObject("Scripting.FileSystemObject") If (fso.FileExists("c:\users\")) Then MsgBox "exists." Else MsgBox " doesn't exist." End If End Sub I get different results. The DIR gives a dot, the first directory in users; the IF has msgbox say the file does not exist. FSO fails. ??? — Excelling
– Excelling, Commented Mar 1, 2016 at 14:25
Usually best to add any new code to your question. Use the {} button to format it so it's readable. — Tim Williams
– Tim Williams, Commented Mar 1, 2016 at 15:43

Gary's Student · Accepted Answer · 2016-03-01 03:12:11Z

Say we are looking for a file containing "happiness" within folder C:\TestFolder or a sub-folder of that folder. We want to know:

the name of the file
its location

We also want to be informed if "happiness" cannot be found. Consider:

Dim FileIsThere As Boolean

Sub MAIN()

   Dim FileSystem As Object
   Dim TopFolder As String

   TopFolder = "C:\TestFolder"
   FileIsThere = False
   Set FileSystem = CreateObject("Scripting.FileSystemObject")

   Process FileSystem.GetFolder(TopFolder)

   If Not FileIsThere Then
        MsgBox "happiness cannot be obtained, settle for the absence of misery"
   End If
 End Sub

 Sub Process(Folder)
    Dim SubFolder
    For Each SubFolder In Folder.SubFolders
      Process SubFolder
    Next

    Dim File
    For Each File In Folder.Files
        If InStr(1, File.Path, "happiness") > 0 Then
            tpath = Left(File.Path, Len(File.Path) - Len(File.Name))
            MsgBox tpath & "  contains  " & File.Name
            FileIsThere = True
        End If
    Next
End Sub

Collectives™ on Stack Overflow

Test if file exists using VBA or Excel without "DIR"

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related