Barcode Scanners for Sage 50 (Peachtree)

I recently assisted a small business for an inventory reconciliation. They use Sage 50 accounting software (formerly Peachtree).

They enter all inventory information by hand and were looking for automation ideas. A barcode scanner would allow them to more quickly build a list of existing inventory in their warehouse.

Barcode Scanning Solutions

We looked at several solutions. Some solutions came with consultants to implement them, some suggested switching to QuickBooks, some were just a scanner. Inventory apps for iPhones weren’t considered industrial enough of a solution. Nothing was less than $800, and several options were more than $5k. None of the solutions seemed to sync between the scanner and Sage 50; all required a middle step of importing/exporting.

We eventually found a generic barcode scanner off Amazon for $99. It is wireless and works with Mac and PC, and charges by USB port. It is made of cheap plastic, but works. Think of it like a wireless keyboard — it just sends the barcode information back to a laptop as if someone typed it. I’d guess it sends the information about 30 feet, so you need a laptop.

Setting Up Inventory Spreadsheet

Once it arrived we needed a spreadsheet to track all the inventory information. Here’s how we set up the spreadsheet:
Columns: Model No, Serial No, Timestamp

Scanning a barcode fills in the information in one cell. We would then have to click to the next cell, scan, click next cell, and enter the date. So we built a quick Excel macro.

The macro moves to the next cell on a scan in Column A. In Column B, it adds a Timestamp in Column C and moves to the next row. It is much more efficient than having to reach back to the laptop to move rows.

Excel Macro for Barcode Scanning

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range

‘ The variable KeyCells contains the cells that will
‘ cause an alert when they are changed.
Set KeyCells = Range(“A2:B10000”)

nrow = ActiveCell.Row
ncol = ActiveCell.Column

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then

‘Model No
If ncol = 1 Then
ActiveCell.Offset(-1, 1).Select
End If

‘Serial No, then Timestamp and go next row
If ncol = 2 Then
ActiveCell.Offset(-1, 1).Select
ActiveCell = DateTime.Now
ActiveCell.Offset(1, -2).Select
End If

End If

End Sub

Next Steps

This helped build the list of all current inventory. Next we had to export the current inventory list from Sage 50, compare the two lists for differences and make the necessary accounting adjustments.

Hope this is helpful to anyone looking for barcode scanner solutions for Sage 50.

Enjoy this post? Get New Posts by Email!

Leave a Reply

Your email address will not be published. Required fields are marked *