+ Reply to Thread
Results 1 to 20 of 20

Extract Data from all excel files in folder and subfolders within

  1. #1
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    341

    Extract Data from all excel files in folder and subfolders within

    Hi all!!

    Its been long, hope all are doing good.

    I have a Consolidated workbook where I am trying to combine all the sheets in a folder and sub folders with in it.

    My source workbooks has a sheet named "ABSTRACT" from which I am looking to extract the following cell values: B5, B6, B7, B8, F6, F7, F8, and values corresponding to TOTAL COST, and NETT PAID in column F.

    I want all these values in my consolidated workbook in incremental rows started from row 2 i.e., row 2 then row 3 then row 4 etc for each source workbook.

    Apart from the above values, I want 'file name', 'file hyperlink', 'file path' in the last three columns after above data is populated.

    I have attached two source workbooks and Consolidated sheet.

    I don't want to hard-code the file path, I want to able to select folder by myself.

    Please help.
    Attached Files Attached Files
    Last edited by Ravana; 09-02-2021 at 05:41 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Extract Data from all excel files in folder and subfolders within

    BTW, this code is not meant for your update version of wb "Consolidated", but

    I'm not an expert, and I'm not sure if I understand you correctly on what you expect.

    Anyway, maybe you want to try this code :
    Please Login or Register  to view this content.

    The code is not checking if the files inside your picked folder has a sheet name "ABSTRACT"
    So the code assumed that the files inside the folder you picked and all the subfolders within the folder you picked
    are ALL the source file with a sheet name "ABSTRACT". Consequently, wb "Consolidated.xlsm" can not be inside the folder you will choose.

    To test the code if it gives you a result that you expected :
    1. create a new folder (say, folder "test"), and put some source files there.
    2. create subfolder(s) inside the "test" folder and put another source files there.
    3. File "Consolidate.xlsm" is outside "test" folder and it's subfolder(s).
    4. Copy the code to wb "Consolidate" module
    5. Run the sub loopAllSubFolderSelectStartDirectory

    FYI, the opening file code I get from this website

    Ignore me if this isn't what you mean.
    Attached Files Attached Files
    Last edited by karmapala; 09-02-2021 at 11:20 AM.

  3. #3
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Extract Data from all excel files in folder and subfolders within

    A different approach...
    Please Login or Register  to view this content.

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Extract Data from all excel files in folder and subfolders within

    Another Option...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extract Data from all excel files in folder and subfolders within

    Ravana,

    No need to open when Sheet name is given.

    Please Login or Register  to view this content.

  6. #6
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Extract Data from all excel files in folder and subfolders within

    Thank you very much for dangelor, sintek and jindon.
    I learn a lot from all of your codes.

    Jindon's code sooooo fast
    but too hard for me to follow ...
    Last edited by karmapala; 09-02-2021 at 11:28 AM.

  7. #7
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Extract Data from all excel files in folder and subfolders within

    Inspired by jindon's code (without opening the wb)

    Please Login or Register  to view this content.
    Last edited by karmapala; 09-03-2021 at 01:52 PM.

  8. #8
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    341

    Re: Extract Data from all excel files in folder and subfolders within

    @karmapala @dangelor @sintek @jindon

    Thanks to all of you guys.

    @sintek, your code worked but it is only looking at main folder but not into subfolders.

    @dangelor - amazing code but Sl No isn't getting updated.

    @jindon - amazing code worked like charm.


    Once again thanks a lot.
    Last edited by Ravana; 09-02-2021 at 02:00 PM.

  9. #9
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    341

    Re: Extract Data from all excel files in folder and subfolders within

    @karmapala
    Please Login or Register  to view this content.
    above code form post#7... I tried this code, it worked but S No is missing....in your earlier code, S No was present but this code doesn't generate S No.... can you please look into it.

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Extract Data from all excel files in folder and subfolders within

    @sintek, your code worked but it is only looking at main folder but not into subfolders.
    Oops...didn't even notice that...Up in the Clouds I am...
    Please Login or Register  to view this content.

  11. #11
    Valued Forum Contributor
    Join Date
    11-04-2018
    Location
    Denpasar
    MS-Off Ver
    Excel 2010
    Posts
    777

    Re: Extract Data from all excel files in folder and subfolders within

    Hi Ravana,
    Sorry I'm too carried way when trying to make the code without opening the workbook,
    it caused me forget to put the consecutive number on the column A .

    I've added the code now in post #7.
    Please have a look.
    The addition code is the line with variable "oStart".

    Thank you.

  12. #12
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    341

    Re: Extract Data from all excel files in folder and subfolders within

    @ Sintek

    Thanks for the update. But when running your code, each file is getting opened and closed. Is there any-way to perform the code without each being opening and closing?



    @Karmapala

    Thanks for the update. Its absolutely brilliant. Wish I could understand what that code is. I mean, if I ever wanted few more cell values, to tinker the code. Anyway, Thanks for taking your time and helping me.

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Extract Data from all excel files in folder and subfolders within

    @ Sintek

    Thanks for the update. But when running your code, each file is getting opened and closed. Is there any-way to perform the code without each being opening and closing?
    No need...as you already have other options solving that way...
    Only updated due to ...
    @sintek, your code worked but it is only looking at main folder but not into subfolders.

  14. #14
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    341

    Re: Extract Data from all excel files in folder and subfolders within

    Agreed. Thanks sintek.

  15. #15
    Forum Expert
    Join Date
    08-17-2007
    Location
    Poland
    Posts
    2,158

    Re: Extract Data from all excel files in folder and subfolders within

    There is a little problem with the line
    Please Login or Register  to view this content.
    Can not cope with diacritics characters.

    Artik

  16. #16
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Extract Data from all excel files in folder and subfolders within

    Then simply don't use it.

  17. #17
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    341

    Re: Extract Data from all excel files in folder and subfolders within

    Hi!

    Please Login or Register  to view this content.
    Instead of using "NETT PAID" is there a way where I can use wild card because, in some work books, it is "NET PAID" and in few it is "NETT PAID"...
    Last edited by Ravana; 09-06-2021 at 03:09 AM.

  18. #18
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Extract Data from all excel files in folder and subfolders within

    Try replacing the 2nd T with an asterisk.

  19. #19
    Forum Contributor
    Join Date
    09-16-2014
    Location
    Hyderabad
    MS-Off Ver
    MS Office 2016
    Posts
    341

    Re: Extract Data from all excel files in folder and subfolders within

    Thanks.... It worked.

  20. #20
    Forum Expert dangelor's Avatar
    Join Date
    09-06-2011
    Location
    Indiana, USA
    MS-Off Ver
    365 Pro Plus
    Posts
    2,274

    Re: Extract Data from all excel files in folder and subfolders within

    Good to hear!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] list of subfolders in folder - without files and sub-subfolders
    By MartyZ in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-11-2022, 10:56 AM
  2. [SOLVED] Batch convert all excel files in a folder (and its subfolders) to pdf
    By billj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-23-2019, 05:12 PM
  3. listing excel files in a folder including subfolders
    By olivermdantes in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 02-22-2016, 04:17 AM
  4. copying data from excel files in folder/subfolders
    By Hannspree in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-22-2015, 06:22 AM
  5. Macro to extract all metadata from files and subfolders in a folder
    By Abbadon486 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-25-2014, 10:32 AM
  6. [SOLVED] Reading Excel files in folder/subfolders
    By crakter in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 02-21-2014, 05:48 AM
  7. [SOLVED] Opening Excel files in same folder through a Macro - but not subfolders?
    By toffee_madman in forum Excel General
    Replies: 9
    Last Post: 10-19-2011, 11:09 AM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1