repost: searcing multi excel files...

[Scott]

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!

[Toppers]

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]

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" <Toppers@discussions.microsoft.com> wrote in message
news:A5610C00-9DDE-4D0F-9DC8-D4C960985FD3@microsoft.com...
> 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!
>>
>>
>>

[Tom Ogilvy]

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" <sjstemp@btinternet.com> wrote in message
news:F54ae.2361$vU4.18@newsfe6-win.ntli.net...
> 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!
>
>

[Toppers]

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" <Toppers@discussions.microsoft.com> wrote in message
> news:A5610C00-9DDE-4D0F-9DC8-D4C960985FD3@microsoft.com...
> > 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]

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" <Toppers@discussions.microsoft.com> wrote in message
news:77AE2806-9869-4D9D-82D1-CD2772E43C33@microsoft.com...
> 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" <Toppers@discussions.microsoft.com> wrote in message
>> news:A5610C00-9DDE-4D0F-9DC8-D4C960985FD3@microsoft.com...
>> > 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!
>> >>
>> >>
>> >>
>>
>>
>>

[Toppers]

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!
>
>
>

[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


"Toppers" <Toppers@discussions.microsoft.com> wrote in message
news:601804AE-F2C9-4F68-914E-D0EFE66FFD12@microsoft.com...
> 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!
>>
>>
>>

[Scott<work>ùجrë,Š‰ìš'+¢Ê µ&]

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

[Toppers]

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
>

[Toppers]

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
>

[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" <Toppers@discussions.microsoft.com> wrote in message
news:19F5D62F-7795-4699-933F-C0A6ACEA3A27@microsoft.com...
>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
>>

[Toppers]

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" <Toppers@discussions.microsoft.com> wrote in message
> news:19F5D62F-7795-4699-933F-C0A6ACEA3A27@microsoft.com...
> >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
> >>
>
>
>

[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

[Toppers]

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
toppers@johntopley.fsnet.co.uk.

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
>
>
>