Checking if Files Exist

[Balfin]

Hi,

Is there a way to check to see if there are any .xls files, open or closed, in a given directory?

Thanks.

 

[Reid]

How about this?

Sub FindXLS()

    Dim FS As FileSearch
    Dim FilePath As String, FileSpec As String

    FilePath = "C:\Test\" '<----- Edit as needed
    FileSpec = "*.xls"

    Set FS = Application.FileSearch
    With FS
        .LookIn = FilePath
        .FileName = FileSpec
        .Execute
        If .FoundFiles.Count = 0 Then
            MsgBox "No Files Found"
            Exit Sub
        Else
            'Your code here
        End If
    End With
End Sub

 

[Richard Schollar]

If in 2007 or above, you won't be able to make use of FileSearch, but you could use Dir:

Sub Test()
Dim strPath as String, strFilter as String, strResult as String
strPath = "C:\"   'amend as appropriate
strFilter = "*.xls"   'amend as appropriate
strResult = strPath & "\" & strFilter
If strResult<>"" Then
   'xls file found!!!   COde as appropriate
End If
End Sub

 

[Balfin]

Thanks! That worked great!

What does ".Execute" do? It was the only line I was missing and I needed it in there to work.

 

[Richard Schollar]

FileSearch doesn't return any results until you use the execute command - you can think of it as akin to having the car ready to go but not having turned the key in the ignition so the car doesn't go anywhere.

 

[Norie]

Richard

Is your code perhaps missing a little something?

Like Dir?

 

[Richard Schollar]

Richard

Is your code perhaps missing a little something?

Like Dir?

Ah you mean the Dir() in invisible ink???

Sub Test()
Dim strPath as String, strFilter as String, strResult as String
strPath = "C:\"   'amend as appropriate
strFilter = "*.xls"   'amend as appropriate
strResult = Dir(strPath & "\" & strFilter)
If strResult<>"" Then
   'xls file found!!!   COde as appropriate
End If
End Sub

Thank you Norie

 

[Reid]

So the Filesearch method is no more? That stinks. I guess I will need to get more familiar with DIR. I wonder what the reasoning was on that decision.

 

[Richard Schollar]

So the Filesearch method is no more? That stinks. I guess I will need to get more familiar with DIR. I wonder what the reasoning was on that decision.

Yep - I've heard rumours that FileSearch is buggy but then I've never actually seen anyone back that up with evidence. I used to use it all the time in my code (at work they are still on 2003 and hopefully won't upgrade for a while) and don't relish the prospect of modifying all my existing code...

 

[Norie]

Richard

I don't have any actual evidence but I'm sure in the past I've experienced difficulty with FileSearch.

Admittedly though it was normally my problem.

But I'm pretty sure a few times it was VBA/Excel causing the problem, honest.