+ Reply to Thread
Results 1 to 37 of 37

Extracting Data from specified Cols in Multiple Workbooks

  1. #1
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Extracting Data from specified Cols in Multiple Workbooks

    Hi Folks
    Hoping someone can help me through this project- I am 'dummy' personified when it comes to Code-

    I currently have 30 individual Workbooks and that number will be increased to 86 over the next couple of months-

    All the Workbooks are stored in D:\ and all Headings are identical. The Sheet names which I want to import the data from will all have the same name-

    What I am looking to do is import the data (values/text) from the specified Cols but only from those Workbooks that I identify by placing the value '1' in a Col adjacent to the Workbook name all of which will be listed in a separate sheet, and only the Rows where Col CE in the Workbook sheet has the value '1'

    I have attached a sample Workbook as I would like it to look for your perusal-

    I would need full detail on how to add all the required Workbooks to the Code as they become available-

    One question I have right now is, will the workbooks have to be open in order to extract the data? I am hoping not as the Workbooks are all >=40mb minimum and are slow in opening due to links to other files.

    Regards
    hammer
    Attached Files Attached Files
    If you have found solving my problem/s to be an interesting and educational exercise then how about Repping me up?
    No idea why I need Rep, other than feeling left out....

  2. #2
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    On your Consolidate there are headings highlighted in Yellow? Are those the only columns you are wanting to import, and to also include column CE?
    Thanks,
    Mike

    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Extracting Data from specified Cols in Multiple Workbooks

    The Sheet names which I want to import the data from will all have the same name-
    What would that name be?
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    @Mike7952 Yes, that is correct-

    @xladept the sheet name is 'Data'

    Cheers

  5. #5
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Are all the imported data worksheets have the same headers naming? Not just same column but exact same column header name? And all the data start in row 2 with headers in row 1?
    Last edited by mike7952; 09-27-2015 at 08:41 PM.

  6. #6
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Apologies re my earlier post Mike-
    I need all those Headings A:N plus CE

    Yes, all Cols have exact same Heading name thanks-

  7. #7
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Okay, by chance can you upload and example workbook that will need imported?

  8. #8
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Hi Mike- they are all to large (>=40mb)

    I can mock one up though with just a few lines- would that be okay?

  9. #9
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Yes that will be good. Just cut it down to 20 or so rows

  10. #10
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Here we go- I deleted all the other sheets otherwise Workbook is from an actual one I use-

    In this one only rows 6 and 15 would be imported into our Consolidation Workbook as those rows are the only ones with the value 1 in Col CE
    Attached Files Attached Files

  11. #11
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Okay give me a few minutes.

  12. #12
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    So Columns O to CD you do not want?

  13. #13
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Correct Mike-

  14. #14
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Give this workbook a try.
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Getting a message I haven't seen before-

    'Opening in protected view' is this normal and does it take a while?

  16. #16
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Is that when code is ran or is it opening the file I uploaded?

  17. #17
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Okay, fixed that part- now to the file

  18. #18
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    I get the debugger pop up and it highlights .open

    My Workbooks are all macro enabled if that makes a difference to the path?

    I input a real Workbook name with a '1' in Col B and the pop up states 'invalid path' and has .xlsx -- maybe need .xlsm?

    Thanks

  19. #19
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Change this in the abc macro to .xlsm

    Please Login or Register  to view this content.

  20. #20
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Capture.PNG copy of pop up

  21. #21
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    That is before I changed to .xlsm but the same pop up appears showing .xlsm

  22. #22
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Change path to files to the to real path to your files

  23. #23
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    D:\ where is the path to the files at?
    Example
    D:\some folder\some other folder\

    Or tell me where these files are located at?

  24. #24
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Really appreciating your valuable time and assistance with this Mike but as I stated in my OP I am a dead set dummy at Code-
    What does a real path look like? (sorry)

  25. #25
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Sorry Mike-
    They are just in D drive- no specific folder/s

  26. #26
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Do a right click on any on of the files that you want to import. Click properties and it will give the full path to where the file is located

  27. #27
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    D:\Curragh.xlsm Like this one?

  28. #28
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Okay then change this line

    Please Login or Register  to view this content.
    to

    Please Login or Register  to view this content.

  29. #29
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Okay Mike- good progress!

    First file it brought in perfectly-
    Second and subsequent files are missing the data from Cols C,D,E and G only- rest of the Cols have the data-

    All Headings etc are identical as each workbook is just a 'save as' from an original-

  30. #30
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    I would have to see those files that are missing the columns. I copied the original file 10 times and it imported everything

  31. #31
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Thanks Mike-
    I will now have a look at them and see if I can spot anything-
    Seems odd that it is bringing in all the other Cols correctly-

  32. #32
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    I cannot see any reason for this Mike (untrained eye)-

    I would have to send you a file via some other means as they are too large to upload here-
    Thanks

  33. #33
    Forum Expert mike7952's Avatar
    Join Date
    12-17-2011
    Location
    Florida
    MS-Off Ver
    Excel 2007, Excel 2016
    Posts
    3,519

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Can email me or upload to drop box or any other file sharing site. Pm me for email.

  34. #34
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Many thanks to mike7952 for providing a solution for this project-

    Cheers
    hammer

  35. #35
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Is the solution posted, if so, which post?

  36. #36
    Forum Contributor
    Join Date
    06-25-2011
    Location
    Sydney
    MS-Off Ver
    Excel 2016
    Posts
    615

    Re: Extracting Data from specified Cols in Multiple Workbooks

    @xladept ---Yes, the solution is at post #14

    There were minor code changes required as shown in subsequent posts-

    Cheers
    hammer

  37. #37
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Extracting Data from specified Cols in Multiple Workbooks

    Thanks hammer!

+ 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. Extracting Data from multiple workbooks using ID number
    By sheexin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-16-2015, 11:59 PM
  2. Error when extracting information from multiple workbooks
    By mcnikal in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-17-2014, 07:50 AM
  3. How can I create a macro for extracting data from multiple workbooks within a folder
    By surfing69 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-06-2013, 11:36 AM
  4. Replies: 0
    Last Post: 07-29-2013, 03:00 AM
  5. Extracting and combining data from specific sheet from multiple workbooks
    By aggies2010 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-19-2013, 03:39 PM
  6. [SOLVED] Compilation of Multiple Cols into 2 Cols in new sheet
    By hammer2 in forum Excel General
    Replies: 4
    Last Post: 07-31-2012, 06:46 AM
  7. Generate multiple charts from cols B & C, conditional of duplicate data in col A
    By kbeaulieu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-13-2012, 08:07 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1