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

Something like this:

Option Explicit

Sub Macro1()
Dim fName As String

    If Weekday(Date - 1) = 1 Then
        fName = "Z:\Backup\backup_" & Format(Date, "yyyy-mm-dd") & _
            "_0600\waw_29_" & Format(Date - 2, "yyyy-mm-dd") & _
            "_val_v001.csv"
    Else
        fName = "Z:\Backup\backup_" & Format(Date, "yyyy-mm-dd") & _
            "_0600\waw_29_" & Format(Date - 1, "yyyy-mm-dd") & _
            "_val_v001.csv"
    End If
    
    Workbooks.Open Filename:=fName
    
End Sub