PDA

View Full Version : Extract data from Multiple excel files



rinabastia
12-30-2015, 09:57 AM
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.

Regard,
RinaBastia

SamT
12-30-2015, 11:59 AM
Steps in code


For each File in folder,
with File.Sheets("CCDS Vs PI").Range("F:F")
With Results.Sheets("Sheet1")
Results.Range("A" & NextRow) = File.Name
For each RValue in Results.Range("C2:F2)
Results.Cells(NextRow, RValue.Column) = CountIf(File.Range("F:F:"), RValue)
Next RValue
End With Results
End With File
Next File

rinabastia
12-31-2015, 12:36 AM
Dear SamT,

Extremely sorry, couldn't understand.

Regards,
Rajani