This document outlines how a LaserChamp barcode scanner, ScanChamp® ScanReader Solo software, and OpenOffice.org Calc, are used to create a lightweight employee timesheet solution on a Windows computer. Note similar functionality can be achieved with an RFID scanner.

The solution will allow you to scan a barcode (say from a Badge) and with ScanReader Solo deliver the barcode with an associated timestamp from the scanner to an Excel worksheet.  If you are using OpenOffice.org’s Calc application you can follow these same steps. If you do not have OpenOffice.org’s toolset, you can download them for free from www.openoffice.org

The first step is to create an Excel spreadsheet with three sheets.  This example uses the spreadsheet you can download from here. Feel free to modify it for your own use, but use caution and read through this instruction before modifying.  The first sheet is the employee database.  This sheet contains the BadgeID numbers (barcode data) and Names of the employees.  The second sheet is the scanned data sheet, which is populated via an ODBC connection with ScanChamp ScanReader Solo.  This sheet is the destination of all scans received by ScanChamp ScanReader Solo.  The third sheet is the actual Employee Timesheet, which will contain the employee attendance information.  This sheet is generated based on data in the other two sheets, implementing the Excel “VLOOKUP” function.

Now setup is required for ScanChamp ScanReader Solo to read data from the LaserChamp barcode scanner.  In ScanReader Solo, make sure that the Time stamp is checked.  In addition for our example, also ensure that the Scanner ID, and User ID options are checked.  Next, create an ODBC Connection to the Excel spreadsheet.  For instructions on setting up these items, see the ScanReader Solo documentation.

With the Excel spreadsheet, ScanChamp ScanReader Solo, and the ODBC connection set up, the system can be used.  Scan the employee BadgeID barcodes as they arrive to work.  Launch ScanChamp ScanReader Solo and Connect to the scanner.  Select “Read.”  The “Scanfiles” sheet will populate with the appropriate data.  Instantaneously, the “TimeCard Info” sheet will be populated with the NAMES of the employees along with their badge numbers and the time that their badge was scanned.  This ability takes advantage of the spreadsheet’s VLOOKUP feature.  (See formula in TimeCard Info sheet in one of the NAME cells, such as A2).

Excel Notes:
Please note that the spreadsheet cells must be formatted for “Text.”  Otherwise, Excel will translate your data to Scientific Notation, and the VLOOKUP function will not work.  This is easily done by highlighting the cells, choosing Format > Cells, and selecting Text.  It will not reformat used cells, but will hold the setting for future input.

In Excel, rows that have ever had data in them are considered “used rows”, even if the “Clear” function has been used.  ScanChamp ScanReader Solo will not write data to these rows, but will find the next unused row in the spreadsheet.  You must “Delete” rows that have had data in them if you want to write a new set of data to that row.

Also note that the Excel spreadsheet need not be open to receive data through the ODBC connection.