Re: Can a Macro be created that can do this?

From: John Kitchens (ironax_at_pineland.net)
Date: 10/08/04 

Date: Fri, 8 Oct 2004 14:15:09 -0500

Hello Julie,

Access is not currently an option for us. There will only be one user using
this file on one machine at one time. It is not being shared through a
network.

Just one person running the show!

The way I have the file setup now is that every time a new customer comes in
their name is entered on "*** one" and the user will press enter. Once
they do this they have actually added it to a list that stores these names.
This info is stored on "sheet3".

A combobox is used to retrieve the names of the current customers. For
example "Black, Roy" is a customer that comes in a lot.

When he comes in the user just clicks the dropdown from the combobox and
selects his name. (I have them enter the info by last name, first name, and
have it stored alphabetically so it is easier to retrieve.)

I have already tried using the Search feature in windows to return a list of
*.xls files containing the customer's name in given folders, but the problem
is that since the first day that "Roy Black" (or any cust.) comes in there
name is stored on "*** 3" in the template and it will remain there until
the end of time.

So when I do a search for "Black, Roy". It will pull up a list of all the
files since his first visit b/c his name is stored on "*** 3". The
problem is that it will pull all the files even if he wasn't the customer
b/c his name is stored on "*** 3".

I need to retrieve a list of the files that will have his name, (or any
cust.) on "*** 1" in the combobox.

Does that make sense?

My progam works great. I just need to be able to do this one last thing.

If you have any ideas please let me know. Thank you for your previous
reply.

Sincerely,
John Kitchens

"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:#dsgCLTrEHA.3608@TK2MSFTNGP14.phx.gbl...
> Hi John
>
> one difficulty (as i see it) is the amount of time it would take a macro
to
> search through all of the folders & files to retrieve the information.
>
> my first recommendation for a process like this is to do it in something
> like Access as retrieving the names & information would be a lot easier
and
> you wouldn't need to save the "tickets" to separate files, all the
> information could be effectively captured in the database and reported as
> required.
>
> if access isn't an option, then an alternative is that your users have a
> shared workbook open in the background of their machine and every time
they
> wait on a customer they run a macro to write the customer's name / date /
> ticket number to this workbook. You can then use the workbook to return
the
> information you seek. There are limitations to what you can do with a
> shared workbook so these would need to be considered.
>
> Also wondering how many "users" you had.
>
> Alternatively you could use the Search feature in windows to return a list
> of *.xls files containing the customer's name in given folders and save
> yourself the work :)
>
> Cheers
> JulieD
>
>
> "John Kitchens" <ironax@pineland.net> wrote in message
> news:ck5vao02sni@enews1.newsguy.com...
> >I am using Excel 2000. I have a template that the user fills out and
saves
> > as an xls, not xlt, file each time they wait on a customer. There will
be
> > maybe 60 or so tickets on any given day. All of the daily tickets are
> > named
> > for example, t1059(100604), t1060(100604), etc. and saved in a daily
> > folder
> > named for example 100604, 100704, etc.
> >
> > Tommorrow the user starts a new folder and continues naming the files
per
> > the above example.
> >
> > On each ticket(file) I have a combox made from the forms toolbar that
> > contains a list of every customer that has ever visited us. Each time a
> > new
> > customer comes in we type their name in a certain cell and hit enter and
> > then it is available in the combobox to select for future visits.
> >
> > When we save this file under the name t1061(100604) etc. the persons
name
> > is
> > listed in the combobox so that when the file is reopened at a later date
> > their name will be there so it is easy to tell who the customer is. This
> > is
> > a new division of our company and at the present time there are only a
few
> > hundred customers. Some of the customers visit numerous times a day. It
> > is
> > not practical to save the files under the customer's name.
> >
> > Eventually there will probably be 500 or more customers.
> >
> > NOW MY QUESTION.
> >
> > I would like a file that I could open and type the name of a customer
and
> > through the use of a macro have excel either open all of these files or
> > provide a list of all the files that contain the customers name.
> >
> > For example, if Widget Manufacturing has been a customer for the past 6
> > months and call and wants to know how much activity they has done with
us
> > for a certain time period I would like to be able to put his name in and
> > press a button, or something to that effect, and obtain a result of all
> > his
> > files.
> >
> > If I could obtain a list of these files I have another template that I
> > could
> > use to provide a summary and give me the desired information I just need
a
> > way to FIND the files.
> >
> > If there is a way to look in multiple folders that is desired, but if it
> > could only look in one folder at a time I could live with that.
> >
> > I hope this makes sense.
> >
> > Thanks for any help or suggestions that are given.
> >
> > Sincerely,
> > John Kitchens
> >
> >
> >
>
>