I need to extract rows of data from 200+ SPREADSHEETS.

[TomTaylor]

I have 200 plus spreadsheet files, (not worksheets) all in the same column format, each about 8 columns and 200-500 rows each. Named A001.XLS, A002.XLS, etc.

If a row contains the value of "1" in column D I need to extract that row to a new spreadsheet, txt file, what have you.

Naturally this is a crisis for today. Any ideas or help appreciated.

I know its going the wrong way but if I could convert all of the .xls files to .csv or .txt and concantanate(sp) them I would be able to handle if from there.

Other posts have addressed the issue of doing this with 100+ worksheets in the same spreadsheet file. How could I get all these Spreadsheet .xls files combined into one file?

Thank you.

Tom

 

[TomTaylor]

Yes the data is on "Sheet1" of each file.

I can deal with either case. Value 1 is todays crisis, the other values will be next weeks crisis

Thank you.

 

[Nimrod]

Ok .. it's all posted in my previous reponse... though it will copy over all rows where D is NOT zero ... OK ?

Please let me know how it works out for you.

 

[kjo1080]

From What I read, I don't think you want to copy over the rows where d is not equal to 0. Nimrods code is GREAT!!, but I don't think it is what you want (at least not the way that I understand)

My advice is to follow Nimrods steps for pasting the code into your spread sheet, but try pasting the original code that I gave you and run that one. Just see if it is what you want when you run it (it will not mess up any of your original data!!!). Again, you are going to have to change the parts of the code that I mentioned earlier:

the "Do While filepart < 250 " you need to put in the exact number of files. "I:\Bsd\" & filename refers to you specific location of your file. Windows("MacroTest.xls").Activate is whatever you name the file you want you rows pasted to. This should also be the file that you paste and run this macro from!

So open the workbook that you want to extract the data to. copy the code from where it says "Sub ExtractData() to End Sub" , paste into a new module (as described by Nimrod) change the items mentioned above to your specific needs, close the module, and run the macro!!

Let me know!

Note:
you can change my code to pull all the rows where D is not empty by changing:

If Range("D" & cell).Value = 1 Then
to
If Range("D" & cell) <> "" Then

 

[rickolh]

Hi, my comments for the codes provided by both Nimrod and kjo1080 have each its goods and both should achieve your desire results with some minor tweaking. However, the major difference between the two is the total time execute the codes.

kjo1080's codes can retrieve data more precisely but will take a much longer time to execute.

Nimrod's codes are much much more faster to execute as no other files are open at all except for the working file with the codes although certain criterias have to be met.

Looking at your conditions, I believe Nimrod's codes ahould suit you as you have a large number of files to search and the conditions are in its favour.