Macro to open specified file path with variable date

Hi all,

I'm looking to write a macro for an excel sheet that will open up a data file from my HD. The end goal is to have a macro that will automatically open a report file from the previous business day, and then copy and paste it into my current worksheet. The file changes daily, as it is the "daily reports." However, the path an string always stay the same, and the numbers at the end that change simply represent the date in the format yyyymmdd...

An example for the file is "O:\Globe\Reports\DTD_TMCA_20130701. So, I start with -

Dim strPath As String
Dim strFind As String

strPath = "O:\Globe\Reports"
strFind = DTD_TMCA

Now I need it to automatically take the file from the LAST WORKDAY (so for July 1, it would be June 28), such as the Workday function in excel that would read =WORKDAY(TODAY(),-1).

Thanks for your help!

Have you tried changing strFind to:

Please Login or Register  to view this content.

That worked, I had the function written incorrectly..Thank you!