Hi guys
I have to questions regarding a dynamic file path in VBA.
Code
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
Display More
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!