+ Reply to Thread
Results 1 to 11 of 11

Extract data based on variables from multiple subfolders

  1. 07-06-2013, 12:38 AM #1
    honeybunny is offline
    Registered User
    Join Date
    07-05-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2003
    Posts
    14

    Extract data based on variables from multiple subfolders

    Hi everyone,

    I am new to VBA and I need help with this. Any help will be appreciated.

    So I have a folder called "Main Source" which is located in 'C:\Documents and Settings\' and inside it there are multiple subfolders named "Publish on yyyy.mm.dd" where yyyy.mm.dd is the date I created the subfolder. More subfolders named in the same way will be added. Inside each subfolder, I have 2 excel files. One is called "Data yyyy.mm.dd" and the other is called "Table yyyy.mm.dd.

    I would like to write a macro to pull data from only Column B of Sheet1 in "Data yyyy.mm.dd" in all subfolders and put them in another workbook called "Output" as a summary.

    The format of all "Data yyyy.mm.dd" files identical, with column A as the reference years.

    I would like to have data from Column B of the file "Data yyyy.mm.dd" to be pasted to the "Output" file corresponding to the reference years enterred in Column A. Each "Data yyyy.mm.dd" file should give one column of data starting from Column B if any relevant data exist. Also, the name of the subfolders where the sources come from should be showed in row 3, on top of each data column.

    Say If there are 10 "Data yyyy.mm.dd" files, there should be 10 columns of data copied from them to the "Output" starts from Column B.

    I attached the sample files. Sorry it's a bit lengthy. Thanks in advance.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 07-06-2013, 05:58 AM #2
    tehneXus is offline
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Extract data based on variables from multiple subfolders

    Hi,

    please check this file: Output.xls

    1. Take care to adapt the path to the publish folders.
    2. The list of years is automatically updated when you change B2 or D2 on the sheet, enter min of 0 and max current year
    3. When the list is updated all existing data is cleared
    4. When data is imported all existing data is cleared

    main functionality:
    Please Login or Register  to view this content.
    Please use [CODE]-TAGS
    When your problem is solved mark the thread SOLVED
    If an answer has helped you please click to give reputation
    Read the FORUM RULES
    Reply With QuoteRegister To Reply
  3. 07-06-2013, 10:50 AM #3
    honeybunny is offline
    Registered User
    Join Date
    07-05-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Extract data based on variables from multiple subfolders

    Thanks a lot techXus.

    That is awesome. That's what I was looking for.

    I have 2 questions.
    (1) How to obtain part of the file name appeared in row 3 as "2011 Feb" instead of the whole thing?
    (2) Can I just look up the first 4 characters of Column A of data.xls to find the corresponding value?
    i.e. I input 2010 in Output.xls and but I have 2010a in data.xls this time. I would like to just look for the first 4 characters of Column A in data.xls to have the corresponding value.

    The file you attached works great but how come I can't view the macro code? I tried to copy the code you posted to a new workbook but it still doesn't work.

    Thanks again for your help.
    Last edited by honeybunny; 07-06-2013 at 11:18 AM.
    Reply With QuoteRegister To Reply
  4. 07-06-2013, 11:23 AM #4
    tehneXus is offline
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Extract data based on variables from multiple subfolders

    Hi,

    please check this code (1) and (2) implemented. Note: it will find "2006a" and "2006asadfs<dklfbos<fb" but not "a2006", you need that too?

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 07-08-2013, 12:28 PM #5
    honeybunny is offline
    Registered User
    Join Date
    07-05-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Extract data based on variables from multiple subfolders

    Hi,

    Thanks again for your help.
    No, I don't need "a2006". But I have encountered error at line 30, about Left$.
    < If Left$(xlRng.Value, 4) = xlWs.Cells(i, 1).Value Then >
    Do we have to define Left?
    Sorry to bother you again.

    Thanks.
    Last edited by honeybunny; 07-08-2013 at 12:32 PM.
    Reply With QuoteRegister To Reply
  6. 07-08-2013, 12:35 PM #6
    tehneXus is offline
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Extract data based on variables from multiple subfolders

    Hi,

    you're welcome.

    What error number/description. On which data this happens?
    Reply With QuoteRegister To Reply
  7. 07-10-2013, 12:28 PM #7
    honeybunny is offline
    Registered User
    Join Date
    07-05-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Extract data based on variables from multiple subfolders

    Hi,

    It showed "Compile Error: Can't Find Project or Library".
    I attached the ammended file.

    I was thinking if the problem could be solved by adding the following syntax
    Please Login or Register  to view this content.
    Thanks a lot.
    Attached Files Attached Files
    Last edited by honeybunny; 07-10-2013 at 12:48 PM.
    Reply With QuoteRegister To Reply
  8. 07-10-2013, 01:12 PM #8
    tehneXus is offline
    Valued Forum Contributor tehneXus's Avatar
    Join Date
    04-12-2013
    Location
    Hamburg, Germany
    MS-Off Ver
    Work: MS-Office 2010 32bit @ Win8 32bit / Home: MS-Office 2016 32bit @ Win10 64bit
    Posts
    944

    Re: Extract data based on variables from multiple subfolders

    Hi,

    you don't have to write your own "Left" function, I suppose there is no "Left$" in 2003?
    Use the usual "Left" instead.

    If this still does not work type "VBA." and you'll get a list of functions.
    Reply With QuoteRegister To Reply
  9. 07-11-2013, 07:31 PM #9
    honeybunny is offline
    Registered User
    Join Date
    07-05-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Extract data based on variables from multiple subfolders

    Hi,

    I tried that in 2007, but it still doesn't work.
    I am sorry but I wasn't clear about the "VBA" part. Where to type "VBA"?

    Thanks again.
    Reply With QuoteRegister To Reply
  10. 07-28-2013, 07:17 AM #10
    honeybunny is offline
    Registered User
    Join Date
    07-05-2013
    Location
    Ohio
    MS-Off Ver
    Excel 2003
    Posts
    14

    Re: Extract data based on variables from multiple subfolders

    Hi,

    The problem is solved. Just have to uncheck the missing library.

    Thank you.
    Reply With QuoteRegister To Reply
  11. 07-28-2013, 09:56 AM #11
    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 based on variables from multiple subfolders

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    _________________
    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
+ 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)

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