open last saved excel file in folder and subfolder / vba excel 2010

[RHB1987]

Hello,

I have been searching for many hours for a VBA-code in Excel for 2010 for the following.

I want to open the last saved closed Excel-file that can be found in a folder and its subfolders. It has to open just one file in the folder or in one of its subfolders.
It should find and open e.g. example.xls in folder "example\folder1\folder2". Because I use different Excel files during the day and closing them, vba should open a file two hours later called later.xls in folder "example\folder3".
I found several codes on the internet, but many do not work in Excel 2010.

The code below works if you want to open a file in a specific folder, unfortunately the code does not search the subfolders. That is the most important part for me. Also it should only find *.xls (and/or xlsx.) If one or two filetypes could be specified, that would be great.
It would be very nice if someone could alter the code below, but this is not necessary, if you have an entirely different approach that is also ok.
But I know the code below works and many other codes on the internet do not for some reason.
For example, the Application.FileSearch does not work in 2010.

The code I have is from http://www.mrexcel.com/forum/showth...en-the-most-recently-created-file-in-a-folder . Many thanks to Iozzablake for 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

I hope there is someone who can alter this code. I have tried myself for some hours, but unfortunately I was unsuccessful.

Sincerely,
Richard

 

[JoeMo]

With some modifications I can get your code to work in Excel 2007, including in sub-folders:

Sub GetMostRecentFile()
    Dim FileSys As Object
    Dim objFile As Object
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
        
    'set path for files - change for your folder
'Set const on next line to your folder path
    Const myDir As String = "C:\Users\Joe\Documents\Visual Basic for Excel"
    
    'set up filesys objects
    Set FileSys = CreateObject("Scripting.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
        'Debug.Print objFile.Name
        If InStr(1, objFile.Name, ".xls") > 0 Then
            If objFile.DateLastModified > dteFile Then
                dteFile = objFile.DateLastModified
                strFilename = objFile.Name
            End If
        End If
    Next objFile
    Workbooks.Open myDir & Application.PathSeparator & strFilename
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub

 

[wigi]

Hello JoeMo

I do not see where you search subfolders. Can you show indicate where your code does this?

 

[emilG]

For sample code to walk a directory tree, look in http://www.ozgrid.com/forum/showthread.php?t=51306 and set
.SearchSubFolders = True</pre>

 

[wigi]

For sample code to walk a directory tree, look in http://www.ozgrid.com/forum/showthread.php?t=51306 and set
.SearchSubFolders = True

Application.FileSearch was deprecated as of Excel 2007. So that is not to be used anymore...
</pre>

 

[RHB1987]

Hi wigi,

thank you for your postings. Unfortunately nothing yet.
I hope someone will have a good solution for this.

Sincerely,
Richard

 

[emilG]

Good point. The functionality has been replaced by FSO - see the Microsoft Support article "HOW TO: Recursively Search Directories by Using FileSystemObject" at http://support.microsoft.com/kb/185601 for details on using FSO to walk a directory tree...

 

[JoeMo]

Hello JoeMo

I do not see where you search subfolders. Can you show indicate where your code does this?

I ran a quick test (apparently too quick!) and thought I was searching subfolders, but you are correct, the code I posted shows no evidence of this.

 

[RHB1987]

I ran a quick test (apparently too quick!) and thought I was searching subfolders, but you are correct, the code I posted shows no evidence of this.

Hi Joe, it was a good try.

Maybe it is helpful if I sum up the changes that need to be made to the code.
I will use the altered code from Joe because it now opens only .xls-files.

Sub GetMostRecentFile()
    Dim FileSys As Object
    Dim objFile As Object
    Dim myFolder
    Dim strFilename As String
    Dim dteFile As Date
        
    'set path for files - change for your folder
'Set const on next line to your folder path
    Const myDir As String = "C:\Users\Joe\Documents\Visual Basic for Excel"
    
    'set up filesys objects
    Set FileSys = CreateObject("Scripting.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
        'Debug.Print objFile.Name
        If InStr(1, objFile.Name, ".xls") > 0 Then
            If objFile.DateLastModified > dteFile Then
                dteFile = objFile.DateLastModified
                strFilename = objFile.Name
            End If
        End If
    Next objFile
    Workbooks.Open myDir & Application.PathSeparator & strFilename
            
    Set FileSys = Nothing
    Set myFolder = Nothing
End Sub

Changes that need to be made:

• search subfolders
• open last-saved .xls-file, not last modified .xls-file

The difference between the last modified file and the last saved file is that the saved file is new in the folder or in the subfolders. If it finds the last modified file it only finds existing files that were modified (and saved offcourse) but not new files.

Anyone?

 

[RHB1987]

Hi,

I have a code now that I have altered, and it works very good if you are just looking for the last modified .xls-file in a folder and its subfolders.
However, it still does not find the last-saved file in a folder and its subfolders. I mixed two codes together with a little alteration here and there.

If there is someone who can alter the code so it will open the last-saved file that would be very great.

HERE IS THE CODE, BUT IT IS NOT YET AS IT IS SUPPOSED TO BE:

Sub Finding_Last_Modified_File()
Dim fso As Scripting.FileSystemObject
Dim fol As Scripting.Folder
Dim fdr As Scripting.Folder
Dim target As Scripting.file
Dim fls As Scripting.Folders
Dim strFile As String
 
Set fso = CreateObject("Scripting.FileSystemObject")
Set fol = fso.GetFolder("YourFolderHere")
Set fls = fol.SubFolders
dteFile = DateSerial(1900, 1, 1)
 On Error Resume Next
For Each fdr In fls
  For Each target In fdr.files
  
        Debug.Print target.DateLastModified
        If InStr(1, target, ".xls") > 0 Then
            If target.DateLastModified > dteFile Then
                dteFile = target.DateLastModified
                strFile = target
            End If
        End If
  Next target
Next fdr
  Workbooks.Open strFile
Set fso = Nothing
Set fol = Nothing
Set fls = Nothing
End Sub

Anyone, please?