open most recent file in folder

is it possible [using VBA code] to open the most recent file in a folder.

The folder contains files that have a name and a date. i.e. files are saved down with dates that exclude weekends but I just need to grab the most recent file.

Investigation 610 - 16 September 2014.xls
Investigation 610 - 17 September 2014.xls
Investigation 610 - 18 September 2014.xls
Investigation 610 - 19 September 2014.xls
Investigation 610 - 22 September 2014.xls

So today 17/9 I need to open the file that would be Investigation 610 - 22 September 2014

Path = G:\Inv\Cpns\CpnTkts\Cpns 2014\09. September 2014 [of course the folder next month will be G:\Inv\Cpns\CpnTkts\Cpns 2014\09. November 2014

Is this doable?

Thanks in advance
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:
Hello,

taken from here : http://www.mrexcel.com/forum/excel-questions/223730-can-vbulletin-open-most-recently-created-file-folder.html#post1093694

Firts you need to do this :
You need to add a reference to the Microsoft Scripting Runtime.

Tools > References and check the Microsoft Scripting Runtime, you will need to scroll down until you see it.

Then this code will work. Here is the code :

Sub GetMostRecentFile()
    
    Dim FileSys As FileSystemObject
    Dim objFile As File
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
        
    'set path for files - change for your folder
    Const myDir As String = "c:\Refresh"
    
    'set up filesys objects
    Set FileSys = New FileSystemObject
    Set myFolder = FileSys.GetFolder(myDir)
        
    
    'loop through each file and get date last modified. If largest date then store Filename
    dteFile = DateSerial(1900, 1, 1)
    For Each objFile In myFolder.Files
        If objFile.DateLastModified > dteFile Then
            dteFile = objFile.DateLastModified
            strFilename = objFile.Name
        End If
    Next objFile
    Workbooks.Open strFilename
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Samo4fun

I get Compile error User-defined type not defined
0
Haris DulicCommented:
On which line and did you enable mentioned reference?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

JagwarmanAuthor Commented:
on Dim FileSys As FileSystemObject and Dim objFile As File

what does did you enable mentioned reference mean?
0
Haris DulicCommented:
In order to use this library you need to add reference to it in VBA go to Tools > References and check the Microsoft Scripting Runtime, you will need to scroll down until you see it.
screen shot
0
JagwarmanAuthor Commented:
Ah sussed the bit that said "did you enable mentioned reference" I thought I had turned it on but apparently not.
0
JagwarmanAuthor Commented:
so that ran but with an Run-time error 1004 saying Investigation 610 - 17 September 2014.xls could not be found but when I open the folder manually I can see other files in there including Investigation 610 - 22 September 2014.xls which is the one I need it to open
0
Haris DulicCommented:
;) , is it OK now?
0
JagwarmanAuthor Commented:
Nope. it ran but with a Run-time error 1004 saying Investigation 610 - 17 September 2014.xls could not be found but when I open the folder manually I can see other files in there including Investigation 610 - 22 September 2014.xls which is the one I need it to open
0
Haris DulicCommented:
Got the error..

Just replace the line 25 with this :

Workbooks.Open myDir & "\" & strFilename
0
JagwarmanAuthor Commented:
thanks for staying with it it does exactly what I need now.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.