I need to be able to search 5 or 6 separate excel files that in turn hold
around 10,000 rows of customer data each and find precise records...

For instance I need to find PAUL SMITH of LONDON with postcode E17 4AJ, how
can I do this simply?

Unfortunately I do not have that option. The only option I have is to
search each of the files and as opposed to opening each one individually and
searching I'm sure there must be a way I can automatically search each one
from one location and produce the desired results.

It doesn't matter if the code is slow and crude... I just need something to
get my teeth into!!!

Regards

Scott

Sorry for the repost, its just im sure there is a solution!

Scott,
Some questions:

- How is the data organised e.g from your example it could be fields with
FirstName, SurName, City, PostCode?
- How do you want to enter your selection(s) e.g. via Userform?
- Where do you want to put the selected data?

As answers to your previous post indicate, this is unlikely to be simple but
more info is needed to make a judgement on how best to tackle it.


"Scott" wrote:

I need to be able to search 5 or 6 separate excel files that in turn hold
around 10,000 rows of customer data each and find precise records...

For instance I need to find PAUL SMITH of LONDON with postcode E17 4AJ, how
can I do this simply?

Unfortunately I do not have that option. The only option I have is to
search each of the files and as opposed to opening each one individually and
searching I'm sure there must be a way I can automatically search each one
from one location and produce the desired results.

It doesn't matter if the code is slow and crude... I just need something to
get my teeth into!!!

Regards

Scott

Sorry for the repost, its just im sure there is a solution!

Basically my users data is info about themselves... i.e.

firstname, lastname, house #, street, district, postcode, tel #, credit card
# and policy #

What I need to do is maybe have a stand alone userform that searches *.xls
file in the current directory and either (a) displays ala SQL list all the
matches or (b) bring up a list of row numbers for which these are in.

This would have to asking for more than 1 piece of info because obviously if
you put "smith" in it would bring up numerous hits...

Thanks for your time

Scott
(I'm surprised this is very easy as I would imagine it is quite a common
thing to do???)

"Toppers" wrote in message
...
Scott,
Some questions:

- How is the data organised e.g from your example it could be fields with
FirstName, SurName, City, PostCode?
- How do you want to enter your selection(s) e.g. via Userform?
- Where do you want to put the selected data?

As answers to your previous post indicate, this is unlikely to be simple
but
more info is needed to make a judgement on how best to tackle it.


"Scott" wrote:

I need to be able to search 5 or 6 separate excel files that in turn hold
around 10,000 rows of customer data each and find precise records...

For instance I need to find PAUL SMITH of LONDON with postcode E17 4AJ,
how
can I do this simply?

Unfortunately I do not have that option. The only option I have is to
search each of the files and as opposed to opening each one individually
and
searching I'm sure there must be a way I can automatically search each
one
from one location and produce the desired results.

It doesn't matter if the code is slow and crude... I just need something
to
get my teeth into!!!

Regards

Scott

Sorry for the repost, its just im sure there is a solution!

Look at your options using the FileSearch object. I am not sure you can
search for multiple words/phrases. I am not familiar with your postal code,
but unless it is a very inefficent system, I would think searching for
London and E17 4AJ would be redundant.

So perhaps search for files containing that postal code. Then search those
files for Paul Smith. Then open that subset and look for Paul Smith and E17
4AJ on the same line. (using the FIND method).

--
Regards,
Tom Ogilvy

"Scott" wrote in message
...
I need to be able to search 5 or 6 separate excel files that in turn hold
around 10,000 rows of customer data each and find precise records...

For instance I need to find PAUL SMITH of LONDON with postcode E17 4AJ,
how
can I do this simply?

Unfortunately I do not have that option. The only option I have is to
search each of the files and as opposed to opening each one individually
and
searching I'm sure there must be a way I can automatically search each one
from one location and produce the desired results.

It doesn't matter if the code is slow and crude... I just need something
to
get my teeth into!!!

