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

 

[Kyle123]

Thanks VoG, I've tried posting similar code on other forums and had problems

 

[RHB1987]

Thanks VoG, I've tried posting similar code on other forums and had problems

Sub test()
Debug.Print Split(CreateObject("wscript.shell").exec("cmd /c dir C:\My Documents\*.xls* /T:W /O:-D /b /s /l ").stdout.readall, vbCrLf)(0)
End Sub

Thanks for the code, but it does not work for me.
Should it do exactly the same thing as the correct code previously posted?
I see that it opens a screen, but before it's opened it's already closed.
Does it work for you?

 

[RoryA]

It outputs a result to the Immediate Window in the VBEditor.

 

[Kyle123]

Try:

Sub test()
Workbooks.Open Split(CreateObject("wscript.shell").exec("cmd /c dir C:\My Documents\*.xls* /T:W /O:-D /b /s /l ").stdout.readall, vbCrLf)(0)
End Sub​

 

[RHB1987]

Try:

Sub test()
Workbooks.Open Split(CreateObject("wscript.shell").exec("cmd /c dir C:\My Documents\*.xls* /T:W /O:-D /b /s /l ").stdout.readall, vbCrLf)(0)
End Sub​

Hi,

thank you. The code works, but the code does not search the subfolders, but only the main folder.
Can it altered so it will also search the subfolders?

It is possible to explain which things in the code do what? Because it cannot make anything of it.
/T:W means?
/O means?
/b means?
/s means?
/l means?
stdout.readal means?
vbCrLf)(0) means?

 

[RHB1987]

Richard,

Files straight into C:Wim will be left out, files in subfolders will be processed OK.
Don't know if that's material to you now.

Hi Wigi,

I did think about this for some time, without really doing something with it, but I have thought about that it is best to check the folder first and then the subfolders. Then compare the two files and open the last modified file.
So now I can really say that I can find the last modified file in a folder and its subfolders. In this code it off course only checks Excel files, but anyone can modify it so it matches their needs. This works perfectly for me. I hope also others can appreciate this code.

Sub Finding_Last_Modified_File()
Dim fdr, fdr2 As Scripting.Folder
  Dim target As Scripting.file
    For Each fdr In CreateObject("Scripting.FileSystemObject").GetFolder("C:\Users\RBerendsen").SubFolders
        On Error Resume Next
        For Each target In fdr.files
            If InStr(1, UCase(target), UCase(".xls")) > 0 Then
                If target.DateLastModified > dteFile Then
                    dteFile = target.DateLastModified
                    strFile = target
                End If
            End If
        Next
    Next
    DateFirstFile = dteFile
    FirstFile = strFile
    For Each fdr2 In CreateObject("Scripting.FileSystemObject").GetFolder("C:\Users\RBerendsen\Documents").SubFolders
        On Error Resume Next
        For Each target In fdr2.files
            If InStr(1, UCase(target), UCase(".xls")) > 0 Then
                If target.DateLastModified > dteFile Then
                    dteFile = target.DateLastModified
                    strFile = target
                End If
            End If
        Next
    Next
    DateSecondFile = dteFile
    SecondFile = strFile
    If DateFirstFile >= DateSecondFile Then
    If Len(Dir(FirstFile)) Then Workbooks.Open FirstFile
    ElseIf DateSecondFile >= DateFirstFile Then
    If Len(Dir(SecondFile)) Then Workbooks.Open SecondFile
    End If
End Sub

Sincerely,
Richard

 

[wigi]

Thanks for posting Richard, the community will be grateful.

 

[Jerry Sullivan]

This thread was referenced by a recent question
...and I found Kyle's suggestion to be an interesting alternative to using VBA to step through folders and subfolders. His linked code is reposted by Peter in Post #30.

I think the reason this didn't find the latest file for Richard is that the Dir function with /O:D switches sorts the matches for each subfolder to put the newest on top of each group, but it doesn't put the newest match of all subfolders on top.

Here's a function that uses the Dir through shell approach to return all matches, then uses VBA to find the newest.

Function GetLastFile(sPattern As String) As String
'--returns path of the file meeting the pattern
'     with the newest modified date
Dim vMatches As Variant
Dim i As Long
Dim dModified As Double, dLastModified As Double
Dim sCommand As String

sCommand = "cmd /c dir " & """" & sPattern & """" _
   & " /B /O:D /S /T:W"

