Need to link vlookup source file everytime

[ssuresh98]

Great site. Newbie using Excel 2010.

I have a csv file (>1000000 rows) on the server which updates every morning. I use this file as my source to vlookup and enter information. I have no problems with vlookup (know how to use it) but if I create a macro, it works only when I have the source file open. I have tried entering the full path of the source file. It works for some time but then stops working. There are other users who have access to this file too.


My problem is how do I enter the path of the source file so that I don't have to update links every time I want to use the macro.


TIA

 

[vijaySharma]

Hello Suresh,


Welcome to Chandorg Forums.


If the name of the source CSV file remains static, you could declare a CONST variable in the Macro to refer to the file.


If the name changes and has a date component, you could still generate this inside your macro.


HTH

~VijaySharma

 

[ssuresh98]

Thanks so much Vijay.

As I mentioned I am very new to Excel VBA and do not know how to declare consts for my situation. Please find my scenario below.


File on server: serverDatabaseSample.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)


Sub InsertName ()

Activecell.formula =vlookup(RC[-1],serverDatabaseSample.csvsample!$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 declaring the const will be appreciated.


Thanks

 

[brijendraydv]

Hi Suresh,


I think you cannot do vlook function untill that file is not opened.


Thanks,

 

[SirJB7]

@brijendraydv

Hi!

You can, and if it's closed a pop-up usually (depending on you Excel configuration) displays a message prompting for updating links from other files.

Regards!


@ssuresh98

Hi!

In my opinion t might be a network issue, not an Excel one.

Regards!