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

 

[wigi]

What is the difference if I may ask?

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.

 

[RHB1987]

What is the difference if I may ask?

Hi wigi,

The difference is the following: If a file already exists in a folder and is altered and saved, this is DateLastModified
If a file is being written from another program and is saved into a folder, than the file has never been altered before, but only saved.
If you use DateLastModified, it does not open the file that has been last saved into the folder (from a different program) but the file DateLastModified.

I found a different code that might work, but I can only test it tomorrow, at my work. I really hope it will work.
Instead of target.DateLastModified I use now FileDateTime(target)

Debug.Print FileDateTime(target)
        If InStr(1, target, ".xls") > 0 Then
            If FileDateTime(target) > dteFile Then
                dteFile = FileDateTime(target)

You cannot say I haven't been trying to get the code myself, but I do need some help!

 

[wigi]

Yes, that will work. For example:

MsgBox CreateObject("scripting.filesystemobject").getfile(target).datelastmodified

 

[RHB1987]

Yes, that will work. For example:

MsgBox CreateObject("scripting.filesystemobject").getfile(target).datelastmodified

Hi wigi,

sorry, I do not understand what you mean, could you elaborate this a little?

 

[JoeMo]

If you are looking for new files, how about date created? The FileDateTime function returns either the date created or the date last modified. The DateCreated property returns just the date created.

 

[wigi]

Hi wigi,

sorry, I do not understand what you mean, could you elaborate this a little?

Did you try this? You replied only 4 minutes after me...

Target is the path and filename for an existing file (taken from your code).
What the code does, is giving a message box containing the date last modified for that file.
Experiment with this property and then you can build it into your existing code.

 

[RHB1987]

If you are looking for new files, how about date created? The FileDateTime function returns either the date created or the date last modified. The DateCreated property returns just the date created.

Hi Joe,

to be honest, it seems like the FileDateTime and the DateLastModified does exactly the same thing.
It gives e.g. the following information:
DateLastModified: 10/07/2012 10:23:40
FileDateTime: 10/07/2012 10:23:40

And even DateCreated gives the same kind of information:
28/06/2012 11:36:43

So it seems there is no difference between the three different approaches.

I have found out now why my VBA did not find the new files, they end with .XLS and not with .xls.
So the code I use is already good, except for the fact it should find .xls AND .XLS files.

Any ideas?

Please provide code and not suggestions

 

[wigi]

Please provide code and not suggestions

Hello

I would suggest to use the UCase( ) function to convert to uppercase and test files like that.

 

[RHB1987]

Did you try this? You replied only 4 minutes after me...

Target is the path and filename for an existing file (taken from your code).
What the code does, is giving a message box containing the date last modified for that file.
Experiment with this property and then you can build it into your existing code.

Hi, I tried it, but I think the target is empty, so it will generate an error.
I used: On Error Resume Next
If I leave that out, it generates a: Run-time error '70' Permission denied
Probably therefore your code does not work.

The target passed its information on to strFile. Because the date and time has not been stored I added strFileMod like this:

strFileMod = target.DateLastModified

I now use:

MsgBox strFile & " " & strFileMod

But actually I do not need it because I just open the file using

Workbooks.Open strFile

I will try the UCase( ) function as you suggested

 

[JoeMo]

Hi Joe,

to be honest, it seems like the FileDateTime and the DateLastModified does exactly the same thing.
It gives e.g. the following information:
DateLastModified: 10/07/2012 10:23:40
FileDateTime: 10/07/2012 10:23:40

And even DateCreated gives the same kind of information:
28/06/2012 11:36:43

So it seems there is no difference between the three different approaches.

I have found out now why my VBA did not find the new files, they end with .XLS and not with .xls.
So the code I use is already good, except for the fact it should find .xls AND .XLS files.

Any ideas?

Please provide code and not suggestions

You can add:

Option Compare Text

before the Sub .... line