How do I import text file, analyze data, export results, open next file

[Geoffro]

Ok, here's my dilemma...

I have about 3000 text files that I want to import one at a time, do
some manipulation to the data, and import a result into a new file or
worksheet where I can then do some analyzing on that data. Oh, one
more thing, I want to be able to do this with a macro. I have a list
of all of the file names and they can be imported into excel. How do I
import the file data using that file name in the workbook?

Here's an example....

I have a file named "file1.txt". I want to import the data from
file1.txt automatically. From there I want to sort the data, do some
calculations, and export the results into a new worksheet or text file
where each line looks like this:

File1,45,686,yes,12342

Afterwards, I want to clear the worksheet and move on to the next file
in the list named "file2.txt" and do the same calculations and add
it to the result worksheet or text file like this:

File1,45,686,yes,12342
File2,44,632,no,14432

Finally, after all 3000+ files have been analyzed I want to take the
finished worksheet or text file and do some other analyzing on it.

Any help is greatly appreciated here and I am more than willing to
compensate someone for their time if they were to help get this done.
I can be contacted at (e-mail address removed)

Thanks,

Geoff

 

[Geoffro]

Whoops! I guess my email addy can't show up that way. It's

geoffro10 at yahoo dot com

Thanks!

 

[Tom Ogilvy]

assume the 3000 names are in a worksheet name filenames in a workbook name
MasterList.xls
Assume the summary sheet is the first sheet in a workbook named Summary.xls

Sub Process3000()
Dim bk as workbook
Dim cell as Range
Dim rng as Range
Dim rng1 as Range
Dim rng3 as Range
for each cell in Workbooks("MasterList.xls"). _
worksheets("Filenames").Range(A1:A3000")
set bk = workBooks.open(cell.name)
set rng = bk.Activesheet.Range("A1").currentRegion
rng.Sort Key1:=bk.Activesheet.Range("A1")
' perform unspecified calculations
' assume a new row is added at the bottom with formula
' as an example.
set rng3 = bk.Activesheet.Cells(rows.count,1).End(xlup)
rng3.Resize(1,4).FormulR1C1 = "=Sum(R1C:R[-1]C)"
rng3.offset(0,2).Value = "Yes"
copy that line to the summation workbook
set rng1 = Workbooks("Summary.xls").worksheets(1) _
cells(rows.count,1).End(xlup)(2)

rng3.offset(0,1).Resize(1,4).copy
rng1.offset(0,1).Pastespecial xlValues
rng1.Value = cell.Value ' file name
bk.close Savechanges:=False
Next
End sub

Of course you have not defined your analysis, so I just gave an example
where such code would be place.