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

[davidhall80]

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.

 

[lozzablake]

Something along these lines should do it:

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

 

[davidhall80]

thanks!!!!!

Thanks Alot

 

[Joe C]

This does not work for me as I get an error on the
filesysobjct line
Do I need some library opened?

 

[Joe C]

Figured it out, hat to turn scrping on.

 

[manentia]

Figured it out, hat to turn scrping on.

I have the same problem but I don't understand your solution. Can you elaborate? Thanks.

 

[stanleydgromjr]

davidhall80,

This may help you.

See:
macro that identify the newest file in a folder and open it
http://groups.google.com/group/micro...80a40f22b4aea0

Or:
finding last modified file in a certain folder
http://www.ozgrid.com/forum/showthread.php?t=21485


Have a great day,
Stan

 

[GB]

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]

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]

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