asked on

VB Script: Drill down in order to get the most recent folder in a Root folder and most recent file in the folder

Hello experts,

I have a root folder C:\Root which contains multiple folder, I am looking for a procedure in order to;
1-Drill down root folder and get the most recent folder based on creation date (with wscript.echo.FolderName).
2-Once the most recent folder is found get the most recent file based on creation date in the most recent folder(with wscript.echoFileName).

The drill down process will be just done in C:\Root folder level.

Thank you very much for your help.

ASKER CERTIFIED SOLUTION

Rgonzo1971

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

Tested and it works.

I added my final script used in which I copy most recent folder to a destination folder.

Thank you again for your help.

' ==============================================================================
' C O N S T A N T S   &   V A R I A B L E S
' ==============================================================================

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8
Const TriStateUseDefault = -2



' Specify path to folder of files to process and log file

WorkingDir = Replace(WScript.ScriptFullName, WScript.ScriptName, "")
strLogFile = WorkingDir & "\log-file.txt"
strStamp = TimeStamp(Now)

' ==============================================================================
' I N I T I A L I Z A T I O N
' ==============================================================================

' Create filesystem object
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Open log file (append)
Set objLogFile = objFSO.OpenTextFile(strLogFile, ForAppending, True)

' ==============================================================================
' M A I N   L O G I C
' ==============================================================================


CopyMostRecentFolder "F:\2-Files-Storage-Reference\4.Script-templates\1.Scripts\2.VBS-BAT\2.1Reference-Folders\_copy-most-recenter-folder\Source", _ 
"F:\2-Files-Storage-Reference\4.Script-templates\1.Scripts\2.VBS-BAT\2.1Reference-Folders\_copy-most-recenter-folder\Destination"

' ==============================================================================
' W R A P U P
' ==============================================================================

' Done, cleanup and exit
objLogFile.Close
'objInputFile.Close
Wscript.Quit


' ==============================================================================
' S U B R O U T I N E S   &   F U N C T I O N S
' ==============================================================================

' Build string of current date time (DDMMYYYY_HHMMSS)
Function TimeStamp(dtmDateTime)
   TimeStamp = Year(dtmDateTime) & Right("0" & Month(dtmDateTime), 2) & Right("0" & Day(dtmDateTime), 2) & "_" & Right("0" & Hour(dtmDateTime), 2) & Right("0" & Minute(dtmDateTime), 2) & Right("0" & Second(dtmDateTime), 2)
End Function



' ==============================================================================
' COPY most Recent folder into a destination folder
' ==============================================================================

Sub CopyMostRecentFolder(srootFolder,sdestinationFolder)


Set recentFldr = Nothing
Set destinationFolder = objFSO.GetFolder(sdestinationFolder)

For Each fldr In objFSO.GetFolder(srootFolder).subfolders
  If (recentFldr Is Nothing) Then
    Set recentFldr = fldr
  ElseIf (fldr.DateCreated > recentFldr.DateCreated) Then
    Set recentFldr = fldr
  End If
Next
'Set recentFile = Nothing
'For Each file In recentFldr.Files
'  If (recentFile Is Nothing) Then
'    Set recentFile = file
'  ElseIf (file.DateCreated > recentFile.DateCreated) Then
'    Set recentFile = file
'  End If
'Next
'WScript.Echo "Recent file is " & recentFile.Name & " " & recentFile.DateLastModified

objFSO.CopyFolder recentFldr,destinationFolder & "\" & recentFldr.Name ,True
objLogFile.WriteLine Now & " RecentFolder "  & recentFldr & " copied to " & destinationFolder 

End Sub

Open in new window

Luis Diaz

ASKER

Last question if I want to copy the most recent file into an specific sheet in excel I now that we can use Microsoft Scripting runtime.
What Should I add in order to open the most recent file select all the content and copy it in a reference worksheet?

Thank you again for your help.