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.

 

[Zack Barresse]

Before you open a workbook programmatically, it is best to check if it is open first...

Public Function WbOpen(wbName As String) As Boolean
'Originally found by Jake Marx
    On Error Resume Next
    WbOpen = Len(Workbooks(wbName).Name)
End Function

Use it like this ...

'Setting workbook variable..
Dim wb as Workbook
If WbOpen("book1.xls") = True Then
    Set wb = Workbooks("book1.xls")
Else
    Set wb = Workbooks.Open("C:\path\book1.xls")
End If

HTH

 

[Zack Barresse]

When opening workbooks you should check if they are open or not first...



<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Function</SPAN> WbOpen(wbName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><SPAN style="color:#007F00">'Originally found by Jake Marx</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    WbOpen = Len(Workbooks(wbName).Name)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

 

[tech2]

Hi,

I can see where the code is finding the latest file in the 'Refresh' folder but I don't see the code where it's grabbing it from. For example, I need the macro to look in my 'TEMP' directory and put the latest file into a folder called 'NEW'. The file name stays the same before the 'dot' but the extension changes.

thx

tech2

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

 

[Mr Bongo]

Hello

Brilliant website, extremely helpful.

I have got this macro working, but I need a bit more functionality, and as a newb, I am having massive difficulty with VBA syntax and commands.

Essentially, I need to copy the data in the most recent file into a summary worksheet. I have given effect to this as follows (this is at the very end of the module):

Workbooks.Open strFilename
Range("a1").CurrentRegion.Copy
Workbooks("Testy.xlsm").Activate
Sheets("Get Data").Activate
Range("a1").Select
ActiveSheet.Paste

After this I need to :
- close the most recent file that the code has opened (I cannot seem to activate let alone close "strFilename")
- set up an interval routine which causes the macro to re-execute in 5 minutes time

It is important that I can close the workbook that has just been opened (not the summary workbook), as I will be opening a new one every 5 minutes. The code should also not close all workbooks other than the active workbook (as I will be using excel for other things while this is running).

Any help massively appreciated

H

 

[ianmiles2012]

Hello,

I get a Run-time error '1004' with the line: Workbooks.Open strFilename

Any ideas ?

Many thanks,
Ian

 

[Zack Barresse]

Usually it's best to start your own thread, and if you'd like to reference another thread, post a link to it in your own post, as opposed to hijacking someone else's thread.

As to your specific question, I would think a 1004 error on that line would mean the file path/name was inaccurate.

 

[Legacy 298751]

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

Is there any way to jump back and forth between this designated page after it's been opened? For instance once this file name (that changes) is open, is there a way to switch back and forth between this window and another previously opened excel file for copying and pasting?

 

[Arnodekkers1995]

This may be a bit late, but i´m using the above provided code to open the most recent workbook.
Actually i changed it a real small bit, especially in the workbooks.open line

The point is, it seems like it doesn´t open the workbook, but when i then manually open the workbook, it says it´s already open. So, probably it just doesn´t show it. How can i change this?

My code is below:

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 = "Z:\SolidWorks\Solidworks System Files\Databases\Klanten-Database NAV"
    
    '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
    Debug.Print myDir & "\" & strFilename
    
    'Workbooks.Open strFilename
    Workbooks.Open myDir & "\" & strFilename, False, True
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub

 

[mrtim2232]

Morning,

I have tried to run your program but I would like it to be able to run for different users is this possible?

 

[Zack Barresse]

mrtim2232, please start your own thread. To reference other threads, copy/paste the URL in your new thread.