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

From: JulieD (JulieD_at_hctsReMoVeThIs.net.au)
Date: 10/08/04 

Date: Fri, 8 Oct 2004 20:30:51 +0800

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