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

 

[netuser]

Richard, Looking into this further, the problem is that the wshell cmd as written will not return extended characters.

One solution would be to use the approach of the fShellRun function to write and read the results of a DIR call using Unicode. The modifications are shown in blue font. I've also renamed the function to fShellRunUnicode to distinguish it from the original source.

Sub TestGetLastFile()
   Const sPattern As String = "C:\Test\*.xls*"
   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

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 [B][COLOR="#0000CD"]/u[/COLOR][/B]/c dir " & """" & sPattern & """" _
   & " /B /O:D /S /T:W"

vMatches = Split(fShellRunUnicode(sCommand), 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

Function fShellRunUnicode(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 as Unicode 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
        fShellRunUnicode = ""
        Exit Function
    End If

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

Exit Function

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

End Function

I was looking for exactly something like this. All work fine except that I cant use any variable for pattern the line

Const sPattern As String = FromPath & Partialname & "*" & ".csv"

It give me Compilation error , but if I use same path and name directly in it, it works. How can use the variable please

I store path in FromPath variable and Partial Name that is part of file name to search in Partialname .

Thanks

 

[Jerry Sullivan]

This statement declares a Constant String data type and assigns a value to it....

Const sPattern As String = FromPath & Partialname & "*" & ".csv"

Constants can't be assigned variable values, so you'll either need to declare FromPath and Partialname as Constants (if their value is fixed); or make sPattern a variable like this....

Dim sPattern As String
sPattern = FromPath & Partialname & "*" & ".csv"

 

[netuser]

This statement declares a Constant String data type and assigns a value to it....

Const sPattern As String = FromPath & Partialname & "*" & ".csv"

Constants can't be assigned variable values, so you'll either need to declare FromPath and Partialname as Constants (if their value is fixed); or make sPattern a variable like this....

Dim sPattern As String
sPattern = FromPath & Partialname & "*" & ".csv"

Thanks for quick reply, i will test it tomorrow and let you know. Path is not fix that is why i need the variable, the path change depending on the username of the user.

 

[netuser]

Work perfectly thanks Jerry