Check if file exists without opening it

[Jaymond Flurrie]

I have a code that opens and closes a workbook to check if it exists. The problem is that opening that workbook takes quite a while since it's size is over 10MB. I'm pretty sure we have a smarter way to do this, for example some loop to go thru filenames in directory and saying that "yep, it's there" or "nope, not there".

So something like

Sub CheckForFile()
    Dim strFile As string
    Dim strFolder As string
    Dim bFound As Boolean

    bFound = FileExists(strFile, strFolder)

    If bFound Then
        Debug.Print "yep, it's there"
    Else
        Debug.Print "nope, not there"
    End If
End Sub

Function FileExists(strFile As String, strFolder As String)
    Dim FileName As File 'What's the correct objects here?
    Dim Folder As FileFolder 'What's the correct objects here?
    FileName = file(strFile) 'How does this one go?
    Folder = folder(strFile) 'How does this one go?
    
    For Each FileName In Folder 
        If FileName.Name = strFile Then
            FileExists = True
            Exit Function
        End If
    Next File
    FileExists = False
End Function

I would imagine that for someone handling files with VBA often this is a piece of cake.

 

[VoG]

Try

Function FileExists(strFile As String, strFolder As String) As Boolean
FileExists = Dir(strFolder & "\" & strFile) <> ""
End Function

 

[Jaymond Flurrie]

Try

Function FileExists(strFile As String, strFolder As String) As Boolean
FileExists = Dir(strFolder & "\" & strFile) <> ""
End Function

Kind of depressing when someone pulls it off with one line of code. But thanks anyway, that's pretty much exactly what I was looking for!