VLOOKUP variable workbook name

[BarneyLTD]

Hi all, hope everyone is doing well during this time?

Can anyone help solve an issue i am having?
I have started to set up a excel add-in with some macros I use most often but have hit a wall with trying to use a variable workbook name in a vlookup.

The below code is what is currently inserted but the date on the workbook changes depending on the date so 120220 would need to somehow be set to a variable to use today's date. I have tried to use the Today() function but can't seem to figure out the correct way. This workbook is open while the lookup is performed.

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC1,'Order Processor v4 [COLOR=rgb(0, 0, 0)]120220[/COLOR].xlsb'!List[[Column1]:[Column6]],6,0)"

Thanks

 

[JackDanIce]

Try:

ActiveCell.FormulaR1C1 = Replace("=VLOOKUP(RC1,'Order Processor V4 @var_date.xlsb'!List[[Column1]:[Column6]],6,0)", "@var_date", Format(Date, "DDMMYY"))

 

[BarneyLTD]

Try:

ActiveCell.FormulaR1C1 = Replace("=VLOOKUP(RC1,'Order Processor V4 @var_date.xlsb'!List[[Column1]:[Column6]],6,0)", "@var_date", Format(Date, "DDMMYY"))

Works perfectly! thanks

 

[JackDanIce]

You're welcome