Regards

Scott

Sorry for the repost, its just im sure there is a solution!

More questions:

- Do you always have the post code, as this normally give a single hit
(unless the SMITHs oocupy a street! and even then the hits would be small)?
If so, this could (should) be the search criterion i.e Lastname + Post Code).

- Is the data sorted in any way and/or can it be sorted if required?

_ With data volumes you specify, it might be possible to merge the data
into one (temporary) file: how confident are you on the volumes and how
dynamic are they?

TIA

"Scott" wrote:

Basically my users data is info about themselves... i.e.

firstname, lastname, house #, street, district, postcode, tel #, credit card
# and policy #

What I need to do is maybe have a stand alone userform that searches *.xls
file in the current directory and either (a) displays ala SQL list all the
matches or (b) bring up a list of row numbers for which these are in.

This would have to asking for more than 1 piece of info because obviously if
you put "smith" in it would bring up numerous hits...

Thanks for your time

Scott
(I'm surprised this is very easy as I would imagine it is quite a common
thing to do???)

"Toppers" wrote in message
...
Scott,
Some questions:

- How is the data organised e.g from your example it could be fields with
FirstName, SurName, City, PostCode?
- How do you want to enter your selection(s) e.g. via Userform?
- Where do you want to put the selected data?

As answers to your previous post indicate, this is unlikely to be simple
but
more info is needed to make a judgement on how best to tackle it.


"Scott" wrote:

I need to be able to search 5 or 6 separate excel files that in turn hold
around 10,000 rows of customer data each and find precise records...

For instance I need to find PAUL SMITH of LONDON with postcode E17 4AJ,
how
can I do this simply?

Unfortunately I do not have that option. The only option I have is to
search each of the files and as opposed to opening each one individually
and
searching I'm sure there must be a way I can automatically search each
one
from one location and produce the desired results.

It doesn't matter if the code is slow and crude... I just need something
to
get my teeth into!!!

Regards

Scott

Sorry for the repost, its just im sure there is a solution!

Editing the volumes is a no go from the start... They will not let me do
that. Read only! The postcode is always going to be there as well as the
surname and thus that would be the basis for the search.

Fortunately that data is consistent across the files and thus should be able
to do any accurate search...

Scott


"Toppers" wrote in message
...
More questions:

- Do you always have the post code, as this normally give a single hit
(unless the SMITHs oocupy a street! and even then the hits would be
small)?
If so, this could (should) be the search criterion i.e Lastname + Post
Code).

- Is the data sorted in any way and/or can it be sorted if required?

_ With data volumes you specify, it might be possible to merge the data
into one (temporary) file: how confident are you on the volumes and how
dynamic are they?

TIA

"Scott" wrote:

Basically my users data is info about themselves... i.e.

firstname, lastname, house #, street, district, postcode, tel #, credit
card
# and policy #

What I need to do is maybe have a stand alone userform that searches
*.xls
file in the current directory and either (a) displays ala SQL list all
the
matches or (b) bring up a list of row numbers for which these are in.

This would have to asking for more than 1 piece of info because obviously
if
you put "smith" in it would bring up numerous hits...

Thanks for your time

Scott
(I'm surprised this is very easy as I would imagine it is quite a common
thing to do???)

"Toppers" wrote in message
...
Scott,
Some questions:

- How is the data organised e.g from your example it could be fields
with
FirstName, SurName, City, PostCode?
- How do you want to enter your selection(s) e.g. via Userform?
- Where do you want to put the selected data?

As answers to your previous post indicate, this is unlikely to be
simple
but
more info is needed to make a judgement on how best to tackle it.


"Scott" wrote:

I need to be able to search 5 or 6 separate excel files that in turn
hold
around 10,000 rows of customer data each and find precise records...

For instance I need to find PAUL SMITH of LONDON with postcode E17
4AJ,
how
can I do this simply?

