Macro to look thought multiple folders and subfolder for specific file name and aggregate into one File

[ag079893]

ello

any help or direction to recourses or YouTube will be appreciated to help me in resolving below

I need to aggregate multiple xls files with specific name from multiple folders and subfolder into one file.

I receive multiple reports every day that are saved with in folder with Date in the file name
there is saving logic is :
Fund Name > Year> month > Date (05.05)> bank name> IncomeStatement21361321.xls

Aggregation file is saved in Year folder with Name (YTD PNL.xls) i need Excel to look thought all folder and to find xls file that contain IncomeStatement in the file name. ( there are PDF and xls File with same name) and then merger it into (YTD PNL.xls).

Thank you in advance for your help

 

[MrMisster]

Power query for this.

Search Google for "power query files from folder"

Set your query to look at the top of the hierarchy you want as it will automatically include all sub folders.

Then in query manager just filter the filenames for you needs and have have tables opened.

This should combine all files and give you an aggregate as requested in one table.

 

[ManiacB]

This can also be done through a Macro, but we need to see what the data looks like in the each file.

 

[ag079893]

Power query for this.

Search Google for "power query files from folder"

Set your query to look at the top of the hierarchy you want as it will automatically include all sub folders.

Then in query manager just filter the filenames for you needs and have have tables opened.

This should combine all files and give you an aggregate as requested in one table.

First of all thank you for response

i have tried Power Q but getting ton of errors. and not all files are loading for some reason.

 

[ag079893]

This can also be done through a Macro, but we need to see what the data looks like in the each file.

data structure pretty simple. 5 columns with no headers.
Name of account
start date
last date
security id
pnl

Number of rows can be difference but no more that 2000 rows.

i know how to merge file from specific folder but do not know how to scan subfolder for file name and then pasting it.
Do you know any recourses that would show how to scan subfolders for file that contains the name ?

 

[ag079893]

First of all thank you for response

i have tried Power Q but getting ton of errors. and not all files are loading for some reason.

just to add to the below. files are for EXLS 2016( but my pc has 2013) and every time i try to open it it shows error " File format and extension .... did not match. file could be corrupted or unsafe.... Do you want to open it anyway ? " i think this error causes kick out in Power Q. there is no way i can resave 500 files with new format. hence cant really use power q.