Consulting

Results 1 to 13 of 13

Thread: Hundreds of sheets, trying to loop through and extract specific columns

  1. #1

    Hundreds of sheets, trying to loop through and extract specific columns

    Hello Gurus of the VBA world:

    I could really use some expert help here.

    I have a few hundred excel files named "SENSOR REPORT 01-MAR-19" something. The "SENSOR REPORT" is common to the workbook name, but the date changes and there is an excel file for everyday for the past several years in that format.

    I'm trying to extract 2 specific columns (column B and Column H) from the 3rd worksheet (named "DATA") in the workbook. The workbook has several worksheets, but I'm looking to extract those 2 columns( columns B and H) only from the 3rd sheet named "DATA".

    As a final product, I was wanting 3 columns with the File name(so that I can extract the date out of it later), Column B and Column H.

    Can I paste all these files in a folder and run a macro to where I will get a 3 column output with the below information:

    Output could be:
    SENSOR REPORT 01-MAR-19 | NAME | DATA

    which is pretty much:

    "File name" "header of column B from worksheet named 'DATA"" "header of column H from worksheet named 'DATA""

    I'd be grateful for your help. Thank you!
    Attached Files Attached Files
    Last edited by happy_camper; 06-19-2020 at 09:34 AM.

  2. #2
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    If all files contain the same structure, I think Power Query will do the trick quite easily. Put all files into a folder and do the extraction.

  3. #3
    Thank you JKwan. I will look Power Query up and google and try to figure it out. Thank you

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    ...extract 2 specific columns (column B and Column H)...
    ..."File name" "header of column B from worksheet named 'DATA"" "header of column H from worksheet named 'DATA""...
    Do you want the entire columns or just the Headers?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    I want the entire column values for both columns. Thank you SAMt.

    So, then I will have 3 columns with Date(which was got from the file name), Name and Pressure. I will then filter for specific names and pull data out.

  6. #6
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    Here is the PQ sample. I replicated your attached file into c:\temp\data then created this PQ and pulled the data into it. By the way, don't this this PQ file into that folder, otherwise, it will pull try and pull data into the file.
    Attached Files Attached Files

  7. #7
    Thank you very much JKwan. I appreciate your help. I have a question.

    1) I went into Alt+F11 but could not see a VBA code module or a macro associated.

    So, how do I get this to work? You replicated my file in a particular folder. So, I can go ahead and put all my files in a particular folder. Next, do I put your spreadsheet outside that folder and refer the path and run it? Kindly advise. Thank you!

  8. #8
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    this is PQ not VBA. There is "no" code, at least they are automatically generated by PQ. What you need to do:
    1 - click on the Table of any cell. Your Ribbon now will have Design and Query Tab
    2 - Go to the Query tab and hit Refresh - that is it. It will pull all the files in the folder where you specified

    If you want to see the code, click Edit and when the Editor comes up, on the Home tab, under Query. Hit Advanced Editor. That is the code generated by PQ

  9. #9
    VBAX Expert
    Joined
    Aug 2004
    Posts
    810
    Location
    Yes, put all your data files into c:\temp\data and hit refresh and all data will get pulled in. Don't put the PQ file into that folder. If you edit the PQ code, you can change the folder by my post #8

  10. #10
    Oops. JKwan. It says cannot extract data from a password protected notebook. I dont know the password to these workbooks but all the data is visible

  11. #11
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    See also: http://www.vbaexpress.com/kb/getarticle.php?kb_id=454

    In VBA, with the use of DIR, you can open every file in a folder, then copy the data you want. Don't forget to close each opened file before opening the next
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  12. #12
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    A Handy tip

    • Create two Constants: FirstFileDate and LastFileDate as Longs
    • Paste the Date parts of the first file name and the last file name into two Excel Cells
    • Format those cell as Number with no decimal places.
    • Give the two constants those numerical values


    Now you can iterate thru all files with
    For d = FirstFileDate to LastfileDate
        Filename = "SENSOR REPORT " & Format(d, "dd-mmm-yy")
        '
        '
        '
    Next d
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  13. #13
    Thank you!

Posting Permissions

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