Unfortunately I do not have that option. The only option I have is to
search each of the files and as opposed to opening each one
individually
and
searching I'm sure there must be a way I can automatically search each
one
from one location and produce the desired results.

It doesn't matter if the code is slow and crude... I just need
something
to
get my teeth into!!!

Regards

Scott

Sorry for the repost, its just im sure there is a solution!

Scott,
Here is some code to get you started. I haven't done anything
regarding the Userform but have put together some code to get the XLS files
(thanks to a previous posting by Tom) and then a routine get the post code.

HTH

Option Explicit
Dim wkbks() As String
Dim nwkbks As Integer
Dim SrchPc As String
Sub Main()
Call OpenAllExcelFiles
Call FindPostCode("SO16 9AZ")
End Sub

Sub OpenAllExcelFiles()
'based on a Tom Ogilvy example
Dim wks As Worksheet
Dim wkbk As Workbook
Dim i As Integer

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\MultiFiles\" '<== set
the directory
.SearchSubFolders = False
.Filename = ".xls"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(Filename:=.FoundFiles(i))
ReDim Preserve wkbks(i)
wkbks(i) = FileNameOnly(.FoundFiles(i)) ' Store Workbook names


Next i
Else
MsgBox "There were no files found."
End If
nwkbks = .FoundFiles.Count
End With

End Sub


Sub FindPostCode(SrchPc)

Dim Pc_Found As Boolean
Dim i As Integer
Dim srchrng As Range
Dim pc As Variant

Pc_Found = False

For i = 1 To nwkbks ' Loop through workbooks

Workbooks(wkbks(i)).Activate

With ActiveWorkbook
' Assumes post code in column C - change as required
Set srchrng = Worksheets("Sheet1").Range("C2:C" & Cells(Rows.Count,
"C").End(xlUp).Row)
Set pc = srchrng.Find(SrchPc, LookIn:=xlValues)
If Not pc Is Nothing Then
MsgBox "Post Code " & SrchPc & " found in " & wkbks(i)
Pc_Found = True
Exit For
End If

End With

Next i

If Not Pc_Found Then
MsgBox "Post Code " & SrchPc & " was not found"
End If

End Sub

Function FileNameOnly(pname) As String
' Returns the filename from a path/filename string
Dim i As Integer, length As Integer, temp As String
length = Len(pname)
temp = ""
For i = length To 1 Step -1
If Mid(pname, i, 1) = Application.PathSeparator Then
FileNameOnly = temp
Exit Function
End If
temp = Mid(pname, i, 1) & temp
Next i
FileNameOnly = pname
End Function


"Scott" wrote:

I need to be able to search 5 or 6 separate excel files that in turn hold
around 10,000 rows of customer data each and find precise records...

For instance I need to find PAUL SMITH of LONDON with postcode E17 4AJ, how
can I do this simply?

Unfortunately I do not have that option. The only option I have is to
search each of the files and as opposed to opening each one individually and
searching I'm sure there must be a way I can automatically search each one
from one location and produce the desired results.

It doesn't matter if the code is slow and crude... I just need something to
get my teeth into!!!

Regards

Scott

Sorry for the repost, its just im sure there is a solution!

Thanks for the brisk reply toppers. Sorry been at work all night!!!

The searching and opening of the file structure works fine but the search
for postcode does not appear to work at the moment...

I have another busy day at work tomorrow so will be able to talk to you more
late sat and Sunday... Thanks so far looks promising and is giving me good
ideas...

Scott


"Toppers" wrote in message
...
Scott,
Here is some code to get you started. I haven't done anything
regarding the Userform but have put together some code to get the XLS
files
(thanks to a previous posting by Tom) and then a routine get the post
code.

HTH

Option Explicit
Dim wkbks() As String
Dim nwkbks As Integer
Dim SrchPc As String
Sub Main()
Call OpenAllExcelFiles
Call FindPostCode("SO16 9AZ")
End Sub

