VBA Code to link to external sheet

I would like to be able to perform a calculation in a spreadsheet that uses inputs from another spreadsheet, with the complicating factor being that the file path of the external sheet changes each month.

Each month a new folder and file is created (the external file), with a pre-defined naming convention. Based on the inputs of the calculation spreadsheet (i.e. the current date), I can easily write a formula that gives me the name of the external sheet in a cell.

The problem is however, I’d like to incorporate this dynamic file path into my formulas in the calculation sheet automatically.

The formula in the Calculation Sheet is:

=OFFSET(INDEX('C:\FolderYYY\FilenameMMM.xls]Tabname!$B:$B,MATCH($B$49,'C:\FolderYYY\FilenameMMM.xls] Tabname!$B:$B,0)-1,1),$B$50,5,1,1)
[Where FilenameMMM is defined as text in a cell in the sheet.]

I don’t know VBA code, so I don’t know how to achieve this is in VBA.
Can anyone provide a code that would allow me to calculate this, given that I have a cell which has the text of the file path?

Currently in VBA, to open the external file I have:

Sub Open_Ext_File()
x = Value & Range("E46").Value
filname = x & ".xls"
Workbooks.Open Filename:=filname
End Sub

[Where E46 is a combination of text and a concatenation of other cell values]

I need to know how I can use filname (as defined above) in VBA formulas to achieve what the offset formula would with the desired file path.
This would save me having to write a macro to find and replace the previous months’ file path with the current for all the tabs in the calculation sheet.

Cheers