How to shorten path in formulas?

Hi!

I have to insert a several formulas (Index - match) on the sheet, which finds the data in another workbook, like the one in the example below:

Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('C:\Users\MyName\Documents\work\public tender - items\Public tender 2021\Additional\[Items-public tender JN004837-2021-repeated.xlsm]Items JN 1. part'!C1:C8,MATCH(RC8,'C:\Users\MyName\Documents\work\public tender - items\Public tender 2021\Additional\[Items-public tender JN004837-2021-repeated.xlsm]Items JN 1. part'!C8,0),2)"

.

It works fine, except that the path to another workbook is rather long, so I'd like to shorten it.

I'd like to do something like:

dim strFile as string
strFile = "C:\Users\MyName\Documents\work\public tender - items\Public tender 2021\Additional\[Items-public tender JN004837-2021-repeated.xlsm]Items JN 1. part"

but this isn't working correctly because it keeps opening a file dialog box and asking to update values for

strFile & Items JN 1. part

What is the right way to do it?

I'm using Excel for Microsoft 365 for small businesses.

The last thing you showed

strFile & Items JN 1. part

doesn't make sense to me. I don't know how you are using that.

If you just want to shorten the code (it won't shorten what you see in the cell) try this:

const strFile = "C:\Users\MyName\Documents\work\public tender - items\Public tender 2021\Additional\[Items-public tender JN004837-2021-repeated.xlsm]Items JN 1. part"

Range("B2").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX('" & strFile  & "'!C1:C8,MATCH(RC8,'" & strFile  & "'!C8,0),2)"

(removed misread)

You are very welcome and thanks for the rep!