Hi,
Any suggestions you can provide for me on this problem are very apppreciated.
I am trying to pull specific data from within a large number of files into
one single listing in an excel sheet. I have a list of files (approx. 1000)
that are saved without extensions or in some cases with meaningless
extensions. (e.g. .12n, .101, .m, etc.). The file name actually represents
a part number. I need to pull the complete file name of each file into one
column of excel. I also need to pull utilization data from each file and
place it in the corresponding row of the excel list. For example, the output
in excel would look something like this:

Filename Utilization
101222 0.67
1032.5n 0.55
10b55.1 0.76

Within each file, the utilization numbers are specified in various ways. In
some cases, the are following the text "SHEET UTILIZATION". In other cases,
they follow the text "Efficiency". There may be other cases as well. In
addition, in some cases there is no utilization number in the file - in which
case I would like to return "not available".

Question: given the file extensions and your description, I assume these
files are not Excel files but some kind of text format? What is the format -
are the comma delimited, or fixed width, or just free text?

You can iterate through the files and open them using the Open "FilePath"
for Input As #1 statement; then you can read it and somehow parse the
contents. But the difficulty is that the contents vary and sometimes it
seems in ways you may not be aware of. You need some logic that can be
followed to find that utilization number, but you say it comes after "SHEET
UTILIZATION" sometimes or "Efficiency" sometimes or who knows what. When you
know what comes before it it is easy to handle: search your input for "SHEET
UTILIZATION" or "Efficiency", find where this ends, and take the next set of
continuous characters. But how could you find it in those "who knows what"
cases? You need to either know where on the line it starts (nth character)
or what is directly before/after it, or perhaps by knowing something
distinctive about the format - is it the only number in the file that would
be in the format 00.0? Unless you have some logically consistent way you can
identify the number you cannot write code to find it.
--
- K Dales


"Boca J" wrote:

Hi,
Any suggestions you can provide for me on this problem are very apppreciated.
I am trying to pull specific data from within a large number of files into
one single listing in an excel sheet. I have a list of files (approx. 1000)
that are saved without extensions or in some cases with meaningless
extensions. (e.g. .12n, .101, .m, etc.). The file name actually represents
a part number. I need to pull the complete file name of each file into one
column of excel. I also need to pull utilization data from each file and
place it in the corresponding row of the excel list. For example, the output
in excel would look something like this:

Filename Utilization
101222 0.67
1032.5n 0.55
10b55.1 0.76

Within each file, the utilization numbers are specified in various ways. In
some cases, the are following the text "SHEET UTILIZATION". In other cases,
they follow the text "Efficiency". There may be other cases as well. In
addition, in some cases there is no utilization number in the file - in which
case I would like to return "not available".