VBA loop to open most recent day's excel workbook

**richter394** · 01-13-2014, 08:05 PM

So I have a VBA code to open a workbook saved with yesterday's date. It looks like the following:

Sub OpenYesterday
Dim OpenPath As String

OpenPath = "N:\File\": & "today_" (Now(),-1, "YYYYMMDD") & ".xlsx"

Workbooks.Open Filename:=OpenPath
EndSub

Obviously I have my full filepath in the code but how can I loop this so it will open the last workbook that has been saved? For example, on Monday I would want it to pull Friday's report. I save a workbook every weekday with that days date. Example: Today is 1/13/2014 and I want this code to open the workbook that was saved on Friday 1/10/2014. I have tried the "WorkDay" function which works as well but lets say I didn't save a workbook today (1/13/2014) so tomorrow the last workbook would still be Friday's 1/10/2014 workbook. Is there a way to loop this code so that it will continually look for yesterday's workbook and if there is not a workbook from yesterday it would loop back two days until the 1/11/2014 workbook, then if that isn't saved it would loop back to the 1/10/2014 workbook? So tomorrow if there is no workbook saved with today's 1/13/2014 workbook it would loop 3 times to find the workbook saved on 1/10/2014?

Sorry if this is confusing, let me know if you need further explanation.

Thank you.

Im thinking I will just need it to loop back to "Now(), -2" if there is no workbook from yesterday, and "Now(), -3" if there is no report from 2 days ago, etc...

**Kenneth Hobson** · 01-13-2014, 08:23 PM

If the last created filename in a folder is needed, the code is even easier.

Sub ken()
  Dim i As Integer, OpenPath As String
  For i = 1 To 31
    OpenPath = "N:\File\" & "today_" & Format(Date - i, "YYYYMMDD") & ".xlsx"
    If Dir(OpenPath) <> "" Then Exit For
  Next i
  
  If Dir(OpenPath) = "" Then
    MsgBox "Oops, macro ending as no file can be found within 31 days."
    Exit Sub
  End If
  
  ' your code
End Sub

**richter394** · 01-13-2014, 08:35 PM

Okay, that is helpful. I may be able to use that for about half of the things I need to open the most recent (dated) workbook for. Other workbooks are saved in folders that may have other workbooks saved more recently but not the file name I need.

Example, lets say my workbooks are titled "XXXXXX 2014.01.13" Is there any way to loop my code from the first post to search for "Now(), -2" and then "Now(), -3" and so on? So if I saved a workbook named "XXXXXX 2014.01.06" a week ago and now want to open that workbook can I search by the file name "XXXXXX" and open the last file with that name followed by the most recent date using this code or would I need something else?

I would rather it pull the workbook based on the actual date that is saved in the filename rather than the last saved workbook of the folder.

I do appreciate the help above, that will be useful for me.

Thanks again.