SerialMagic enables running an Excel Macro when a barcode is scanned

More ways to use SerialMagic

This page is outlines how to run a macro (e.g. Excel) each time a barcode is scanned.

This can be valuable for interfacing a barcode scanner with an existing business process that is based on Excel.
For example at least one customer uses this mechanism on OS X in order to improve efficiency of their store inventory checking process.

This example Excel Macro is for use with SerialMagic Professional.
The macro is mapped to CTRL+SHIFT+R key sequence and this sequence is defined in SerialMagic using an SMAP (SerialMagic Action Profile) to occur when each barcode is scanned.

Data that is to be searched will be located in column C of the sheet. When the macro runs, it will search column C for data, and if a match occurs, the macro will enter time/date in column D for the matching data. If a match is not found the macro will throw a dialog indicating the item was not found. See image below for example on Windows after two barcode scans; one matching item barcode, and one non-matching item barcode (this also works on Apple OS X)

Download Excel macro complete with macro source code.

Excel macro source code is also provided below.

Sub DataInput()
'
'Copyright(c) 2005-2006 Serialio.com
'
'This prompts for an input, then does a lookup for the data in the specified column
'If the data is found, it puts a timestamp in the cell to the right of the data
'If the data is not found it shows a dialog indicating that
'Assumption is that entries in the searched column are unique

'sVal = InputBox("Scan or type product barcode...", "LaserChamp Input")
'ActiveCell.Value = sVal

Dim SearchTarget As String
Dim myRow As Long
Dim Rng As Range
Static PrevCell As Range
Dim FoundCell As Range
Dim CurCell As Range

'SearchTarget = "asdf"
SearchTarget = InputBox("Scan or type product barcode...", "LaserChamp Input")

If PrevCell Is Nothing Then
myRow = Selection.Row
Set PrevCell = Range("C" & myRow)
End If

'Set Rng = Range("C:C,E:E") 'Columns for search defined here
Set Rng = Range("C:C,C:C") 'Columns for search defined here
With Rng
Set FoundCell = .Cells.Find(What:=SearchTarget, _
After:=PrevCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)
End With

If FoundCell Is Nothing Then
MsgBox SearchTarget & " was not found."
Else
FoundCell.Activate
' If PrevCell.Address = FoundCell.Address Then
' MsgBox "there's only one!"
' End If
ActiveCell.Offset(0, 1).Select
timestamp = Format(Now(), "dd-mmm-yy hh:mm")
ActiveCell = timestamp
ActiveCell = Now()

Set PrevCell = FoundCell
End If
End Sub