Is there a way to open a file with a known name in an unknown folder using VBA?

Question

I run a series of reports every day and they are saved in a folder based on the date. For example: C:\Users\Desktop\Test\Current\2020 08 05\Report_Name 2020 08 05.xlsx

The following day I run the new reports and move the "2020 08 05" folder to an Archive. But the new reports pull data from the previous day's report, so I need to open it without specifying the dated folder name.

I've tried using a wildcard, but that doesn't work.

    Sub OpenReport()
    Dim sFound As String
    Dim Path As String
    
    Path = "C:\Users\Desktop\Test\Current\*\"
    
    sFound = Dir(Path & "\Report_Name*.xlsx")
        If sFound <> "" Then
            Workbooks.Open Filename:=Path & "\" & sFound
        End If

End Sub

score 0 · Answer 1 · answered Aug 06 '20 at 04:39

You could search each folder and subfolder for the specified file name.

I personally like a non recursive approach and have found "cor_blimeys" answer to this question very helpful many times before.

Loop Through All Subfolders Using VBA

Basically you create a new collection , iterate through files/folders in the root directory, adding folders to the collection when found and then iterate through each sub folder looking for the file name.

The recursive methods always hurt my brain too much and find the non recursive method easier to debug and step through. Bits it's just personal opinion.

Either way , read the link.

Ron Rosenfeld · Accepted Answer · 2020-08-06T21:43:00.530

0

Since the name of the file path is fixed, except for the date, just generate it:

Function yesterdayReport() As String
    Const sBasePath As String = "C:\Users\Desktop\Test\Current\"
    Dim sDT As String

sDT = Format(Date - 1, "yyyy mm dd")
yesterdayReport = sBasePath & sDT & "\Report_Name " & sDT & ".xlsx"
    
End Function

If you need to find the previous working day, taking into accoutn weekends and holidays, you can use one of the WorkDay functions.

For example:

Option Explicit
Function yesterdayReport() As String
    Const sBasePath As String = "C:\Users\Desktop\Test\Current\"
    Dim sDT As String, dt As Date
    Dim vHolidays
    
vHolidays = Array(#1/1/2020#, #7/4/2020#, #8/5/2020#)
dt = Application.WorksheetFunction.WorkDay(Date, -1, vHolidays)

sDT = Format(dt, "yyyy mm dd")
yesterdayReport = sBasePath & sDT & "\Report_Name " & sDT & ".xlsx"
    
End Function

Your holiday argument can be either hardcoded, as I show, or obtained from a range on a worksheet.

There are variations in case your weekends are not Sat/Sun.

edited Aug 06 '20 at 21:43

answered Aug 06 '20 at 11:16

Ron Rosenfeld

53,870
7
28
60

I probably should have specified in my original question, but I run the reports every weekday, not every single day. So this would work Tuesday-Friday, but I'd have a problem on Mondays and holidays. – Will Aug 06 '20 at 16:23
It's certainly simple enough to check for the weekday within the Function. VBA does have that capability. Do you have a list of Holiday dates? – Ron Rosenfeld Aug 06 '20 at 21:14
It changes from year to year, but that would be easy enough to change if it was just once annually. Could you give me an example of what the code would look like using the weekday function? – Will Aug 06 '20 at 21:27
@Will If you have a list of holidays, then you can use the `WorksheetFunction.Workday` method. I'll add some code. – Ron Rosenfeld Aug 06 '20 at 21:40

Is there a way to open a file with a known name in an unknown folder using VBA?

2 Answers2