Excel条形码扫描宏 [英] Excel Barcode Scanning Macro

问题描述

我处理大量独特的产品（每年10,000+），我正在寻求优化Excel，使我的工作更有效率。我有一个USB条形码扫描器，并希望实现这一点到我的过程。

I deal with a large number of unique products (10,000+ per year on my own) and I am looking to optimise Excel to make my job more efficient. I have a USB barcode scanner and want to implement this into my process.

我的供应商向我发送了一个包含产品信息的大型电子表格，如条形码和其他产品详细信息。我也收到了产品的样品，需要在系统中注册，并用电子表格进行交叉引用。

My suppliers send me a large spreadsheet with product info, such as barcode number and other product details. I also recieve samples of the product and need to register them within the system, and cross reference them with the spreadsheet.

我正在寻找一个宏，当它接收到来自扫描仪的输入，在电子表格内搜索和导航到产品所在的行，如果相同的条形码在3-5秒内进行了第二次扫描，则填写列x中的日期，如果列x是被占用的单元格，填写y列中的日期。

I am looking for a macro which: when it recieves an input from the scanner, searches and navigates within the spreadsheet to the row where the product is, and if the same barcode has a second scan within 3-5 seconds, fills in the date in column x, and if column x is an occupied cell, fills in the date in column y.

这将允许我搜索产品，而不填写日期（不是双重扫描）的一般查找，而且还检查产品进出日期。

This will allow me to both search for the product without filling in the date (not double scanning) for general lookups, but also to check the product in and out with the date.

提前谢谢你的帮助！

推荐答案

p>如果它有帮助，这个问题与条形码扫描有关的事实可以被忽略 - 条形码扫描器简单地被实现为键入扫描数的键盘设备（一些附加到最后输入，有些不是，和一些是可配置的）。

If it helps, the fact that this question relates to barcode scanning could be disregarded - barcode scanners are implemented simply as keyboard devices that 'type' the number scanned (some append 'enter' on the end, some don't, and some are configurable).

所以你的问题的答案不一定要特别的扫描条形码。它几乎只需要一个 InputBox ，它需要一个数字，然后在 MATCH 函数中使用该数字。

So the answer to your question need not be in any way specific to the scanning of barcodes. It pretty much just needs an InputBox that takes a number and then uses that number in the MATCH function.

例如

code = InputBox("Please scan a barcode and hit enter if you need to")
matchedCell = match(code, productRange, 0)
matchedCell.Offset(0,2) = Now

其中 productRange 是您的产品列表占用的单元格范围。

where productRange is the range of cells that your product listing takes up.

编辑：

首先，按照这样的教程： http://www.excel-easy.com/vba/create-a-macro.html

To start with, follow a tutorial like this one: http://www.excel-easy.com/vba/create-a-macro.html

您应该最终得到一个按钮您的表单，当单击时，将在单元格A1中放入单词hello。

You should end up with a button on your form that, when clicked, will put the word "hello" in cell A1.

一旦你有这个工作，用我建议的行替换'hello'代码你应该在你的路上。

Once you have this working, replace the 'hello' code with the lines I suggested above and you should be well on your way.

这篇关于Excel条形码扫描宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！