PDA

View Full Version : Barcode Scanner Excel Lookup



rozbaz
02-20-2014, 03:34 AM
Hello vba gurus:)

I would like to know if the scenario below is possible.

Today through the use of a barcode scanner i do scan barcodes in an excel in A2 and in C2 i have a vlookup formula (actually an index-match formula because the vlookup is reversal) that results to an SKU code that i have in Sheet2. Upon that I copy and alt-tab to the ERP of the company and then i paste.

I would like to make this scenario automatic through some kind of background vlookup so i dont have to alt+tab.

So that these barcodes that gets scanned i would like the scanner to type the result of the vlookup instead of what it actually scans.

The way i have imagined this concept is that if something gets typed while another key is pressed (e.g. ctrl) then this will initiate a vlookup and the actual typing would get overwritten by the vlookup result. Maybe a software like AutoHotkey would be necessary but i am not sure.

That all should be done in background because i want this to get typed in an ERP system and not in excel.

Hope this is possible

Thank you in advance

rozbaz
02-21-2014, 02:09 AM
So since this seems impossible, I thought of maybe an easier workaround.

My formula in the excel now looks like this


=VLOOKUP(INDEX(DATABASE!D:D;MATCH("*"&LEFT(A2;1000)&"*";DATABASE!G:G;0));PRICELIST!B:C;2;FALSE)

Which basically when I scan with my barcode reader in cell A2, in cell C2 it returns the price for a book. The problem is that I have to alt+tab and copy paste all the time since the price needs to be pasted in a non-excel application, which makes it a little bit slow.

What I would like to do is make this a little bit more automatic by usign AutoHotkey so when I press a combination of keys in my keyboard this would initiate a copy to clipboard and execute. So what i want to do is have a stand-alone vbs file that would perfom the above formula even if the excel is closed and would replace (substitute) the clipboard (i.e. the barcode scanned) with the price (i.e. the result of the above function)

westconn1
02-21-2014, 02:23 AM
this forum has rules about x posting
i have replied to your similar thread in vbforums