Solved

open most recent file in folder

Posted on 2014-09-17
11
382 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

688 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