Fastest way to check if file exists

[bradyboyy88]

I have 1000s of files to sort through in a directory and I was wondering if anyone has experience on which is the best way to check if a file exists. From my research the two approaches are the Dir function and the other is to createa a filesystemobject and use FileExists function. Does anyone know which is naturally faster in their search function?

Thanks

 

[bradyboyy88]

Scratch both methods. They are insanely slow when you are looking in the 1000s. Does anyone have a faster method?

 

[Fluff]

How about

Sub bradyboyy88()
   Dim Pth As String
   Dim Fname As String
   
   Pth = "C:\MrExcel\"
   Fname = "RWC 2019.xlsx"
   Debug.Print FileExists(Pth, Fname)
End Sub

Function FileExists(Pth As String, Fname As String) As Boolean
   FileExists = Dir(Pth & Fname) <> ""
End Function

 

[bradyboyy88]

How about

Sub bradyboyy88()
   Dim Pth As String
   Dim Fname As String
   
   Pth = "C:\MrExcel\"
   Fname = "RWC 2019.xlsx"
   Debug.Print FileExists(Pth, Fname)
End Sub

Function FileExists(Pth As String, Fname As String) As Boolean
   FileExists = Dir(Pth & Fname) <> ""
End Function

Thanks for the reply fluff but Dir is insanely slow similar to the file system object FileExist function . I am curious if there is any other methods.

 

[Fluff]

What are you trying to do?

 

[Fluff]

Just run a quick test & looping through 1022 filenames in Col A & putting True or false in colB took less then 0.2seconds

 

[bradyboyy88]

Basically we have a ado recordset with a list of directories then we scroll through the recordset to see if that directory/file truly exists. The issue I am finding is these files are not local drives but instead network drives so they are a bit more time consuming to go through. I did not know of any other way to account for that then to find the quickest method possible.

 

[Fluff]

OK, I know nothing about ado recordsets as I've never used them.

 

[Fluff]

Not sure if this will help but have a look here
https://www.mrexcel.com/forum/excel...-rather-than-fso-name-like-speed-up-code.html

 

[bradyboyy88]

Not sure if this will help but have a look here
https://www.mrexcel.com/forum/excel...-rather-than-fso-name-like-speed-up-code.html

Great find. Yea I am not to worried about the speed of sorting through the recordset but more so the DIR function and it looks like this post is just what I am loking for. IL give it a try !