Folder search

[Sj]

At present to search for a date within my workbook I use the following code,

Sub myfind()
Dim Message, Title, Default, SearchString
Message = "Enter date as ( d-* or dd-* )" ' Set prompt.
Title = "Select Day " ' Set title.
Default = "dd-mmm-yy" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)

'SearchString = "Rob"
Set S = Sheets.Application
For Each S In Application.Sheets

With S.Range("A1:IV65536")
Set F = .find(SearchString, MatchCase:=False, LookAt:=xlPart,
LookIn:=xlValues)
If F Is Nothing Then
Else
Location = F.Address
S.Select
Range(Location).Select
Exit For
End If
End With
Next S
End Sub


What I want to do is to be able to apply this to searching a folder and it's
subfolders for a date within the books in the folders. The date I will be
searching for will only ever exist once in the folders. Is there a way I can
search the folders for a date that exists in a cell on each spreadsheet

 

[Joel]

Change the variable Folder as required

Sub myfind()
Dim Message, Title, Default, SearchString

'define folder, make sure slash is last character
Folder = "c:\temp\"

Message = "Enter date as ( d-* or dd-* )" ' Set prompt.
Title = "Select Day " ' Set title.
Default = "dd-mmm-yy" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)

FName = Dir(Folder & "*.xls")
Do While FName <> ""
Set bk = Workbooks.Open(Filename:=Folder & FName)

Found = False
For Each S In bk.Sheets

With S.Range("A1:IV65536")
Set F = .Find(SearchString, MatchCase:=False, _
LookAt:=xlPart, LookIn:=xlValues)
If Not F Is Nothing Then

Location = F.Address
S.Range(Location).Select
Found = True
Exit For
End If
End With
Next S
If Found = False Then
bk.Close savechanges:=False
Else
Exit Do
End If
Loop
End Sub

 

[Sj]

Joel

I tried this but this opens the excel book in the folder and not the one
where the date is, the main folder is called Balance and within this is a
Master excel book and two subfolders called 2009 & 2010, within these folders
are workbooks for each month of the year, whay I need to do is be able to
type in the date and workbook that contains that date opens to that sheet
then I can copy the information I need from there.

 

[Joel]

I extracted the year from the data put into the input box and then search the
correct folder

Sub myfind()
Dim Message, Title, Default, SearchString

'define folder, make sure slash is last character
Folder = "c:\temp\"

Message = "Enter date as ( d-* or dd-* )" ' Set prompt.
Title = "Select Day " ' Set title.
Default = "dd-mmm-yy" ' Set default.
' Display message, title, and default value.
SearchString = Trim(InputBox(Message, Title, Default))

'get year
Sdate = DateValue(SearchString)

FName = Dir(Folder & Year(Sdate) & "\*.xls")
Do While FName <> ""
Set bk = Workbooks.Open(Filename:=Folder & FName)

Found = False
For Each S In bk.Sheets

With S.Range("A1:IV65536")
Set F = .Find(SearchString, MatchCase:=False, _
LookAt:=xlPart, LookIn:=xlValues)
If Not F Is Nothing Then

Location = F.Address
S.Range(Location).Select
Found = True
Exit For
End If
End With
Next S
If Found = False Then
bk.Close savechanges:=False
Else
Exit Do
End If
Loop
End Sub

 

[Sj]

When I tried this one I got an error message saying run-time error 1004
and it seems to be at the point where it says

Set bk = Workbooks.Open(Filename:=Folder & fname)

Any ideas why this would be?
SJ

 

[Joel]

from
Set bk = Workbooks.Open(Filename:=Folder & FName)

to
Set bk = Workbooks.Open(Filename:=Folder & Year(Sdate) & "\" & FName)
Loop

 

[Sj]

ok, I've done this and when I run it it opens the dialog box to input the
date so I tried 01/09/2009 and the excel sheet that opened was the November
workbook and not the one titled September would this have anything to with
the date is formatted?

 

[Joel]

Your original requst said :

"What I want to do is to be able to apply this to searching a folder and
it's
subfolders for a date within the books in the folders."

You said within the BOOKS. So my code is searcing all the files in the
folder for the year specified by the Input box. The code is leaving open the
first file it finds with the date you specified in the 1st worksheets sheet
with that date. The code is using DIR() which randomly selects all the files
specified which in this case is "*.xls". then it goes through the sheets in
the order they are listed in the workbokk from left to right.

I'm not sure if the problem is with the date format since I don't know whatt
data is actually stopped on and the date you specified in the inputbox. If
you want to find the earliest or lastest worksheet with the data you are
looking for I need you to specify a different algorithm for which worksheet
you want opened.