Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I import text file, analyze data, export results, open next file
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 Thanks, Geoff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I import text file, analyze data, export results, open next file
Whoops! I guess my email addy can't show up that way. It's
geoffro10 at yahoo dot com Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I import text file, analyze data, export results, open next file
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. -- Regards, Tom Ogilvy "Geoffro" wrote in message oups.com... Whoops! I guess my email addy can't show up that way. It's geoffro10 at yahoo dot com Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export / Import to / from Text file | Excel Programming | |||
Import/Export Text File Tab Delimiter | Excel Programming | |||
Using Message Box to Open/Import Text File | Excel Programming | |||
Use VBA code to open a text file with the import wizard | Excel Programming | |||
Get External Data, Import Text File, File name problem | Excel Programming |