If file not exist in relative path 2023/07 July/ open 2023/06 June

[Josu]

Hello, one wonderful person helped me to figure out how to open folder from relative path
File which I want to open will be in "C:\GENERAL\2023\07 July\ or either another month
Is it any chance to amend this macro to open C:\GENERAL\2023\06 June\ if 07 July is empty or not exist?
Currently I have below

Sub test()

Dim dt As String, dt2 As String
Dim filename As String, strMonth As String
Dim mth As Byte
Dim strMth As String, strPriorMth As String
Dim yr As String
yr = Year(Now())

strMth = MonthName(Month(Now()))
If Month(Now()) = 1 Then
    mth = 1
    strPriorMth = MonthName(12, True)
Else
    mth = Month(Now() - 1)
    strPriorMth = MonthName(Month(Now()) - 1, True)
End If

dt = Format(DateSerial(2023, mth, 1), "mm")
dt2 = Format(DateSerial(2023, mth, 1), "mmmm")

filename = "C:\GENERAL\" & yr & "\" & dt & " " & dt2 & "\" & "Handover*.xlsm"

Debug.Print filename
'Workbooks.Open filename
End Sub

 

[John_w]

You want to open the first file matching "Handover*.xlsm" in either "C:\GENERAL\2023\07 July\" (current month) or "C:\GENERAL\2023\06 June\" (previous month).

Try this:

Public Sub Test2()
   
    Dim basePath As String, fullPath As String
    Dim yearMonth As String
    Dim workbookFileName As String
   
    basePath = "C:\GENERAL\"
   
    'Current year and month
   
    yearMonth = Format(Date, "yyyy\\mm mmmm\\")
    fullPath = basePath & yearMonth
   
    If Dir(fullPath, vbDirectory) = vbNullString Or Dir(fullPath & "Handover*.xlsm") = vbNullString Then
   
        'Path doesn't exist or doesn't contain Handover*.xlsm, so construct path for previous month (and year)
       
        yearMonth = Format(DateAdd("m", -1, Date), "yyyy\\mm mmmm\\")
        fullPath = basePath & yearMonth
       
    End If
   
    workbookFileName = Dir(fullPath & "Handover*.xlsm")
    If workbookFileName <> vbNullString Then
        Workbooks.Open fullPath & workbookFileName
    End If
   
End Sub

 

[Josu]

You want to open the first file matching "Handover*.xlsm" in either "C:\GENERAL\2023\07 July\" (current month) or "C:\GENERAL\2023\06 June\" (previous month).

Try this:

Public Sub Test2()
 
    Dim basePath As String, fullPath As String
    Dim yearMonth As String
    Dim workbookFileName As String
 
    basePath = "C:\GENERAL\"
 
    'Current year and month
 
    yearMonth = Format(Date, "yyyy\\mm mmmm\\")
    fullPath = basePath & yearMonth
 
    If Dir(fullPath, vbDirectory) = vbNullString Or Dir(fullPath & "Handover*.xlsm") = vbNullString Then
 
        'Path doesn't exist or doesn't contain Handover*.xlsm, so construct path for previous month (and year)
     
        yearMonth = Format(DateAdd("m", -1, Date), "yyyy\\mm mmmm\\")
        fullPath = basePath & yearMonth
     
    End If
 
    workbookFileName = Dir(fullPath & "Handover*.xlsm")
    If workbookFileName <> vbNullString Then
        Workbooks.Open fullPath & workbookFileName
    End If
 
End Sub

Thank you, this macro finding correct path, but by some reason not opening file Just in case *is wildcard in file name

 

[Josu]

Thank you, this macro finding correct path, but by some reason not opening file Just in case *is wildcard in file name

Ignore this, there was error in month, it was 7 instead of 07
I changed mm to m and it's working.
Thank you very much again! Now I will try to add checks for last modified