Extract data from Multiple excel files

[rajani85]

Dear All,

Merry Christmas & Happy New Year.

I have a folder with 100s of excel files and each excel workbook has 3 worksheets (except file names & values remaining all are same naming convention in all the excel files)

eg.,
Sheet 1= AAAAA
Sheet 2= XX Vs YY
Sheet 3= YY Vs ZZ

Here my task is I have to go to particular worksheets of the individual workbooks and get the countif (there are 4 categories ie., Major, Minor, Not Applicable & Maximum) of 6th column in the active workbook.

I have attached the template of excel file please have a look.

FileName.xlsxResult_Tempalte.xlsx

Regard,
RinaBastia

[JBeaucaire]

I made a tiny change to your SHEET1 layout. This macro will collect the data as described from your defined folder path (fPATH). Be sure to edit the path in the macro.

Please Login or Register  to view this content.

[rajani85]

Dear JBeaucaire,

Many thanks for your support but I'm extremely sorry to say it is not picking the correct information.

It should be like the below:

File Name Last Modified CCDS Vs PI PI Vs PIL
Minor Not Applicable Major Maximum Minor Not Applicable Major Maximum
File Name 31-12-2015 09:35 4 16 3 1 2 4 0 1

Regards,
RajCapture.PNG

[JBeaucaire]

When I open the FileName.xlsx and filter column F for "Minor", I see 7 rows. The macro is using a COUNTIF() and is getting the result of 7.
http://screencast.com/t/P3lFmxiwkUa

When I filter column F for "Maxmium", I see 2 rows. The macro is using a COUNTIF() and is getting the result of 2.
http://screencast.com/t/Txk2jEDo


Based on your sample file and column F, the macro seems to be doing the correct thing.