Macro to open "Newest" Created Folder??

erock24

Well-known Member
Joined
Oct 26, 2006
Messages
1,163
I've been working on a macro that opens me up to a certain path so I can select a file.

This is the path sofar:

Code:
TheFile = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1"

My code takes me there, but I want to go a step further if it is possible.

\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\THE NEWEST CREATED FOLDER"

Is this even possible?
Thanks for your time and help
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
After liberally pulling code from the VBA help system, I've put together a way to do what you are asking:

Insert the following two functions into your code:
Code:
Function ReturnNewestFolder(mypath)
' Display the names in C:\ that represent directories.
myname = Dir(mypath, vbDirectory)    ' Retrieve the first entry.
NewestfolderName = "" 
Newestfolderdate = ""
Do While myname <> ""    ' Start the loop.
    ' Ignore the current directory and the encompassing directory.
    If myname <> "." And myname <> ".." Then
        ' Use bitwise comparison to make sure MyName is a directory.
        If (GetAttr(mypath & myname) And vbDirectory) = vbDirectory Then
           If NewestfolderName = "" Then
             NewestfolderName = myname
             Newestfolderdate = ShowFolderInfo(mypath & myname)
           ElseIf ShowFolderInfo(mypath & myname) > Newestfolderdate Then
             NewestfolderName = myname
             Newestfolderdate = ShowFolderInfo(mypath & myname)
           End If
         End If
    End If
    myname = Dir    ' Get next entry.
Loop
ReturnNewestFolder = NewestfolderName
End Function

Function ShowFolderInfo(folderspec)
    Dim fs, f
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    ShowFolderInfo = f.DateCreated
End Function

You then call the function ReturnNewestFolder() with a statement such as:
(note: Path must end with a \)

Code:
NewestFolderInThePath = ReturnNewestFolder(Path)

Thus the variable 'NewestFolderInThePath" will contain the name of the newest folder in the path you sent to the function.

You could build to your final path by concatenating the output to the function with the path you sent in:

Code:
FinalPath = Path & NewestFolderInThePath

Hope this helps.

Owen
 
Upvote 0
WoW!!!

Holy crap that works!!!

Code:
NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
    
    TheFile = Path & NewestFolderInThePath
    CreateObject("WScript.Shell").CurrentDirectory = TheFile
    
    TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Select the file and choose open.")

Opens me up to to the newest folder in path. Very, very cool. thank you.
 
Upvote 0
well, it was working, but now .. all of a sudden.

it is erroring out highlighting in yellow this saying "method CurrentDirectory of object IWshShell3 failed"??

why would it work but now it doesn't

Code:
CreateObject("WScript.Shell").CurrentDirectory = TheFile

here is larger sample of macro:

Code:
Sub Copy()
Dim myReturn&
myReturn = MsgBox("Do you want to do prior year downloads?", _
                    vbInformation + vbYesNo, _
                    "Prior Year Downloads?")
If myReturn = 6 Then GoTo PriorYear

myReturn = MsgBox("Do you want to do Monthly Balance Sheet?", _
                    vbInformation + vbYesNo, _
                    "Monthly Balance Sheet?")

If myReturn = 7 Then GoTo SkipMonthly

Dim OpenA As Workbook, OpenB As Workbook
    Dim TheFile As String
    Dim TheFile2 As String
    
    NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
    
    TheFile = Path & NewestFolderInThePath
    CreateObject("WScript.Shell").CurrentDirectory = TheFile
    
    TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Select the file and choose open.")
    If TheFile = "False" Then
        Exit Sub
    End If
    Workbooks.Open Filename:=TheFile
     
    Set OpenA = Workbooks(Workbooks.Count)
 
Upvote 0
Erock,

I am not certain why it was working and is now failing. You can attempt to use some debugging to see where the failure is occurring. I would start by checking to see if the variable 'TheFile' is correctly formated. You could place the following code just prior to the line of code that is failing:
Code:
MsgBox (TheFile)

This will display a message box with the variable 'TheFile' shown. You can then see if it is formated correctly.

That is all I can think of to assist you at this time.

Take care.

Owen
 
Upvote 0
When I put the
Code:
MsgBox (TheFile)
the msg box gives me the name of the folder that is most recent. So, it seems that is working.

Weird thing is that I Made a new folder then tested macro once and it worked. It opened me up in the new folder - most recent. Then I tried to run macro again and it errored out. So, macro will work and open me up to most recent file only once then it won't work anymore. Any Ideas anyone.

Thanks for your time and help.
 
Upvote 0
Erock,

I looked at this again and ran your code on my system to attempt to isolate the problem. When I ran your code and it errored on me. I then noticed that you have not defined the variable 'Path' in your code. When I defined 'Path' the code worked fine. Here is the code I used:

Code:
Sub Test()


Dim OpenA As Workbook, OpenB As Workbook
    Dim TheFile As String
    Dim TheFile2 As String
    
    Path = "C:\Documents and Settings\My.Computer\Desktop\"
    NewestFolderInThePath = ReturnNewestFolder(Path)
    TheFile = Path & NewestFolderInThePath
    CreateObject("WScript.Shell").CurrentDirectory = TheFile
    TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Select the file and choose open.")
    If TheFile = "False" Then
        Exit Sub
    End If
    Workbooks.Open Filename:=TheFile
    Set OpenA = Workbooks(Workbooks.Count)
End Sub

Hope you find a solution!

Owen
 
Upvote 0
Got it. Seems to be working great now.

Code:
 NewestFolderInThePath = ReturnNewestFolder("\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\")
   Path = "\\datawhse\root\LAW81\LAWSON\print\RBLEVINS2\anrvwfins\1\"
    
    TheFile = Path & NewestFolderInThePath
    CreateObject("WScript.Shell").CurrentDirectory = TheFile
    
    TheFile = Application.GetOpenFilename("Excel Files (*.*), *.*", , "Select the file and choose open.")

Thank very much for all your time and help.
 
Upvote 0
Macro works great, but I was wondering if it could be brought to the next level.
Can it open the newest created file in the newest created folder? Is this possible?

Thank you very much for your time and help.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

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