VBA to search for and open file with same name as cell text

[AcornNut]

I have 2 workbooks (Workbooks A & B) that are created with updated data almost on a daily basis, but each day is saved as a new file. Both workbooks have a different name format, but contain the date in the name and are saved in different folders. Workbook A has the latest saved date (not always today's date) in B4. Is there a way that Workbook A can find the corresponding Workbook B (with the same date in the file name as Workbook A cell B4) and open that file to use as it's database to pull data?

 

[Micron]

Should be doable (I have no code for this).
Find code that loops over files in a folder and returns each filename. Then use Instr function in that loop to test if the file name contains the date value from wb A Range("B4"). If so, then open it. If there is more than one file that contains the date value in its name, multiple files will open, so that might be a consideration. If you want the code to get a data in the opened workbook, that is an extension (additional steps) of the code.

 

[AcornNut]

Thank you for your reply, Micron. I'm pretty much self taught and am not that savvy with coming up with the code from scratch.

Basically, I need the code to search for the file in folder/subfolders as described above, and open in the back ground, copy the contents from that file to a designated database sheet, and then close. I have the code to open a specific file when the path and file name are known and constant (see code).

'OPEN SPECIFIED DATABASE WB WITHOUT SHOWING IN WINDOW (OPENS IN BACKGROUND)
Private Sub Workbook_Open()
Application.ScreenUpdating = False
Dim wb As Workbook
Set wb = Workbooks.Open("File Path\File Name.xlsx")
wb.Windows(1).Visible = False
Application.ScreenUpdating = True
End Sub


'CLOSES "INVISIBLE" DATABASE WB
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.DisplayAlerts = False
Workbooks("File Name").Close SaveChanges:=False
End Sub

What I need is a code that searches for a specified file as described in my original post, then copy all the contents (1 sheet) onto a specified sheet on the parent file and then close.

Any help anyone can give me to help

 

[Micron]

One unknown at the moment is how the code knows what folder to look in.
Is that folder path in wbA B4?
Or maybe the wb to find is always in the same folder as wbA? Or always one level up/down?
Or you want to use a file dialog to navigate folders and pick a file as you would in Windows (probably the most versatile method)?

 

[AcornNut]

The parent folder will remain the same, but the subfolders will change monthly (year above month and the target files saved are saved in the month folders). The two wb's are in different folders on the same level. I'd rather not call up a file dialog since the point is cutting back on time and manually doing these things.