Open a file in the most recent folder

[primaldeity]

Hello All.

I need some help with a macro to open specific file within the newest folder in another folder.

The source file is titled source.xls. It is located at R:\Reports\1302\26. R:\Reports is constant. However, each month the next folder name is changed to reflect the current year and month. For example, the next folder will be R:\Reports\1303. Next, the \26 folder will change daily as it reflects the day the last set of reports were run. So, to try and clear it up, I will open the following report today R:\Reports\1302\26\source.xls and then I'll open R:\Reports\1302\27\source.xls tomorrow, and so on.

I hope this makes sense. Once the folder is open I can copy the data from the source file and put it into the file I want, but I just don't know how to open the most recent source.xls file. If there's a better way to accomplish opening the most recent source.xls file, please let me know.

I am using Windows 7 and Excel 2010.

Any help is greatly appreciated.

Thanks!

[TMS]

If it were me, and I know it's not, I'd just create all the files in one folder and then incorporate the date into the file name. For example, Source 130226.xls.

But, either way, you need to build the location of the file. So, it could be: "C:\Report\" & Application.WorksheetFunction.Text(Date, "yymm") & "\" & Application.WorksheetFunction.Text(Date, "dd") "\source.xls"


Regards, TMS