[GUI APP] Spreadsheet Barcode Scanner utility

[evilC]

This script is to assist people when using a USB barcode scanner in combination with a spreadsheet - eg you have a spreadsheet of asset tag numbers and you wish to verify whether or not each item scanned is present in the spreadsheet, without having to look at the laptop screen or press any keys in between scans.
Simply pull the trigger on your scanner and listen for the sounds made by the laptop.



Features:

• Compatible with MS Excel.
• Requires a barcode scanner with a "Preamble" character set - this causes the scanner to send a special character before sending the characters it scanned. This allows the script to "lock out" the keyboard so as to prevent accidental overwriting of data in the spreadsheet. Non-printable preambles are recommended (Default is F8)
• Uses the COM interface - does not operate Excel via keyboard shortcuts.
• Marks found cells in green.
• Searches across ALL worksheets in the workbook.
• Excel does NOT have to be the active application when scanning.
• You can scan quicker than it can search the spreadsheet - searches are queued and processed on a FIFO basis.
• Scanned items that are not found are added to an extra worksheet called "!NotFound!"
• Spreadsheet is auto-saved on each scan.
• When an item is found, Text-To-Speech is used to tell you which worksheet it was found on.
  This will either be numeric (ie it will say "One" for the first sheet) or named - if you name a Sheet "Sheet1|Hello", it would say "Hello" when it found an item on that sheet (ie use a pipe (|) symbol to specify a name)
• All settings are persistent and saved in an INI file.

Source code:
https://github.com/evilC/Spreadsheet-Ba ... canner.ahk

Instructions:
(Assuming default keys).

1) Run the script.
2) Open the Excel spreadsheet you wish to search, and while it is the active application, hit F4 to "link" the script to this spreadsheet.
3) Ensure the preamble setting on your barcode scanner matches the Preamble Key setting in the script. This is normally done by scanning barcodes from your scanner's manual.
You may need to do the same for the Postamble (Terminator) setting, but most scanners default to Enter, which is correct for this script.
4) You may also need to configure your scanner to set the delay between keystrokes - most important is a short delay (1-10ms should do) between the preamble key and the scanned data. This is to give the script a chance to lock out the keyboard.
5) Scan some barcodes! Excel does not need to be the active application!

[evilC]

New version added

Now hosted on Github
Now only supports Excel (Switched from keystrokes to COM interface)
Now adds not found items to "!NotFound!" worksheet.

[Babar the Elephant]

Hi;

I am glad to see that someone did some work with barcode scanners, I'd like to check this out. Thanks for posting!

[evilC]

I have shifted to working on a pure Excel VBA version at the moment, but would certainly be interested in any improvements anyone can come up with for the AHK version...