+ Reply to Thread
Results 1 to 4 of 4

Extract data from Multiple excel files

  1. 12-30-2015, 09:14 AM #1
    rajani85
    rajani85 is offline
    Registered User
    Join Date
    05-11-2013
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    21

    Extract data from Multiple excel files

    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
    Reply With QuoteRegister To Reply
  2. 12-30-2015, 08:29 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Extract data from Multiple excel files

    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.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 12-31-2015, 03:06 AM #3
    rajani85
    rajani85 is offline
    Registered User
    Join Date
    05-11-2013
    Location
    Hyderabad
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Extract data from Multiple excel files

    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
    Last edited by rajani85; 12-31-2015 at 03:09 AM. Reason: Pic Added
    Reply With QuoteRegister To Reply
  4. 12-31-2015, 02:48 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Extract data from Multiple excel files

    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.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Extract Data from Multiple Closed Excel Files
    By Gittelman in forum Excel General
    Replies: 2
    Last Post: 07-29-2014, 12:09 AM
  2. Extract Data from Multiple Cells of Closed Excel Files and Include Formulas
    By Gittelman in forum Excel General
    Replies: 0
    Last Post: 07-28-2014, 10:19 PM
  3. Extract Data from multiple excel files based on a Enquirer ID
    By pmyk in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-28-2013, 01:40 AM
  4. Extract Data From Multiple Excel Files -> One File
    By ozzabozza in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-16-2013, 10:20 AM
  5. Load multiple excel files into an excel sheet and extract data
    By Martijn79 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-03-2013, 06:08 PM
  6. Extract Data From Multiple Excel Files -> One File
    By Michael via OfficeKB.com in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2012, 01:02 PM
  7. Sort and extract data from one excel file into multiple excel files
    By fahadq in forum Excel General
    Replies: 0
    Last Post: 06-17-2008, 12:39 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1