Vlookup - pass filename to table array

[VRK]

Newbie here. Basic level is recording macros and pasting them together. I try to give users the ability to open any file and pass it through. Problem: I have not been able to find the vba method that passes a user’s chosen file via GetOpenFilename, into a VLOOKUP table_array. File1 is opened and cleaned up, then open File2 and clean it. Return to File1 to enter Vlookup formula and point to File2 in the table_array in columns B:G. The recorded macro line is:
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'[file2.xls]Sheet1'!C2:C7,2,FALSE)"

 

[pbornemeier]

Sub FilenameIntoFormula()

    Dim sFileName As String
    Dim sFilePath As String
    Dim sFilePathName As String
    
    sFilePathName = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    sFilePath = Left(sFilePathName, InStrRev(sFilePathName, "\"))
    sFileName = Right(sFilePathName, Len(sFilePathName) - InStrRev(sFilePathName, "\"))
    Range("A1").FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'" & "[" & sFileName & "]" & "'!C2:C7,2,FALSE)"
End Sub