asked on

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

ASKER CERTIFIED SOLUTION

Haris Dulic

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jagwarman

ASKER

Samo4fun

I get Compile error User-defined type not defined

Haris Dulic

On which line and did you enable mentioned reference?

Jagwarman

ASKER

on Dim FileSys As FileSystemObject and Dim objFile As File

what does did you enable mentioned reference mean?

Haris Dulic

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.

Jagwarman

ASKER

Ah sussed the bit that said "did you enable mentioned reference" I thought I had turned it on but apparently not.

Jagwarman

ASKER

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

Haris Dulic

;) , is it OK now?

Jagwarman

ASKER

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

Haris Dulic

Got the error..

Just replace the line 25 with this :

Workbooks.Open myDir & "\" & strFilename

Jagwarman

ASKER

thanks for staying with it it does exactly what I need now.