Can VB open the most recently created file in a folder?

davidhall80

Well-known Member
Joined
Jul 8, 2006
Messages
663
I have a folder called 'Refresh'. Every week a new file is dropped into that folder. Instead of me placing that new file into my workbook, i would like my macro to just grab it. Is there a way for VB to grab the most recently created file that comes into my 'Refresh' Folder.
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

lozzablake

Well-known Member
Joined
Dec 15, 2005
Messages
818
Something along these lines should do it:

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
 

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841
This does not work for me as I get an error on the
filesysobjct line
Do I need some library opened?
 

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841

ADVERTISEMENT

Figured it out, hat to turn scrping on.
 

GB

New Member
Joined
Sep 26, 2004
Messages
2
Does anyone know what this refers to?

Joe C Re: Can VB open the most recently created file in a folder?
--------------------------------------------------------------------------------
Figured it out, hat to turn scrping on.

I'm having exactly the same issue; running the code returns the following error

Compile Error. User-defined type not defined.
 

Joe C

Well-known Member
Joined
Oct 17, 2002
Messages
841
Yes and the code above for the most part does that. It is particular to the type of extension file you are looking for. When it runs you need certain librairies open. If you get a debug error look at the line that it is stopping on. You then need to find the library that houses this comand.
I cann ot tell you which one for I had to open like 12 different libraries to operate the module, so I have a bunch open. Someone else might know.
 

dragontooth

New Member
Joined
Mar 3, 2008
Messages
29
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.

HTH
dragontooth
 

Forum statistics

Threads
1,136,310
Messages
5,675,002
Members
419,542
Latest member
shablagoo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top