vMatches = Split(CreateObject("wscript.shell").exec( _
   sCommand).stdout.ReadAll, vbCrLf)

For i = LBound(vMatches) To UBound(vMatches)
   If vMatches(i) <> "" Then
      dModified = FileDateTime(vMatches(i))
      If dModified > dLastModified Then
         dLastModified = dModified
         GetLastFile = vMatches(i)
      End If
   End If
Next i

End Function

Here's an example of how the function could be called....

Sub TestGetLastFile()
   Const sPattern As String = "C:\Test\a*.xlsx"
   Dim sReturn As String
   
   sReturn = GetLastFile(sPattern)
   
   If sReturn = vbNullString Then
      MsgBox "No files found matching pattern: """ & _
         sPattern & """"
   Else
      MsgBox "The newest file matching pattern: """ & _
         sPattern & """ was found at: " & vbCr _
         & sReturn
   End If
End Sub

The command window pops up momentarily during the execution of that code.
If you prefer to eliminate that, you could replace the vMatches assignment with this

vMatches = Split(fShellRun(sCommand), vbCrLf)

...then add the fShellRun function below.
I found this unattributed code at this Link. There might be better methods of hiding the command window.

Function fShellRun(sCommandStringToExecute)

' This function will accept a string as a DOS command to execute.
' It will then execute the command in a shell, and capture the output into a file.
' That file is then read in and its contents are returned as the value the function returns.

Dim oShellObject, oFileSystemObject, sShellRndTmpFile
Dim oShellOutputFileToRead, iErr

Set oShellObject = CreateObject("Wscript.Shell")
Set oFileSystemObject = CreateObject("Scripting.FileSystemObject")

    sShellRndTmpFile = oShellObject.ExpandEnvironmentStrings("%temp%") & oFileSystemObject.GetTempName
    On Error Resume Next
    oShellObject.Run sCommandStringToExecute & " > " & sShellRndTmpFile, 0, True
    iErr = Err.Number

    On Error GoTo 0
    If iErr <> 0 Then
        fShellRun = ""
        Exit Function
    End If

    On Error GoTo err_skip
    fShellRun = oFileSystemObject.OpenTextFile(sShellRndTmpFile, 1).ReadAll
    oFileSystemObject.DeleteFile sShellRndTmpFile, True

Exit Function

err_skip:
    fShellRun = ""
    oFileSystemObject.DeleteFile sShellRndTmpFile, True

End Function

 

[RHB1987]

Here's an example of how the function could be called....

Sub TestGetLastFile()
   Const sPattern As String = "C:\Test\a*.xlsx"
   Dim sReturn As String
   
   sReturn = GetLastFile(sPattern)
   
   If sReturn = vbNullString Then
      MsgBox "No files found matching pattern: """ & _
         sPattern & """"
   Else
      MsgBox "The newest file matching pattern: """ & _
         sPattern & """ was found at: " & vbCr _
         & sReturn
   End If
End Sub

Dear Jerry,

thank you very much for posting this code.
I do not know if the code works for you, and if it is completely tested, but I get a Run-time error '53': File not found.
The error comes up with this line:

dModified = FileDateTime(vMatches(i))

I tried both this:

vMatches = Split(CreateObject("wscript.shell").exec( _
   sCommand).stdout.ReadAll, vbCrLf)

as well as:

vMatches = Split(fShellRun(sCommand), vbCrLf)

But I received an error on the same line.

With regard to the posting I did previously. It is correct that the code I eventually posted only looks through the main folder and then only the subfolders, but not in the subfolders of the subfolders.
That is sufficient for me, usually. I did not think it would really add anything to post that. If someone is interested in this code, they would certainly find out by just trying.

But if you have the possibity to check the error I received, so the code would really work (for me), that would be great.
Sometimes I do need to look in the subfolders of the subfolder.
I usually do know in which folder a file should be found, so I just use the same code, but to look into a different folder if the first code does not find what I expected.

Regards,

Richard

 

[Jerry Sullivan]

Hi Richard,

The code worked for me for the couple tests I ran.

When the code errors and stops at the debugger, enter these each of these expressions separately in the Immediate Window (Ctrl-G) of the VB Editor to get some additional information to help trouble-shoot the problem.

?i
?UBound(vMatches)
?vMatches(i)

Also, what value did you assign to sPattern as the pattern to match?