VBA function for check if file with name that contain some word exists

[MMasiarek]

Hello everyone,

I'm trying to write VBA function to check if file with name that contains some string exists.
Currently I have a code like that:

Function FileExists(path As String)

    Dim fso_obj As Object
    Dim full_path As String
    
    
    Set fso_obj = CreateObject("Scripting.FileSystemObject")
    FileExists = fso_obj.FileExists(path)
    
End Function

In sheet I'm using function like that: =IF(FileExists($B$3 &"\"& $B$2 &"\"& B4 &".txt");1;2)


I tried to upgrade my function by changing to to this version:

Function FileExists(path As String, folder As String, file_name As String)

    Dim fso_obj As Object
    Dim full_path As String

    full_path = path & "\" & folder & "*\*" & file & "*.*"
    
    Set fso_obj = CreateObject("Scripting.FileSystemObject")
    FileExists = fso_obj.FileExists(full_path)
    
End Function

And in sheet I used it like that: =IF(FileExists($B$3;"\"&$B$2&"\";B4 &".tx");1;2)

The new version of my function is returning FALSE.
Table with initial data looks like that:

Folder	123​
Path	C:\Users\mmasiarek\Desktop	Exists
File#1	1​
File#2	2​
File#3	3​

What I'm trying to get is to have function which will return TRUE(in third column) if in the folder that contains '123' in its name is a file that contains string from second column.

Could anyone asists me with that issue?

 

[Micron]

Not clear what you're asking.
You want the function to return true if the there is a folder with that name that contains a file with that name?
You want it to return true if there is a folder whose name contains that string and if there is a file name that contains that string?
So in the latter case, True if folder name "Testing" contains "est" and file name "123" contains "2".
In former case, True if folder name "123" exists and contains a file named "1".

Regardless, I think the formula is incorrect for the data you posted. The cell references seem out of place for the order that the function is expecting but I'm getting strange results so not sure. Note that I had to change the separators in the formula below because my system uses comma not semicolon.
I'm thinking it should be FileExists($B$2&"\"&$B$1&"\",B3 &".txt")
Also, you say you want True (and I supposed False if not True) yet your formula is choosing between 1 and 2. You can eliminate the IF part and just have the function return True/False or 1/2 or -1/0 which is the numeric values for T and F.

 

[Micron]

I just noticed that you're inserting backslashes in both the formula
IF(FileExists($B$3;"\"&$B$2&"\";B4 &".tx");1;2)

and in the sub
full_path = path & "\" & folder & "*\*" & file & "*.*"

and you're using an undeclared variable in the sub (file) which probably should be "file_name".

so I doubt you'll ever get the function to return True. I would put the \ in the sheet cells and drop them from the formula and sub, or at least take them out of the formula and leave in the sub if not putting them in the cells.

 

[Micron]

Here's how I would probably approach this (remember, I must use commas):
=FileExists($B$2,$B$1,B3)

Function FileExists(path As String, folder As String, file_name As String) As Integer

Dim fso_obj As Object
Dim full_path As String

full_path = path & "\" & folder & "\" & file_name
''Debug.Print full_path

Set fso_obj = CreateObject("Scripting.FileSystemObject")
If fso_obj.FileExists(full_path) Then
   FileExists = 1
Else
   FileExists = 2
End If
    
End Function

Folder	Excel
Path	C:\Users\Micron\Documents	1​
File#1	MMasiarek.xlsm	2​
File#2	cat	2​
File#3	bird	2​

Note that since your original formula was returning 1 or 2, I modified the sub to return 1 (T) or 2 (F).

 

[MMasiarek]

Here's how I would probably approach this (remember, I must use commas):
=FileExists($B$2,$B$1,B3)

Function FileExists(path As String, folder As String, file_name As String) As Integer

Dim fso_obj As Object
Dim full_path As String

full_path = path & "\" & folder & "\" & file_name
''Debug.Print full_path

Set fso_obj = CreateObject("Scripting.FileSystemObject")
If fso_obj.FileExists(full_path) Then
   FileExists = 1
Else
   FileExists = 2
End If
   
End Function

Folder	Excel
Path	C:\Users\Micron\Documents	1​
File#1	MMasiarek.xlsm	2​
File#2	cat	2​
File#3	bird	2​

Note that since your original formula was returning 1 or 2, I modified the sub to return 1 (T) or 2 (F).

Thanks for your reply.
Yes, maybe I wasn't clear enough with what I want to get.
So, I have folders named like: 1_something, 2_something, 3_something. Number values in folder names are unique.
All folders are in one general folder, lets say Documents.
In each folder will be files with uniqe names and with different extensions txt, pdf, xls etc.

So when I put 1 in cell B1 and sample_file in cell B3, formula should find folder with number 1 in its name in Documents and then check if there is a file with 'sample_file' in its name.
Thats why I used * in VBA code next to backslashes

 

[Micron]

So now it seems that if the cell value is 1, look for folder 1_something and loop over a set of files in that folder and find any with "sample_file" in its name. It would then return True where it finds 1_something\sample_file.txt or 1_something\sample_file.xlsx or 1_something\sample_file.doc and so on. Or will there only ever be 1 file that satisfies the name? I ask because:
- your code will only find the first file that matches
- now that you've mentioned different extensions, your code will only find txt because of the formula
IF(FileExists($B$3 &"\"& $B$2 &"\"& B4 &".txt");1;2)

 

[MMasiarek]

So now it seems that if the cell value is 1, look for folder 1_something and loop over a set of files in that folder and find any with "sample_file" in its name. It would then return True where it finds 1_something\sample_file.txt or 1_something\sample_file.xlsx or 1_something\sample_file.doc and so on. Or will there only ever be 1 file that satisfies the name? I ask because:
- your code will only find the first file that matches
- now that you've mentioned different extensions, your code will only find txt because of the formula
IF(FileExists($B$3 &"\"& $B$2 &"\"& B4 &".txt");1;2)

There can be more files which names will contain 'sample_file'.
It can be: sample_file_1.txt, sample_file_2.pdf and sample_file_3.xls.
Extensions are not important in this case. If at least one file with 'sample_file' in its name will be found function should return TRUE or 1 or something similar.

 

[Micron]

then with this data and a folder that contains "1" in its name (1_something)
and is under that path and contains a file which contains the word "sample" in the name (sample_file.txt)
the function returns True or False (it's return type is Boolean but could be any other type you want).
Formula begins in C3 as =FileExists($B$2,$B$1,B3)

Folder	1​
Path	C:\Users\Micron\Documents
File#1	sample	TRUE​
File#2	cat	FALSE​

Function FileExists(strPath As String, strFldr As String, strName As String) As Boolean

Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("C:\Users\Micron\Documents\")

If strName = "" Then Exit Function
For Each sfldr In fldr.SubFolders
   If InStr(sfldr.Name, strFldr) > 0 Then
      For Each File In sfldr.Files
         If InStr(File.Name, strName) > 0 Then FileExists = True
      Next
   End If
Next

End Function

 

[MMasiarek]

then with this data and a folder that contains "1" in its name (1_something)
and is under that path and contains a file which contains the word "sample" in the name (sample_file.txt)
the function returns True or False (it's return type is Boolean but could be any other type you want).
Formula begins in C3 as =FileExists($B$2,$B$1,B3)

Folder	1​
Path	C:\Users\Micron\Documents
File#1	sample	TRUE​
File#2	cat	FALSE​

Function FileExists(strPath As String, strFldr As String, strName As String) As Boolean

Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("C:\Users\Micron\Documents\")

If strName = "" Then Exit Function
For Each sfldr In fldr.SubFolders
   If InStr(sfldr.Name, strFldr) > 0 Then
      For Each File In sfldr.Files
         If InStr(File.Name, strName) > 0 Then FileExists = True
      Next
   End If
Next

End Function

Great, now it works perfectly.
Thank you for your help

 

[Micron]

You're welcome. Glad to know it works for you.