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

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

Date: Tue, 12 Oct 2004 15:58:14 -0500

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

"JulieD" <JulieD@hctsReMoVeThIs.net.au> wrote in message
news:#uyDTQnrEHA.2784@TK2MSFTNGP10.phx.gbl...
> 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
>
>
> "John Kitchens" <ironax@pineland.net> wrote in message
> news:ck6li809d7@enews3.newsguy.com...
> > 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
> >> >
> >> >
> >> >
> >>
> >>
> >
> >
>
>