Filepath opening multiple times in VBA

Question

I want to bring data from another excel file, the code below works as intended, XLookup does the job BUT Windows file picker opens three times with this code. Can anyone advise why? I've got one variable and two references to it in the formula.

Dim FileToOpen As Variant
FileToOpen = Application.GetOpenFilename()
Range("B1").Select
ActiveCell.Formula2R1C1 = _
        "=XLOOKUP(RC[-1]:R[79]C[-1],'& FileToOpen &'!R11C1:R57C1,'& FileToOpen &'!R11C2:R57C2,""Not Found"",0)"

Answer 1

You need to distinguish between Excel and VBA. In VBA, you have a variable named FileToOpen that contains a filename. In Excel, this variable is unknown.

What you want to do is to write the content of the variable into a string. That string will then be written as a formula into a cell. Therefore you need to do some string concatenation in VBA. With you current code, VBA sees only one single string. It cannot look into that string, see that a part of a string is a variable name and replace the name with the content. Everything between the double quotes in left untouched by VBA - except if you have a pair of double quotes, this tells VBA that you want a (single) double quote character within the string.

If you want to write a formula with VBA, my advice is always to write the formula into a intermediate string variable that you can easily check with the debugger.

Change your code to

Dim formula As String   
formula = "=XLOOKUP(RC[-1]:R[79]C[-1],'" & FileToOpen & "'!R11C1:R57C1,'" & FileToOpen & "'!R11C2:R57C2,""Not Found"",0)"
Debug.Print formula 
ActiveCell.Formula2R1C1 = formula

See the difference? Now your formula is build from (5) pieces that VBA sees, and it will conatenate the pieces of the formula and the content of your variable.