I have to questions regarding a dynamic file path in VBA.
Dim dtTestDate As Date Dim sStartWB As String Const sPath As String = "X\XXX\XXX\XXX\XXX\XXX\XXX\Querys\" Const dtEarliest = #8/5/2013# '--to stop loop if file not found by earliest valid date dtTestDate = Date sStartWB = ActiveWorkbook.Name '--add this to suppress error "The Internet address... is not valid" Application.DisplayAlerts = False While ActiveWorkbook.Name = sStartWB And dtTestDate >= dtEarliest On Error Resume Next Debug.Print "Trying to open: " & _ sPath & "File_Name_" & Format(dtTestDate, "dMMMyy") & ".xlsx" Workbooks.Open sPath & Format(dtTestDate, "YYYY") & "\" & Format(dtTestDate, "YYYYMMDD") & "_Fondsen per klasse" & ".xlsx" dtTestDate = dtTestDate - 1 On Error GoTo 0 Wend
This code loops through the folder Queries and opens the youngest .xlsx file.
My first question:
I think I made a dynamic folder in the Queries folder. The folders in queries are "2013" and "2014". For now it loops only through "2014" and opens the youngest file in "2014". I created a "2015" and tested whether it works for 2015 but it doesn't work in that folder. Is that because 2015 is in the future? For 2014 it works fine!
My second question:
Sometimes files are in .xls instead of .xlsx. Maybe sometimes .xlsm. How should I add this code to open multiple extensions?
Thank you in advance!