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:

Please Login or Register  to view this content.

[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

Welcome to the Forum. Please read the Forum Rules and be sure to use CodeTags in future