[Request] Macro to open latest spreadsheet in latest folder

[notphu]

I tried searching the forum for this, but I have only found macros to open the latest file in a specific folder.

Would it be possible to open the latest spreadsheet in the latest folder?
Here is what I mean:

Folder 20131002
File 20131001
File 20131002

Folder 20131101
File 20131031
File 20131101 <- The file I want to open.

The spread sheet that I want to open is the "File 20131101".
It is the latest spreadsheet in the latest folder.

So today, November 7, 2013, there will be a new folder and new files:

Folder 20131107
File 20131106
File 20131107 <- This is the file I want to open today.

Edit
The Folder might not be named with today's date.
Today's folder might be "Folder 20131105".
Edit

Thank You for your help.

 

[DeBeuz]

Hi,

First loop over all Foldernames with Dir("Folder *",vbDirectory) and repeated Dir().
Replace the LastFolder with the new foldername if it is bigger (If Fldr > LastFolder Then LastFolder = Fldr).
Now you should know the latest folder (based on the provided examples).

Next loop over all files in LastFolder with Dir(LastFolder & "\File *",vbNormal) and Dir(), also replacing LastFile if current name is bigger.
At the end of the loop you should have the latest file an latest folder.

Does this help you on your way?

Paul

 

[notphu]

Hi Paul,

I don't really understand, but I'll search for ways to open folders and files and use this to try to make a Macro. Thank You for pointing me in the right direction.

 

[DeBeuz]

Hi,

Try something like this

Option Explicit

Sub Test()
    Dim LastFile As String
    Dim LastFolder As String
    Dim Fldr As String
    Dim Wrk As String
    
    'Loop over all foldernames
    LastFolder = ""
    Wrk = Dir("Folder *", vbDirectory)
    While Wrk <> ""
        If Wrk > LastFolder Then LastFolder = Wrk
        Wrk = Dir()
    Wend
    
    'Loop over all files in LastFolder
    LastFile = ""
    Wrk = Dir(LastFolder & "\File *", vbNormal)
    While Wrk <> ""
        If Wrk > LastFile Then LastFile = Wrk
        Wrk = Dir()
    Wend
    
    'Ok we've got is
    'Open LastFolder & "\" & LastFile
End Sub

I did not test it, but it gives you an idea what I'm point you at.

Succes,

Paul

 

[notphu]

Wow, thank you so much, Paul!
I'll play around with this today.