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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

MrMisster

New Member
Joined
Feb 21, 2020
Messages
11
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
This can also be done through a Macro, but we need to see what the data looks like in the each file.
 

ag079893

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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,132,706
Messages
5,654,841
Members
418,155
Latest member
demasisi

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
Top