View Full Version : Solved: Sort all files in a folder and open the latest one

11-02-2012, 01:43 AM
Dear all, can somebody help me to have a code which will sort all files in a specified folder (this folder containes mainly excel files and few pdf files) according to Date and then open the latest excel file?

It really helpful if someone can help me out.

Thanks and regards,

11-02-2012, 03:03 AM
Sub M_snb_most_recent()
c00 = "G:\OF\"
Workbooks.Open c00 & Split(CreateObject("wscript.shell").exec("cmd /c dir " & c00 & "*.xls /b /o-d").stdout.readall, vbCrLf)(0)
End Sub

Adapt the path to your own situation

11-05-2012, 01:23 AM
Thanks snb for your solution.

11-06-2012, 01:49 AM
Dear snb, I have a problem with your code. Problem is coming, if in the folder name, there is some space. Say my folder is c00 = "G:\OF\Fol 1" then I am getting following error:

Run-time error '9':
Subscript out of range

Is there some workaround?

I am using Excel-2003


11-06-2012, 03:11 AM
You always have to end the path with a backslash

11-06-2012, 03:14 AM
Oh yes....... I meant to say
c00 = "G:\OF\Fol 1\" only!

11-06-2012, 04:01 AM
In that case:

Sub M_snb_most_recent()

c00 = "G:\OF space\"
Workbooks.Open c00 & Split(CreateObject("wscript.shell").exec("cmd /c dir " & chr(34) & c00 & "*.xls" & chr(34) & " /b /o-d").stdout.readall, vbCrLf)(0)
End Sub

11-06-2012, 05:17 AM
Thanks snb, it really worked. Thank you very much for your effort