vlookup of file that file name changes week to week

[nlange41]

I have a report that I run every week and save with the file name and current date. Part of the process is to open the previous week's file and do a Vlookup to pull in a column from the previous week's report. I'm trying to write in the macro the Vlookup instead of doing it manually. I know how to link it to where the report is saved, however, because the file name changes every week, I'm having trouble with the file name in the macro. I have the previous week's date in B1. I have tried below but get a syntax error. I have B1 formatted to
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(@C[-1],'[Ref Numbers "&B1&".xlsx]Sheet1'!C1:C2,2,FALSE)"

 

[nlange41]

I have a report that I run every week and save with the file name and current date. Part of the process is to open the previous week's file and do a Vlookup to pull in a column from the previous week's report. I'm trying to write in the macro the Vlookup instead of doing it manually. I know how to link it to where the report is saved, however, because the file name changes every week, I'm having trouble with the file name in the macro. I have the previous week's date in B1. I have tried below but get a syntax error. I have B1 formatted to
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(@C[-1],'[Ref Numbers "&B1&".xlsx]Sheet1'!C1:C2,2,FALSE)"

I hit enter on accident.
I have B1 formatted to 26-Nov-21 and file named the same.