Macro Help Needed

[K]

The below macro look at file name in cell A1 and then find it in
SubFolders and then opens it. What and where I should add the code
line that if macro don’t find file in SubFolders then MsgBox should
come up saying that "File Not Found".

*************************************************
Sub OpenFilefromSubFolders()
Dim fs, f, sf, f1, fle
Folder = "C:\Documents\Record"
VRNumber = Range("A1").Value
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(Folder)
Set sf = f.subfolders
If Len(VRNumber) > 0 And Len(VRNumber) < 6 Then
MsgBox "PUT COMPLETE 6 DIGITS OF TRANSACTION", vbCritical, "INCORRECT"
ElseIf Range("A1").Value = "" Then
MsgBox "ENTER TRANSACTION NO.", vbCritical, "INCORRECT"
ElseIf IsNumeric(VRNumber) And (Range("A1").Value <> "") And _
Len(VRNumber) = 6 Then
For Each f1 In sf
fle = Dir(f1 & "\*" & VRNumber & "*.xlsx")
Do While fle <> ""
Workbooks.Open Filename:=f1 & "\" & fle
fle = Dir()
Loop
Next f1
End If
End Sub
*************************************************

I did try putting code line (see below) but its not working as I still
get MsgBox even file is found and open. Please can any friend can
help.

If fle = "" Then
MsgBox "File Not Found"
End If

I put above code below the line * fle = Dir(f1 & "\*" & VRNumber &
"*.xlsx" *

 

[Jacob Skaria]

Within the loop place a counter and increment everytime you fine one workbook.
Outside the loop check if the counter is still 0 and display message "No
workbooks found"

If this post is helpful click Yes

 

[Stefi]

See FileExists method in VBA Help!

Regards,
Stefi

„K†ezt írta:

 

[K]

I did try that but not really helpful

 

[Jacob Skaria]

*************************************************
Sub OpenFilefromSubFolders()
Dim fs, f, sf, f1, fle
Folder = "C:\Documents\Record"
VRNumber = Range("A1").Value
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(Folder)
Set sf = f.subfolders
If Len(VRNumber) > 0 And Len(VRNumber) < 6 Then
MsgBox "PUT COMPLETE 6 DIGITS OF TRANSACTION", vbCritical, "INCORRECT"
ElseIf Range("A1").Value = "" Then
MsgBox "ENTER TRANSACTION NO.", vbCritical, "INCORRECT"
ElseIf IsNumeric(VRNumber) And (Range("A1").Value <> "") And _
Len(VRNumber) = 6 Then
intCounter = 0
For Each f1 In sf
fle = Dir(f1 & "\*" & VRNumber & "*.xlsx")
Do While fle <> ""
Workbooks.Open Filename:=f1 & "\" & fle
intCounter = intCounter + 1
fle = Dir()
Loop
Next f1
If intCounter = 0 then Msgbox "No workbooks found"
End If
End Sub
*************************************************