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.

Option Explicit

Sub CollectData()
Dim wbData As Workbook, ws2 As Worksheet, ws3 As Worksheet, wsDEST As Worksheet
Dim NR As Long, fPATH As String, fNAME As String

Set wsDEST = ThisWorkbook.Sheets("Sheet1")                  'sheet to build results
NR = wsDEST.Range("C" & Rows.Count).End(xlUp).Row + 1       'next empty row

fPATH = "C:\TEMP\XXvsYY\"       'path to data files, remember the final \ in this string
fNAME = Dir(fPATH & "*.xlsx")   'get first filename

Do While Len(fNAME) > 0
    Set wbData = Workbooks.Open(fPATH & fNAME)              'open the found file
    wsDEST.Range("A" & NR).Value = fNAME                    'filename in col A, timestamp in col B
    wsDEST.Range("B" & NR).Value = Format(FileDateTime(wbData.FullName), "m/d/yy h:mm am/pm")
    With wsDEST.Range("C" & NR).Resize(, 4)                 'collect info from sheet2
        .Formula = "=COUNTIF('[" & fNAME & "]" & wbData.Sheets(2).Name & "'!C6, R2C)"
        .Value = .Value
    End With
    With wsDEST.Range("G" & NR).Resize(, 4)                 'collect info from sheet3
        .Formula = "=COUNTIF('[" & fNAME & "]" & wbData.Sheets(3).Name & "'!C6, R2C)"
        .Value = .Value
    End With

    wbData.Close False          'close the data file
    NR = NR + 1                 'next empty row
    fNAME = Dir                 'get next filename
Loop

End Sub

[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.