Can a Macro be created that can do this?

  • Thread starter Thread starter JulieD
  • Start date Start date
J

JulieD

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
 
I didn't look at this carefully but couldn't you have a macro that puts the
identifying info in a centralfile.xls database that can be searched (using
find) for the file to open.
 
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
 
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 "sheet 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 "sheet 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 "sheet 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 "sheet 3".

I need to retrieve a list of the files that will have his name, (or any
cust.) on "sheet 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
 
Hi John

okay, understood about Access and the problem with using window's search
feature.

Doing a quick google search about returning data from a closed file brought
up the following:

---from Dave Peterson
And if the OP wants to use the John Walkenbach's techniques:
http://j-walk.com/ss/excel/eee/eee009.txt
Look for either: GetDataFromClosedFile or GetValue.

----
i had a quick look at the code provided and you could incorporate it into a
routine that cycles through all of the workbooks in a folder or a series of
folders

Set fs = Application.FileSearch
With fs
.LookIn = "C:\temp\test\"
.Filename = "*.xls" 'set to search for excel files only
If .Execute > 1 Then 'set to 1 because count includes this file
FileCountResponse = MsgBox(.FoundFiles.Count & " Excel file/s
are in this folder")
For i = 1 To .FoundFiles.Count
'code here
next i
End if
End With

---

unfortunately i don't have time at the moment to put them together ... i'll
try and get back to this later today but you might like to have a play with
the above information in the meantime.

Cheers
julieD
 
Hello Julie,

I tried to make the code work, but I haven't had any luck. Have you had a
chance to take a look at it?

I wish I knew how to accomplish this. I really need for this to work in
Excel instead of Access.

Any thoughts are appreciated.
Sincerely,
John Kitchens
 
Back
Top