vba to open recent dated file in a folder

[prabhuduraraj09]

Hi all,

In a folder i have many files eg., file name "Report for 22.04.2014.xlsm"

I want vba code to open recent dated file name.

But i dont want vba code to open with last modified date. because i suspect any of the file could be edited by any user.
so i want vba code to check with file name and to know which is recent date and open very recent dated file.

Thank you.

[AlphaFrog]

This will open the most recent dated file name. It tests for file names only within the past 365 days from the current date.

Change the file folder to suit.

Please Login or Register  to view this content.

[prabhuduraraj09]

Hi AlphaFrog,

Thanks for the reply. I could see this could look for recent dates among 365 days.

For current month i have all april dated files in Folder name "04 Apr 2014"
like wise for next month folder will be created as "05 May 2014" And users will save all May dated files in there.....

how could this code could be tweaked to switch over to current month
but keeping in mind if my code is executed on 1st day of month if there is no file for current date it should switchover to previous month and look for very recent dated file.

The code which you have suggested would work if all dated files saved in same folder. But i have folders for every month.

pls help

[AlphaFrog]

Both the folder names and and the file names are dated; Is that correct?

The folder names for a given month have a day-date and could be any day in that month? Why not just name the monthly folders something like May 2014 with no day?

Would you ever have two or more folders for the same month with two different days e.g.; "05 May 2014" and "15 May 2014" ?

[prabhuduraraj09]

Hi AlphaFrog,

sorry not to make it clear statement.

The folder name would be in the name of Month - Format is "05 May 2014"
All files dated from 1st may to 31st may will be in this folder

And next month folder name created for June month - Format is "06 Jun 2014"
All files dated from 1st june to 30th june will be in this folder.

So folder name 05 still says as May and 06 still says June. it is not date.

hope this clarifies....so how could we tweak your code to embede this

[AlphaFrog]

Please Login or Register  to view this content.

[prabhuduraraj09]

Hi AlphaFrog,

i have tried executing the code, having below folders and file name as example.

I have two folders

03 Mar 2014 ( inside this folder there are 2 files Report for 21.04.2014 & Report for 23.04.2014 )
04 Apr 2014 ( inside this folder there are 2 files Report for 18.03.2014 & Report for 29.03.2014 )

on trying this code, It browses through current month folder i.e., 04 Apr 2014 and opens recent file i.e, as per example here it opens Report for 23.04.2014

But if there is no files in 04 Apr 2014 folder of current month.
Then code should go back to March folder i.e, 03 Mar 2014 to open recent dated file in that folder.

This code again and again looking for recent file in current month folder. if there is no file in current month, its not going back to previous month to open recent dated file.

could please look into this.

[AlphaFrog]

Change this...

Please Login or Register  to view this content.

To this...

Please Login or Register  to view this content.

[prabhuduraraj09]

Hi AlphaFrog,

After making the changes yes code picksup previous folder if there is no file in the current month folder.

I have a Question, request you to clarify ....
Now i have folders ( 03 Mar 2014, 04 Apr 2014 ). it is working fine.
What i tried to do is added a one more folder (05 May 2014) for the month of may. But code is not considering this folder.......
Does this because, code sees the current month folder as per system date?.

Please clarify.
Just wanna make sure, so every month if the new folder is created in the same month, no need to change anything in the code.

Thanks and regards.

[AlphaFrog]

Does this because, code sees the current month folder as per system date?.

Yes .

[prabhuduraraj09]

Hi AlphaFrog,

Thanks for your continuous support and timely help.
i completely appreciate your effort.

Added some stars for you

[channy.m]

hey i have a very similar problem and wondering if you could help me too. I tried using the code written in this thread (making tweaks to adapt) but it does not work.

Please Login or Register  to view this content.

The problem is, it doesn't open any files at all. I'm using excel 2010.

Situation (for clarity if needed): i want a spread sheet to open the latest dated xls file (which are all located in the one folder) and named SDRyyyymmdd accordingly.

please help

[AlphaFrog]

hey i have a very similar problem and wondering if you could help me too. I tried using the code written in this thread (making tweaks to adapt) but it does not work.


The problem is, it doesn't open any files at all. I'm using excel 2010.

Situation (for clarity if needed): i want a spread sheet to open the latest dated xls file (which are all located in the one folder) and named SDRyyyymmdd accordingly.

please help

The path should end with a \ character.

strPath = "C:\test\"

Is your dated file extension .xls and not .xlsx or .xlsm ?