Solved

open most recent file in folder

Posted on 2014-09-17
11
191 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
  • 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now