Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Folder search
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Folder search
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Folder search
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Folder search
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Folder search
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Folder search
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Folder search
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" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Folder search
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. "Sj" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search a folder? | New Users to Excel | |||
Microsoft Search opens when I click on a file folder | Setting up and Configuration of Excel | |||
search folder/directory for a phrase inside excel files | Excel Discussion (Misc queries) | |||
Can Excel search for workbook data in a Windows folder? | Excel Discussion (Misc queries) | |||
Import Folder Search Results in a spreadsheet? | Excel Discussion (Misc queries) |