Getting a macro to open the most recent folder and then the most recent file Wha

Question

Getting a macro to open the most recent folder and then the most recent file

What I need to do is get a macro to pick a folder that has the most recent year as the title, then have the macro pick the most recent month folder, than to have it pick the most recent file that has todays date minus one. I currently have it picking up the most recent file from a path but I dont want to want to change or update the macro every month to get the macro to grab the most recent file. I have below what i have been using to grab the most recent file minus one day from another source.

    Dim dtTestDate As Date
    Dim sStartWB As String
   
    Const sPath As String = "J:carsTypesBasketball�48-August-2014

    Const dtEarliest = #1/1/2010# '--to stop loop if file not found by earliest valid date.
   
   
    dtTestDate = Date
    sStartWB = ActiveWorkbook.Name
   
    While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest
        On Error Resume Next
        Workbooks.Open sPath & Format(dtTestDate - 1, "MM-DD-YYYY") & " basketball" & ".xlsx"
        dtTestDate = dtTestDate - 1
        On Error GoTo 0
    Wend
   
    If ActiveWorkbook.Name = sStartWB Then MsgBox "Earlier file not found."

Explanation / Answer

'----
Sub FindMeOneFile()
'Jim Cone - Portland, Oregon USA - August 2014
Dim strLocation As String
Dim strFileName As String
Dim strFolderPartName As String

'specify the top folder
strLocation = "C:Documents and SettingsInmate NameMy Documents" & _
                "My PicturesMicrosoft Clip OrganizerMy Clip Art"
              
'Specify the folder name
'(any characters wild cards used: "*")
strFolderPartName = "*ART*"

'Specify the file name
'(single character wildcard used: "?")
strFileName = "J023278?.WMF"

'Call function
MsgBox FindFileInSubFolder(strLocation, strFileName, strFolderPartName) & " "
End Sub
'---

Private Function FindFileInSubFolder(ByRef strPath As String, _
           ByRef strName As String, ByRef strFolder As String) As String
'Jim Cone - Portland, Oregon USA - April 2006
'Requires project reference to "Microsoft Scripting Runtime" library      <<<
'Determines file a path if only a partial folder/file name is known.
On Error GoTo ScriptErr
Dim objFSO       As Scripting.FileSystemObject
Dim objSubFolder As Scripting.folder
Dim objFolder    As Scripting.folder
Dim objFile      As Scripting.file

'Bring it to life...
Set objFSO = New Scripting.FileSystemObject

'Check for top folder
On Error Resume Next
Set objFolder = objFSO.GetFolder(strPath)
If Err.Number <> 0 Then
    FindFileInSubFolder = "No Top Folder:"
    GoTo FinishUp
End If
On Error GoTo ScriptErr

For Each objSubFolder In objFolder.SubFolders
   'Verify secondary folder contains the specified characters.
    If objSubFolder.Name Like strFolder Then
     'Does file exist in the secondary folder...
      For Each objFile In objSubFolder.Files
       If objFile.Name Like strName Then
         FindFileInSubFolder = _
         "Path is " & objSubFolder.Path & Application.PathSeparator & objFile.Name
         GoTo FinishUp
       End If
      Next 'objfile
    End If
Next 'objsubfolder
FindFileInSubFolder = "Secondary Folder/File Not Found "

FinishUp:
On Error Resume Next
'whatever is needed
Exit Function
ScriptErr:
FindFileInSubFolder = "Error " & Err.Number & " " & Err.Description
GoTo FinishUp
End Function
'----

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.