How to pull data from everchanging folder and file?

[Phoenix71]

I have searched all over to find an answer to this conundrum. I have a excel file that is created by a report. The file is saved in a folder, that folder is in a specific location but the folder name changes everytime the report is ran, usually 2 - 3 times per day. The Filename and folder that it is in are always the same, based on date and how many reports have been ran. Example 2022-1-1_1, 2022-1-1_2, and so on and so on. I need to be able to pull data from the most recent report. I have found plenty of examples for a changing filename, but nothing for a folder name that changes. I am not looking for anyone to write my macro. I just need to be guided n the right direction. I'll figure it out from there. I am still new to this and am wiling to do the work to learn. Just need a push in the right direction.

 

[Marc L]

I have found plenty of examples for a changing filename, but nothing for a folder name that changes.

As the logic is the same, for example just using the VBA Dir function …​

 

[BSALV]

here you retrieve an array c01 with all your subdirectories.
With "Filter" you can do a little bit finetuning.

Sub M_snb()
     c00 = "c:\forum"                                     '---> your startsubdirectory (without spaces !!!)"
     c01 = Split(CreateObject("wscript.shell").exec("cmd /c dir " & c00 & " /a:d/b/s").stdout.readall, vbCr)
     Range("A1").Resize(UBound(c01) + 1).Value = Application.Transpose(c01)
End Sub