Vlookup requires update links to server file everytime

**sureshks** · 04-25-2012, 02:47 AM

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

**dilipandey** · 04-25-2012, 05:51 AM

Hi Sureshks,

Welcome to the forum.

I would suggest you to update your code to open that server file and close that when vlookup is done....
Also as an option, have you included the server name or the drive letter in you code when you say \\server\..?

Regards,
DILIPandey

<click on below 'star' if this helps>

**sureshks** · 04-26-2012, 09:27 PM

Hi Dilipandey,
thanks for your response. Yes, I have included the server name in the code but somehow the macro keeps asking for "Update Values". Is there a way we can to use variables or constants for the server file?
I guess adding a code to open the source file each time should work, but since the files are huge it will slow down the macro.

**dilipandey** · 04-26-2012, 11:48 PM

Hi Sureshks,

Since it is a formula where the server name is used, hence it will ask for updation... it is similar to a formula where we give a reference to other workbook and when calculating / opening that file again, it asks for link updation. So no issue on this front. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**sureshks** · 04-27-2012, 07:31 PM

Hi Dilipandey,
Thanks for your response.
The issue here is that I have a file on the server, (path and name are fixed). If I need to lookup some information from this file, do I always have to update value/links. Why can't excel remember the path & file name and enter information? It gets complicated because it works fine sometimes and it works always when the server file is open.

Sorry to be a bother but I am new to excel vba and may not understand the concept.
TIA

Here is the actual macro:

Sub InsertNamefromBarcode

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],\\resfiles\bioinfo\gCell\reports\prd\[CellLineReport.csv]CellLineReport!C1:C3,3,0)"

End sub

**dilipandey** · 04-28-2012, 01:09 AM

Hi Suresh,

Excel can remember the path and file name... but since that file provides results (which can be updated at any time), hence excel need to get those results every time code (formula) is executed via those links used in VBA code (formulas)...Else how would excel can get those updated results...without referencing / updating links ...!!

Regards,
DILIPandey

<click on below 'star' if this helps>

Regards,
DILIPandey

<click on below 'star' if this helps>

**sureshks** · 04-30-2012, 04:51 PM

Hi Dilipandey,
Thanks for helping out. What I don't understand is this:
1) When the source file is open, the macro runs fine, it does not ask to link or update values
2) When it is closed, it is asking me to update/link values and it wants me to point it to the location of the folder. It is not asking me whether I want to update values, it is asking me to physically point to the file where the data is stored.

Is there a way I can run this macro without this prompt? I don't think screenupdate=False would work because it is asking me for a file location. Is there a way I can store the path and file name so that I don't have to point to it everytime I run the macro?

TIA
Suresh

**dilipandey** · 05-02-2012, 12:31 AM

Yes... you can store the path which can be used further in the macro code..
Also, as a check is the below path is starting with server name :-

\\resfiles\bioinfo\gCell\reports\prd\[CellLineReport.csv]

resfiles is a server name here ?

Regards,
DILIPandey

<click on below 'star' if this helps>

**sureshks** · 05-03-2012, 09:50 AM

Yes resfiles is the server name.
The macro works fine when the source file is open. My question is "Do we need to have the source file open for the macro to work? The source file is read only, does this have any effect?"