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 Jerry,

these are the (test) results of the debugging
i = 15
?UBound(vMatches) = 26
?vMatches(i) = C:\Users\RBerendsen\Documents\SAP\abc.xlsx


I understand now why it generated an error. The pattern I used was like "C:\Test\*.xls*"
When I use "C:\Test\a*.xlsx" it only finds Excel-files starting with the "a"-letter, but not files starting with a digit, it leaves it out of the results, because it does not match the pattern.


Isn't it possible to find any Excel-file within the given folder range?
So no distinction between a letter or a digit or a file even beginning with something like an underscore? (_)
And also no distinction between xls or xlsx files?


Regards,
Richard

 

[Jerry Sullivan]

Richard,

You should be able to use a pattern like "C:\Test\*.xls*" to get the results you describe wanting.

If you got the 3 results from the Immediate Window at the point that you received a file not found error at this line...

dModified = FileDateTime(vMatches(i))

...then the problem isn't with the returning of the file matches. It means the error is occurring on trying to get the FileDateTime for "C:\Users\RBerendsen\Documents\SAP\abc.xlsx"

Assuming that file exists at the path, try just testing this expression in the Immediate Window

?FileDateTime("C:\Users\RBerendsen\Documents\SAP\abc.xlsx")

It should return a datetime value. If not, is there something special about that file such as it being a hidden file or containing special characters in the name?

 

[RHB1987]

Jerry,

it generates the same error, but I did a check like you asked me before, with the pattern I used before.
I just did a test and it said:
?i
45
?UBound(vMatches)
5860
?vMatches(i) = C:\Users\RBerendsen\Documents\Rør 09.12.2013.xlsx
?FileDateTime("C:\Users\RBerendsen\Documents\Rør 09.12.2013.xlsx")

It seems to be correct, that it generates an error when encountering a special character in the filename.
The ø probably is not a regular character.

Is it possible to alter the code so it can also search for filenames with special characters?
When I put "On Error Resume Next" in the code, it doesn't generate an error anymore, but that would also mean that I will never be able to open a file which has a special character in the filename when it is the last file that has been saved, using this code.

For i = LBound(vMatches) To UBound(vMatches)
   If vMatches(i) <> "" Then
   On Error Resume Next
      dModified = FileDateTime(vMatches(i))

Regards,
Richard

 

[Jerry Sullivan]

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

 

[RHB1987]

Hi again Jerry,

I copied the whole code and adjusted the "Const sPattern As String"
I was hoping that this was the solution, but unfortunately it still generates an error with me.

The error occurs again here, but now I get a different error:

dModified = FileDateTime(vMatches(i))

It says:
Run-time error '52': Bad file name or number

I have to say that the code goes way over my head.
So I would not be able to make any adaptations for sure, so I am very glad you are taking time to find this out.

I did these tests:
?i = 775
?UBound(vMatches) = 5866
When I do this, ?vMatches(i)
for some reason it puts a question mark before a filename. There is no space, special character before the filename starts.
The filename just starts with: Work.
I put a fictional filename below which is sort of the same as the real filename, but in a different language. No special characters are in the filename.
?vMatches(i) = C:\Users\RBerendsen\Documents\Privat\Privat work\Registration Work\?Work Water Sewer and Draining - 26 and 27 november 2013.xlsx

And why I do this test:
?FileDateTime("C:\Users\RBerendsen\Documents\Privat\Privat work\Registration Work\?Work Water Sewer and Draining - 26 and 27 november 2013.xlsx") it again says: Run-time error '52': Bad file name or number

Any idea why it generated the ? (question mark) before the filename?

Richard

 

[RHB1987]

I do not know if it makes a difference, but this came to mind:
After Registration Work\ the filename starts with a T and then the next word starts with a W in the original filename.
I do not know if this is a coincidence with concern to this part of the pattern:

sCommand = "cmd /u/c dir " & """" & sPattern & """" _
   & " /B /O:D /S [COLOR=#ff0000][B]/T:W[/B][/COLOR]"

 

[Jerry Sullivan]

Can you temporarily move the file that caused the error to another folder just to see if the problem is isolated that one file or several. It looks like the code made it through about 20% of the ~5,000 files that matched the pattern.

It's hard to guess why that error is occurring. We can try some troubleshooting steps once you determine if it's just single file or many.

The /T:W parameter specifies that the time field is based on Last Written date-time (not Created date-time)- so it's just a coincidence the problem file has those letters.

"?" characters aren't valid in Windows filenames, so that character is representing an unknown character instead of a literal "?" in the name.

 

[RHB1987]

Hi Jerry,

I tried testing what you wrote and it seems this is the only file which generates an error.
I tried to test it by renaming the file a couple of times and now I have renamed it back to the filename is was before.
It did not generate an error anymore. So the problem seems to be solved now.
So no idea what raised the error, but it is gone now.

So it seems this code is about 99,98% accurate.

I must say thank you very much for all your effort !!
The code will for sure come in very handy.
And the code works very fast.


Regards,
Richard

 

[Jerry Sullivan]

Glad to hear that worked Richard.

It's possible that file had a no-print character that was removed when you renamed the file and changed it back.

 

[gerhardbrits75]

Hi, this code is great, but it does absolutely nothing when I try to run it.

I am trying to open a xlsx file from a network drive: Is there something that I should enable or change to the code?




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("\\10.30.77.141\SDSScheduleReports\75\DailySOD")
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(".xlsx")) > 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