Solved

open most recent file in folder

Posted on 2014-09-17
11
319 Views
Last Modified: 2014-09-17
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
0
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 500 total points
ID: 40327728
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
 

Author Comment

by:Jagwarman
ID: 40327881
Samo4fun

I get Compile error User-defined type not defined
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40327883
On which line and did you enable mentioned reference?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jagwarman
ID: 40327914
on Dim FileSys As FileSystemObject and Dim objFile As File

what does did you enable mentioned reference mean?
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40327945
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
 

Author Comment

by:Jagwarman
ID: 40327964
Ah sussed the bit that said "did you enable mentioned reference" I thought I had turned it on but apparently not.
0
 

Author Comment

by:Jagwarman
ID: 40327976
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
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40327977
;) , is it OK now?
0
 

Author Comment

by:Jagwarman
ID: 40327994
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
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40328049
Got the error..

Just replace the line 25 with this :

Workbooks.Open myDir & "\" & strFilename
0
 

Author Closing Comment

by:Jagwarman
ID: 40328161
thanks for staying with it it does exactly what I need now.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question