• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need to link vlookup source file everytime

S

ssuresh98

Member
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
 
Hello Suresh,


Welcome to Chandoo_Org 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
 
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!

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!
 
You must log in or register to reply here.
Back
Top