Sub OpenAllExcelFiles()
'based on a Tom Ogilvy example
Dim wks As Worksheet
Dim wkbk As Workbook
Dim i As Integer

With Application.FileSearch
.NewSearch
.LookIn = "C:\Documents and Settings\My Documents\MultiFiles\" '<== set
the directory
.SearchSubFolders = False
.Filename = ".xls"
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
Set wkbk = Workbooks.Open(Filename:=.FoundFiles(i))
ReDim Preserve wkbks(i)
wkbks(i) = FileNameOnly(.FoundFiles(i)) ' Store Workbook names


Next i
Else
MsgBox "There were no files found."
End If
nwkbks = .FoundFiles.Count
End With

End Sub


Sub FindPostCode(SrchPc)

Dim Pc_Found As Boolean
Dim i As Integer
Dim srchrng As Range
Dim pc As Variant

Pc_Found = False

For i = 1 To nwkbks ' Loop through workbooks

Workbooks(wkbks(i)).Activate

With ActiveWorkbook
' Assumes post code in column C - change as required
Set srchrng = Worksheets("Sheet1").Range("C2:C" & Cells(Rows.Count,
"C").End(xlUp).Row)
Set pc = srchrng.Find(SrchPc, LookIn:=xlValues)
If Not pc Is Nothing Then
MsgBox "Post Code " & SrchPc & " found in " & wkbks(i)
Pc_Found = True
Exit For
End If

End With

Next i

If Not Pc_Found Then
MsgBox "Post Code " & SrchPc & " was not found"
End If

End Sub

Function FileNameOnly(pname) As String
' Returns the filename from a path/filename string
Dim i As Integer, length As Integer, temp As String
length = Len(pname)
temp = ""
For i = length To 1 Step -1
If Mid(pname, i, 1) = Application.PathSeparator Then
FileNameOnly = temp
Exit Function
End If
temp = Mid(pname, i, 1) & temp
Next i
FileNameOnly = pname
End Function


"Scott" wrote:

I need to be able to search 5 or 6 separate excel files that in turn hold
around 10,000 rows of customer data each and find precise records...

For instance I need to find PAUL SMITH of LONDON with postcode E17 4AJ,
how
can I do this simply?

Unfortunately I do not have that option. The only option I have is to
search each of the files and as opposed to opening each one individually
and
searching I'm sure there must be a way I can automatically search each
one
from one location and produce the desired results.

It doesn't matter if the code is slow and crude... I just need something
to
get my teeth into!!!

Regards

Scott

Sorry for the repost, its just im sure there is a solution!

I have now got access to these newsgroups at work! Did you have any idea why
the program does not show any post codes?

I have changed the code to the correct cells and also put in postcodes that
are definately there...

Thanks for your time

Scott


Thanks for the brisk reply toppers. Sorry been at work all night!!!

The searching and opening of the file structure works fine but the search
for postcode does not appear to work at the moment...

I have another busy day at work tomorrow so will be able to talk to you more
late sat and Sunday... Thanks so far looks promising and is giving me good
ideas...

Scott

Scott,
It could be because FIND is case sensitive so you can add the
MatchCase parameter to FIND and set to TRUE to make it case sensitive. Mine
works OK.

I now have a version which works from a Userform, searches on Post Code and
if it finds more than one match, populates a combobox with customer name.;
the user can then select the customer and it populates the customer detail
fields.

If there is only one customer, the fields are populated immediately and the
combobox remains invisible.

How much are you willing to pay for the code?!!

To avoid clogging up this NG do you want to continue this off-line? If so,
let me have an e-mail address.

HTH

"Scott<work" wrote:

I have now got access to these newsgroups at work! Did you have any idea why
the program does not show any post codes?

I have changed the code to the correct cells and also put in postcodes that
are definately there...

Thanks for your time

Scott


Thanks for the brisk reply toppers. Sorry been at work all night!!!

