I have macro set on a button. (see below)

Sub OpenFile()
Folder = "C:\My Document\Records"
FileName = Range("U21").Value
If IsNumeric(FileName) And (Range("U21").Value < "") And _
Len(FileName) = 6 Then

fName = Dir(Folder & "\*" & FileName & "*.xlsx")
If fName = "" Then
MsgBox "Record *" & FileName & "* DONT EXIST" _
& vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
, vbCritical, "INCORRECT"
Else
Do While fName < ""
Workbooks.Open Filename:=Folder & "\" & fName


fName = Dir()
Loop
End If
Else
MsgBox "VIREMENT *" & FileName & "* DONT EXIST" _
& vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
, vbCritical, "INCORRECT"
End If
End Sub

at the moment above macro look at the file name in Range("U21") and
find that file in Folder "C:\My Document\Records" and then Open it.
The problem is that there are also Subfolders in Folder "Records" and
I want macro to look in all the Subfolders for the file and when file
is found then Open it. I did try changing the code line

Folder = "C:\My Document\Records"

TO

Folder = "C:\My Document\Records\*"

but it didn’t work. Please can any friend can help that what changes
I should do in above macro that it should look for file in all the
Subfolders and then Open it

I wrote this code assuming only one level of subdirectories. if you have
multiple levels of subdirectories then I will have to write the code to use
recursion.


Sub GetLogs()

Folder = "C:\My Document\Records"
Filename = Range("U21").Value
If IsNumeric(Filename) And (Range("U21").Value < "") And _
Len(Filename) = 6 Then

Set objShell = CreateObject("Shell.Application")
Set fs = CreateObject("Scripting.FileSystemObject")

Foundfile = False

Set Folder = _
fs.GetFolder(Folder)

If Folder.subfolders.Count 0 Then
For Each Fl In Folder.subfolders
fname = Dir(Fl & "\*" & Filename & "*.xlsx")
Do While fname < ""

fname = Dir()
Workbooks.Open Filename:=Fl & "\" & fname
Foundfile = True
Loop
Next Fl
End If

fname = Dir(Folder & "\*" & Filename & "*.xlsx")
Do While fname < ""
Workbooks.Open Filename:=Folder & "\" & fname
fname = Dir()
Foundfile = True
Loop
Foundfile = True

If Foundfile = False Then
MsgBox "Record *" & Filename & "* DONT EXIST" _
& vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
, vbCritical, "INCORRECT"
End If
End If
End Sub

"K" wrote:

I have macro set on a button. (see below)

Sub OpenFile()
Folder = "C:\My Document\Records"
FileName = Range("U21").Value
If IsNumeric(FileName) And (Range("U21").Value < "") And _
Len(FileName) = 6 Then

fName = Dir(Folder & "\*" & FileName & "*.xlsx")
If fName = "" Then
MsgBox "Record *" & FileName & "* DONT EXIST" _
& vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
, vbCritical, "INCORRECT"
Else
Do While fName < ""
Workbooks.Open Filename:=Folder & "\" & fName


fName = Dir()
Loop
End If
Else
MsgBox "VIREMENT *" & FileName & "* DONT EXIST" _
& vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
, vbCritical, "INCORRECT"
End If
End Sub

at the moment above macro look at the file name in Range("U21") and
find that file in Folder "C:\My Document\Records" and then Open it.
The problem is that there are also Subfolders in Folder "Records" and
I want macro to look in all the Subfolders for the file and when file
is found then Open it. I did try changing the code line

Folder = "C:\My Document\Records"

TO

Folder = "C:\My Document\Records\*"

but it didnt work. Please can any friend can help that what changes
I should do in above macro that it should look for file in all the
Subfolders and then Open it

On Mar 11, 4:07*pm, Joel wrote:
I wrote this code assuming only one level of subdirectories. *if you have
multiple levels of subdirectories then I will have to write the code to use
recursion.

Sub GetLogs()

* *Folder = "C:\My Document\Records"
* *Filename = Range("U21").Value
* *If IsNumeric(Filename) And (Range("U21").Value < "") And _
* * * Len(Filename) = 6 Then

* * * Set objShell = CreateObject("Shell.Application")
* * * Set fs = CreateObject("Scripting.FileSystemObject")

* * * Foundfile = False

* * * Set Folder = _
* * * * *fs.GetFolder(Folder)

* * * If Folder.subfolders.Count 0 Then
* * * * *For Each Fl In Folder.subfolders
* * * * * * fname = Dir(Fl & "\*" & Filename & "*.xlsx")
* * * * * * Do While fname < ""

* * * * * * * *fname = Dir()
* * * * * * * *Workbooks.Open Filename:=Fl & "\" & fname
* * * * * * * *Foundfile = True
* * * * * * Loop
* * * * *Next Fl
* * * End If

* * * fname = Dir(Folder & "\*" & Filename & "*.xlsx")
* * * Do While fname < ""
* * * * *Workbooks.Open Filename:=Folder & "\" & fname
* * * * *fname = Dir()
* * * * *Foundfile = True
* * * Loop
* * * Foundfile = True

* * *If Foundfile = False Then
* * * * MsgBox "Record **" & Filename & "* *DONT EXIST" _
* * * * & vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
* * * * , vbCritical, "INCORRECT"
* * *End If
* *End If
End Sub



"K" wrote:
I have macro set on a button. (see below)

Sub OpenFile()
Folder = "C:\My Document\Records"
FileName = Range("U21").Value
If IsNumeric(FileName) And (Range("U21").Value < "") And _
Len(FileName) = 6 Then

fName = Dir(Folder & "\*" & FileName & "*.xlsx")
If fName = "" Then
MsgBox "Record **" & FileName & "* *DONT EXIST" _
& vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
, vbCritical, "INCORRECT"
* *Else
* * * Do While fName < ""
* * * * *Workbooks.Open Filename:=Folder & "\" & fName

* * * * *fName = Dir()
* * * Loop
* *End If
Else
* *MsgBox "VIREMENT **" & FileName & "* *DONT EXIST" _
& vbNewLine & "OR" & vbNewLine & "ENTERED INCORRECT NUMBER" _
, vbCritical, "INCORRECT"
End If
End Sub

at the moment above macro look at the file name in Range("U21") and
find that file in Folder "C:\My Document\Records" and then Open it.
The problem is that there are also Subfolders in Folder "Records" *and
I want macro to look in all the Subfolders for the file and when file
is found *then Open it. *I did try changing the code line

Folder = "C:\My Document\Records"

TO

Folder = "C:\My Document\Records\*"

but it didn’t work. *Please can any friend can help that what changes
I should do in above macro that it should look for file in all the
Subfolders and then Open it- Hide quoted text -

- Show quoted text -

Thats brilliant Joel. Thanks again

Hi Joel, I have tried your code and i am getting error on code line
(see below). Please help

Workbooks.Open Filename:=Fl & "\" & fname

See below the error message

Run-time error '1004'
"C:\My Document\Records\Record A" could not be found. Check the
spelling of the file name, and verify that the file location is
correct
If you are trying to open the file from you list of most recently used
files, make sure that the file has not been renamed, moved, or deleted