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

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

Date: Sun, 10 Oct 2004 10:50:27 +0800

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