Copying cells from multiple files in a folder

[171z]

Hi everyone,

Total VBA novice here. I would like to consolidate the data stored in multiple raw files which is stored in one folder. All the raw files have the same structure and sheet names.
The items I would like to consolidate are the name of the file, cell A3, A4 and A30 in Sheet 1 of the raw files.
The expected outcome should be something like this:

Filename	Sheet1!A3	Sheet1!A4	Sheet1!A30
123.xlsx	abc	100	101

Appreciate if anyone can help with this. Thanks!

p/s: Do let me know if this method is plausible using power query as well

 

[ValuationMan]

Are the files CSVs or structured as Tables? If so, Power Query makes it very easy. You would set your source equal to a folder and expand content. The VBA solution is a bit more involved. I forget if cross-linking is forbidden here, but your google search would be something along the lines of "VBA loop through files in folder."

 

[171z]

Are the files CSVs or structured as Tables? If so, Power Query makes it very easy. You would set your source equal to a folder and expand content. The VBA solution is a bit more involved. I forget if cross-linking is forbidden here, but your google search would be something along the lines of "VBA loop through files in folder."

It is structured as table. The raw data is something like this:

Sheet1! A3	abc
Sheet1! A4	100
Sheet1! A30	101

Appreciate if you can advise on how I can get the output below using power query.

Source.Name	Sheet1!A3	Sheet1!A4	Sheet1!A30
123.xlsx	abc	100	101

Currently, I am stuck with this:

let
    Source = Folder.Files("C:\Desktop\Test\1. RAW"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File"))),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",{{"Source.Name", type text}, {"ITEM", type text}, {"DETAILS", type any}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([ITEM] <> null))
in
    #"Filtered Rows"