[VBA] Throwing in the towel. Need to open the latest file in a folder based on the file name, and if not there, open the most recent before that.

[RockandGrohl]

Hello. This is driving me nuts and they've removed the partitions in the office so I can't concentrate.

I have a set of files in a folder called "2020 Regional Press Report - Week Commencing [date].xlsx", where the date is in format 2020-02-24 etc (yyyy-mm-dd)

What I need to do is either:

Take the current week commencing date as that is when the report should be ran. In this case it is 2020-02-24. Save that as a string, then push it in to open the report, set as "rpr"

OR

Loop through the folder until it finds the most recent file by filename (not modified, as files can be altered after the fact), then open it.


This process is being completed within a large time-sensitive loop, so speed is definitely a requirement, which is why I am leaning towards the first option, although we may work a week in advance and thus don't have anything available for that week. An example is if we were working a week in advance, this would try to open 2020-03-02, but of course this doesn't exist yet, so in that instance I need it to open "filename - 7", to then go back to 2020-02-24 where it will open the file.

Here is what I have:

Dim Dirfile As String
Dim rpr As Workbook
idp = Format(Date + 7 - Weekday(Date, vbUseSystemDayOfWeek) + 1, "yyyy-mm-dd")

Dirfile = "\\CompanyServer\Weeks\2020\2020 Regional Press Report - Week Commencing " & idp & ".xlsx"

rgt = Right(Split(Dirfile, ".")(0), 10)

fgd = idp - 7 'not working

If Right(Dirfile, 10) <> idp Then
Dirfile = "\\CompanyServer\Weeks\2020\2020 Regional Press Report - Week Commencing " & idp - 7 & ".xlsx"
Set rpr = Workbooks.Open(Dirfile)
Else
Set rpr = Workbooks.Open(Dirfile)
End If

Tearing my hair out here trying to work it out. I feel like I'm doing something criminally wrong because I have a date in "idp" but I can't subtract 7 from it.

 

[MARK858]

I feel like I'm doing something criminally wrong because I have a date in "idp" but I can't subtract 7 from it.

Format turns it into text rather than a date so maybe

fgd = DateValue(idp) - 7
Newfgd = Format(fgd, "yyyy-mm-dd")

Then use Newfgd

 

[JLGWhiz]

Maybe this:

idp Date - Weekday(Date) + 2

 

[RockandGrohl]

Format turns it into text rather than a date so maybe

fgd = DateValue(idp) - 7
Newfgd = Format(fgd, "yyyy-mm-dd")

Then use Newfgd

Hi, this makes a lot of sense. I've changed it around like this:

idp = CLng(Date - Weekday(Date, vbUseSystemDayOfWeek) + 1)

DStr = Format(idp, "yyyy-mm-dd")

StrRPR = "\\CompanyServer\Weeks\2020\2020 Regional Press Report - Week Commencing " & DStr & ".xlsx"
StrTrue = Dir(StrRPR)


If StrTrue = "" Then
StrRPR = "\\CompanyServer\Weeks\2020\2020 Regional Press Report - Week Commencing " & Format(idp - 7, "yyyy-mm-dd") & ".xlsx"
Set rpr = Workbooks.Open(StrRPR)
Else
Set rpr = Workbooks.Open(StrRPR)
End If

This is a better way to check if the file is blank for me using Dir. If it can't find the file, then I check for the same date but minus 7 days.