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

ag079893

New Member
Joined
Feb 8, 2011
Messages
4
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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.
 
Upvote 0
This can also be done through a Macro, but we need to see what the data looks like in the each file.
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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. :(
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top