Copy 10 rows from specific files in a folder

Hi Friends:

I need help for the following problem. I have about 300 odd excel files in Excel 2003 format in a folder. I wish to extract 10 rows from some selected files out of these 300 odd files, based on a certain column B cell value, which is always a date string. The cell value to look for is "25/05/2012". I have a reference workbook (named reflist.xls) where I have listed, in column C, all the selective files to be searched for this particular date string. This reference file would be kept open (or the code itself can be added to run from this file). The macro should search for only for the file names mentioned in Column C and then search for the date string in the selected files (ignoring all other files in the folder), and when the search term is found, copy that particular row and subsequent 9 rows to Sheet2 of this reference workbook itself. All the files in the folder are properly sorted, have only a single worksheet, and data is upto column N. The reflist.xls lists files to look for with their full name including extension. Thanks.

Based on that information, this should do it. It's difficult sometimes to "FIND" a date unless you get the format correct, which I tried to do, but you'll have to test/tweak that part.

Please Login or Register  to view this content.

Hi JBeaucaire:

Thank you very much for your code. I tried the code and it is working exceedingly well. Since my actual date values in the column were in "3-Apr-12" format, I did have difficulties with the code as it was not giving any output at all. I then tweaked the code as follows and it worked!

Please Login or Register  to view this content.

This line was changed to:

Please Login or Register  to view this content.

This change got it going.

Thanks once again for your kind help.

Just a warning about Excel's FIND method, you took out the LookIn and LookAt parameters, you should not do that. When you took them out, that had no effect on the macro at the moment because it kept using the parameters you took out, that's the problem you have to beware of. The FIND method remembers the last values used for LookIn and LookAt and will use them if you don't specifically control them in the macro.

That means if you were to manually use the CTRL-F (Find) functionality in your daily use of Excel for something else, whatever settings you used for that search would become "set" in the FIND wizard and will be used when the macro runs, too, unless you let the macro specifically correct those values.

As such, I neverr leave those two parameters out.

Please Login or Register  to view this content.

Originally Posted by GDM69

I have observed that the code is working very very irratically, sometimes it works ok and sometimes there is no output at all. There is no consistency. Could you throw some light on these issues for me and other fellow members?
Thanks

Heh, I thought I addressed that already:

Originally Posted by JBeaucaire

It's difficult sometimes to "FIND" a date unless you get the format correct, which I tried to do...

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook, or enough of an excerpt that I can see the problem in action. Make sure your edited version of the macro is already in the workbook. I'll take a look. Try to include instructions on using the data/macro and it fails.

Hi GDM69,

I agree with JBeaucaire on this matter.

Try:

Please Login or Register  to view this content.

and:

Please Login or Register  to view this content.

Hope that helps

I'm not sure what this file is supposed to show me. The macro is designed to search file names in column C, thisworkbook does not contain that data. Did things get mixed up? Is the macro enclosed supposed to be in another workbook and this file is one of the "searched files"?


Based on what I *DO* see in that data, I already told you you would need to tweak the FORMAT to match the date format of your data sheet, so maybe this will help clarify what I mean:

Please Login or Register  to view this content.

That matches the data format of your column A dates. Also, in the popup, enter the date in a way you KNOW won't be confused by Excel. In the US, the date:

12/6/12 is December 6, 2012.

In India, I presume that would be June 6, 2012. Enter the date in the popup in a way you know will be evaluated correctly, like June 6, 2012.

I apologize that I won't be able to suss out why your Excel implementation is behving erratically. The method/macro I've given is the one I've used in 1000s of macros, the only issue that needs managing is the matching of the date format and the inclusion of the 2 parameters you indicate actually make it not work. In my 1000s of macros, taking them out leads to erratic results, not the other way.

I'm sorry your environment seems to be behaving the opposite of normal. I couldn't comment on why at this point.

the code in that workbook is not what was suggested. the line should be

Please Login or Register  to view this content.

so that the search format matches the format of the data as it appears in the sheet (not as it appears in the formula bar). to match the formula bar contents (since your data is literal, not the result of formulas) use

Please Login or Register  to view this content.

Heh, I apologize for not spotting that. I reiterate, it's not only important to always include those two parameters when doing a FIND, it's important they be set correctly!

LookIn:=xlValues
LookIn:=xlFormulas

LookAt:=xlWhole
LookAt:=xlPart

You will always need to figure out which are the correct parameters for your situation, then the macro controls them explicitly every time and should work every time.

==============

You can make your searches more surgical by searching just the RANGE needed instead of all CELLS.

Please Login or Register  to view this content.