The searching and opening of the file structure works fine but the search
for postcode does not appear to work at the moment...

I have another busy day at work tomorrow so will be able to talk to you more
late sat and Sunday... Thanks so far looks promising and is giving me good
ideas...

Scott

I have done further testing and FIND should work as-is.


"Scott<work" wrote:

I have now got access to these newsgroups at work! Did you have any idea why
the program does not show any post codes?

I have changed the code to the correct cells and also put in postcodes that
are definately there...

Thanks for your time

Scott


Thanks for the brisk reply toppers. Sorry been at work all night!!!

The searching and opening of the file structure works fine but the search
for postcode does not appear to work at the moment...

I have another busy day at work tomorrow so will be able to talk to you more
late sat and Sunday... Thanks so far looks promising and is giving me good
ideas...

Scott

Unfortunately I have no funds to offer. The best I can do is give you a
couple of tips for the 2000 guineas and 1000 guineas next week at Newmarket.
Money is certainly not a strong point of mine!

Regards
Scott


"Toppers" wrote in message
...
I have done further testing and FIND should work as-is.


"Scott<work" wrote:

I have now got access to these newsgroups at work! Did you have any idea
why
the program does not show any post codes?

I have changed the code to the correct cells and also put in postcodes
that
are definately there...

Thanks for your time

Scott


Thanks for the brisk reply toppers. Sorry been at work all night!!!

The searching and opening of the file structure works fine but the
search
for postcode does not appear to work at the moment...

I have another busy day at work tomorrow so will be able to talk to you
more
late sat and Sunday... Thanks so far looks promising and is giving me
good
ideas...

Scott

I am not a gambling man! Anyway, how do want to proceed froom here?

"Scott" wrote:

Unfortunately I have no funds to offer. The best I can do is give you a
couple of tips for the 2000 guineas and 1000 guineas next week at Newmarket.
Money is certainly not a strong point of mine!

Regards
Scott


"Toppers" wrote in message
...
I have done further testing and FIND should work as-is.


"Scott<work" wrote:

I have now got access to these newsgroups at work! Did you have any idea
why
the program does not show any post codes?

I have changed the code to the correct cells and also put in postcodes
that
are definately there...

Thanks for your time

Scott


Thanks for the brisk reply toppers. Sorry been at work all night!!!

The searching and opening of the file structure works fine but the
search
for postcode does not appear to work at the moment...

I have another busy day at work tomorrow so will be able to talk to you
more
late sat and Sunday... Thanks so far looks promising and is giving me
good
ideas...

Scott

Well the code I use at the moment, can certainly check the current directory
and finds all the valid files correctly. But the postcode search doesn't
seem to work? I have stepped through the code and I doesn't find postcodes
that are there??? That seems odd to me? Obviously I'd like to get this
problem solved, but I have nothing financial to offer you, like I said
earlier the one thing I am really good at is passing on racing tips!

So I really would like you to help me, but if not then I guess I'll have to
suffer in silence!

Scott

Scott,
Happy to help as my own code works OK. I have even tried a
volume test ( a file with 63000+ entries) and that found my post codes. I
can't understand why yours doesn't work as the code uses the "standard" find
logic; I have added to mine to do "FindNext" but yours should find a least
one code.

It won't match if the post code(s) have extraneous blanks at front and back
.... could this be happening? and I assume you get the "No post code found"
message.

If you want, e-mail your code ( plus some data) to me at
.

HTH


"Scott" wrote:

Well the code I use at the moment, can certainly check the current directory
and finds all the valid files correctly. But the postcode search doesn't
seem to work? I have stepped through the code and I doesn't find postcodes
that are there??? That seems odd to me? Obviously I'd like to get this
problem solved, but I have nothing financial to offer you, like I said
earlier the one thing I am really good at is passing on racing tips!

So I really would like you to help me, but if not then I guess I'll have to
suffer in silence!

Scott