I am a newbie in Excel VBA. Please find my scenario below.
I have a source file on server: \\server\Database\Sample.csv (Gets updated with new sample info every night. File name is the same).
File on desktop: c:\April.xlsx (I would like to scan a barcode and vlookup info from server file), here is the macro I recorded.

Sub InsertName ()
Activecell.formulaR1C1 = "=vlookup(RC[-1],\\server\Database\Sample.csv\sample!$A:$C,2,false)
End sub

The formula works when I have the source file open. But when I close the file, it works sometimes and other times it gives an error, sometimes it asks me to update values/links to source file. I do not know why it is not reliable. Any help with this will be appreciated.

Thanks