Macro/Script to calculate percentile on daily basis.

[bats0077]

Hi,
I am trying to automate percentile report generation. Any help would be greatly appreciated.
Requirement:
1. I get 40 csv files with data everyday under a shared LAN folder. Folder name is current date and 40 csv files names remain same.
2. I need to calculate a percentile report everyday taking input from 40 csv files' fixed column say A:A in below format
Percentile 90 95 99.7
1.CSV xyz xyz xyz
2.CSV xyz xyz xsw
till 40.CSV dsd sd sd
Problem:
I have to open all 40 csv(which i do using batch command) files then create a new excel report, copy paste percentile formulas in the sheet. I also noticed my percentile formula wont work until I open the linked csv files.

Any pointers to automate this daily activity ?! like a macro to open all 40 sheets then create new report.xls with percentile formula and close them all ??

Thanks
bats0077

 

[wigi]

Did you investigate the Dir() function to loop through the said forlder and its 40 csv files?