VBA Code to open File from formula result

Hi All

I am currently using a macro which amongst other things opens a file collects data from in then pastes it onto the current worksheet.

The problem is the file name which I require to open changes every day. I have written a formula into a cell to hyper link the correct file name however do not know how to work this into the macro below.

Hope someone can help

Cheers

File Name Formula:

"Z:\Backup\backup_"&TEXT(TODAY(),"yyyy-mm-dd")&"_0600\waw_29_"&TEXT(IF(WEEKDAY(TODAY()-1)=1,TODAY()-2,TODAY()-1),"yyyy-mm-dd")&"_val_v001.csv"

Macro:

Sub Macro1()
    Workbooks.Open Filename:="C:\Users\..................xlsm"
End Sub