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

 

[RHB1987]

Hi,

thanks to wigi and Joe and myself and others providing the original code this code works for me!

You can also use it for different files such as .csv or .xlsx or .txt, whatever.

Thank you all!

The code:

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
Dim strFileMod 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, UCase(".xls")) > 0 Then
            If target.DateLastModified > dteFile Then
                dteFile = target.DateLastModified
                strFile = target
                strFileMod = target.DateLastModified
            End If
        End If
  Next target
Next fdr
'MsgBox strFile & " " & strFileMod
  Workbooks.Open strFile
Set fso = Nothing
Set fol = Nothing
Set fls = Nothing
End Sub

You could leave the following code out, this is only if you want to use the Messagebox.
Then delete the ' in front of MsgBox and put it in front of Workbooks.Open

Dim strFileMod As String
strFileMod = target.DateLastModified
'MsgBox strFile & " " & strFileMod

I do not know how to set this posting to SOLVED, but it is.

 

[RHB1987]

You can add:

Option Compare Text

before the Sub .... line

I do not know that option.
Could you give me more code?
Where would you put this and what would be in it?

Before the Sub line?

I am not an expert at coding

Ucase unfortunately does not work like I thought it would

 

[wigi]

Please inspect the code that was given to you.
The very first line is Sub ...
So Option Compare Text needs to go before that first line.

 

[wigi]

I would suggest to have a look at the code below.
It contains a number of improvements over your code, as well as being a lot less long-winded.
In addition, some checks too.

Sub Finding_Last_Modified_File()    Dim fdr As Scripting.Folder
    Dim target As Scripting.file
    Dim strFile As String
    For Each fdr In CreateObject("Scripting.FileSystemObject").GetFolder("C:\Wim").SubFolders
        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
    If Len(Dir(strFile)) Then Workbooks.Open strFile
End Sub

Note that your code does not process files in the root folder (so not files in the subfolders but directly in the root). My code also does not do that BTW.

 

[RHB1987]

I would suggest to have a look at the code below.
It contains a number of improvements over your code, as well as being a lot less long-winded.
In addition, some checks too.

Sub Finding_Last_Modified_File()    Dim fdr As Scripting.Folder
    Dim target As Scripting.file
    Dim strFile As String
    For Each fdr In CreateObject("Scripting.FileSystemObject").GetFolder("C:\Wim").SubFolders
        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
    If Len(Dir(strFile)) Then Workbooks.Open strFile
End Sub

Note that your code does not process files in the root folder (so not files in the subfolders but directly in the root). My code also does not do that BTW.

Hi,

I just added On Error Resume Next, so it will not generate Run-time error '70' Permission denied.
This might also be a reason why I cannot find a certain .xls file that I have saved last.
The file probably generates an error and therefore the code cannot find it.

Thank you for making the code shorter and faster. Len is probably the fastest method to use.
I am not so good at coding but I am glad this code works now.

Note that your code does not process files in the root folder (so not files in the subfolders but directly in the root).
My code also does not do that BTW.

What is the disadvantage to this kind of searching? I
s it possible it will not find certain files in the subfolders or do you mean that it will not find any files in C:Wim?

The xls-files it found with me are found in a subfolder of a subfolder of a subfolder, so it seems to check all the folders.

One funny thing, because you mentioned UCase and it didn't work for me, I used LCase(target), LCase(".xls")), that off course also works.

Sincerely,
Richard

 

[wigi]

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.

 

[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.

Ok, thanks. Usually the files are written in the subfolder, so this is not a problem.
If you however have an idea how to fix this, I would appreciate that.

 

[Kyle123]

How's this for an alternative, should be a bit quicker and it's only one line, thinking a bit outside the box though

I'm guessing the forum won't automatically let me post this code - and I don't fancy getting locked out, so there's a link to it here:
http://pastebin.com/raw.php?i=A6mxiVVH

 

[RHB1987]

How's this for an alternative, should be a bit quicker and it's only one line, thinking a bit outside the box though

I'm guessing the forum won't automatically let me post this code - and I don't fancy getting locked out, so there's a link to it here:
http://pastebin.com/raw.php?i=A6mxiVVH

Hi, I do not trust this, so I reported this posting

 

[VoG]

Hi, I do not trust this, so I reported this posting

Well this is the code

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