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

[happy_camper]

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!

[JKwan]

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.

[happy_camper]

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

[SamT]

...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?

[happy_camper]

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.

[JKwan]

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.

[happy_camper]

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!

[JKwan]

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

[JKwan]

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

[happy_camper]

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

[SamT]

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

[SamT]

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

[happy_camper]

Thank you!