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

RHB1987

New Member
Joined
Dec 9, 2010
Messages
34
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.

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
 
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.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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)

Code:
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!
 
Upvote 0
Yes, that will work. For example:

Code:
MsgBox CreateObject("scripting.filesystemobject").getfile(target).datelastmodified
 
Upvote 0
Yes, that will work. For example:

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

Hi wigi,

sorry, I do not understand what you mean, could you elaborate this a little?
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0
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 :(
 
Upvote 0
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:
Code:
strFileMod = target.DateLastModified
I now use:
Code:
MsgBox strFile & " " & strFileMod
But actually I do not need it because I just open the file using
Code:
Workbooks.Open strFile

I will try the UCase( ) function as you suggested
 
Upvote 0
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:
Code:
Option Compare Text
before the Sub .... line
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top