Solved

open most recent file in folder

Posted on 2014-09-17
11
274 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
Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
enable formatting rows through Thsworkbook VBA code 5 15
List Box Entries Excel 2010 5 31
Countdown Timer 2 16
Formula to run in whole column: 15 12
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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;…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

829 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