Show Last file in a folder in Alphabetical order.

[dimer]

Hi All,

I have script below that create msgbox after msgbox with a file name of the files in the folder, one by one, in alphabetical order.


Sub Macro123()
Set obFSO = CreateObject("Scripting.FileSystemObject")
Set mFolder = obFSO.GetFolder("C:\Temp\")

'temp = mainFolder.Files.Count
For Each MFile In mFolder.Files
MsgBox MFile.Name
Next

Set obFSO = Nothing
Set mFolder = Nothing

End Sub


if I use temp = mainFolder.Files.Count
And Count returns number 5 - meaning there are 5 files in the folder.

My question is: How I can using Msgbox to show only the last file?
I tryed something like MsgBox MFile(temp).Name but it does not work

Please help,

 

[Trebor76]

I dare say there's probably a way to reference the last file by it's count in a directory, though this will do the same (albeit less efficiently):

Option Explicit
Sub Macro2()

    Dim objFSO As Object, _
        objFSOFolder As Object
    Dim varFile As Variant, _
        varFileLast As Variant

    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objFSOFolder = objFSO.GetFolder("P:\")
    
    For Each varFile In objFSOFolder.Files
        varFileLast = varFile
    Next varFile
    
    MsgBox varFileLast 'Filename and path
    MsgBox objFSO.GetFileName(varFileLast) 'Filename only
    
    Set objFSO = Nothing
    Set objFSOFolder = Nothing

End Sub

HTH

Robert

 

[dimer]

Thank you Robert,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Your solution works perfectly no questions, and I will use it for time being. BIG THANK YOU.
<o></o>
But, I still think, running For Next operator is not that efficient as just referring to the last file.<o></o>

 

[Trebor76]

But, I still think, running For Next operator is not that efficient as just referring to the last file

Agreed - but, like you, I couldn't find a way to open the file in that manner.