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

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" wrote:

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

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" wrote:

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" wrote:

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

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" wrote:

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" wrote:

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" wrote:

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

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

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

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

"Sj" wrote:

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