Scan barcode to excel with date & time stamp in & out.

qpywsqp

New Member
Joined
Dec 2, 2012
Messages
26
Hi all. I have research around the internet and finally found out Macro able to solve my issue. But im totally not a programmer. Hopefully someone can help me on this.

I will scan QR Code with a scanner to cell A1 and wanted cell C1 to have date and time stamp for IN. When Scanner scan the same QR Code, D1 will have date and time stamp for out. If repeat third time scanning the same QR code after a few scanning, it will appear on next row. Which means A8 with the code and C8 with date and time stamp for IN again.

Is that possible to done with Macro?
 
Hi Hiker,

Out of my curiosity, will that possible to have it integrated with pre key-in code in Column A. So whenever i scan the delegate, it will perform find and time in and out for the particular delegate?
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
qpywsqp,

Out of my curiosity, will that possible to have it integrated with pre key-in code in Column A. So whenever i scan the delegate, it will perform find and time in and out for the particular delegate?

1. You would have to scan the delegates as they go in and out, below the list of pre-scaned-in-delegates?

2. Or, cell A2 would have to always be the active cell for scanning in all delegate ins and outs, and that information will populate to the correct row/columns for the pre-scaned-in-delegate, or, add the new/not-listed-delegate to the bottom of the pre-scaned-in-delegates?
 
Upvote 0
qpywsqp,



1. You would have to scan the delegates as they go in and out, below the list of pre-scaned-in-delegates?

2. Or, cell A2 would have to always be the active cell for scanning in all delegate ins and outs, and that information will populate to the correct row/columns for the pre-scaned-in-delegate, or, add the new/not-listed-delegate to the bottom of the pre-scaned-in-delegates?


Hi Hiker,

What i mean is:


Let say i had a database as per below. Active Cell always at Cell A.

Sheet1

ABCDEFGH
1CodeNameTime InTime OutTime InTime OutTime InTime Out
2MC10008Jacky
3MC10080Rebecca
4PC10001Rachel
5PC10100Jeffrey
6PC10108Steve
7MC10988Rosy
8PC10945Johnson
9MC10884Jackson
10MC10886Ravy

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 192px"><col style="WIDTH: 91px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4



When Rebecca being scanned, Cell C3 Time In will time-stamp time and date.

Sheet1

ABCDEFGH
1CodeNameTime InTime OutTime InTime OutTime InTime Out
2MC10008Jacky
3MC10080Rebecca2012/12/31 1:28
4PC10001Rachel
5PC10100Jeffrey
6PC10108Steve
7MC10988Rosy
8PC10945Johnson
9MC10884Jackson
10MC10886Ravy

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 192px"><col style="WIDTH: 91px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"><col style="WIDTH: 161px"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
qpywsqp,

Per your latest screenshots:

The last Worksheet_Change event macro code on 12/18/2012, reply #18, should work because column B was blank from the start.

Are you are going to pre populate all the delegate codes in column A, and the names in column B?

If so, then you must make sure that the active cell will always have to be the next available blank cell in column A, in this case cell A11, before you do any more scanning.
 
Last edited:
Upvote 0
Hiker,

Sorry, the last screenshot show the time and date was created by me manually. The actual macro does not work that way.
 
Upvote 0
qpywsqp,

The actual last Worksheet_Change event macro code on 12/18/2012, reply #18, does work that way.
 
Upvote 0
Hiker,

I had tried it doesnt work on the macro code reply #18.

Correct me if im wrong with the step below.

1. I had database in excel .xls without Macro code. Example as per below:

Sheet1

ABCDEFGH
1CodeNameTime InTime OutTime InTime OutTime InTime Out
2MC10008Jacky
3MC10080Rebecca
4PC10001Rachel
5PC10100Jeffrey
6PC10108Steve
7MC10988Rosy
8PC10945Johnson
9MC10884Jackson
10MC10886Ravy

<tbody>
</tbody>



Excel tables to the web >> Excel Jeanie HTML 4

2. I copy and paste the Macro code from reply #18 into my database and save as to .xlsm.
3. Active cell at A11, type MC10080. Cell A11 blank and Cell C3 did not time-stamp for time and date.
 
Upvote 0
qpywsqp,

Because you have pre existing data in columns A and B, I had to update the macro.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel


Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 12/30/2012
' http://www.mrexcel.com/forum/excel-questions/672492-scan-barcode-excel-date-time-stamp-out.html
If Intersect(Target, Range("A2:A3000")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
Dim lc As Long, fr As Long, n As Long, nr As Long
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  n = Application.CountIf(Columns(1), Cells(Target.Row, 1))
  If n = 1 Then
    lc = Cells(Target.Row, Columns.Count).End(xlToLeft).Column
    If lc = 1 Then
      Cells(Target.Row, lc + 2) = Format(Now, "m/d/yyyy h:mm")
    ElseIf lc > 2 Then
      Cells(Target.Row, lc + 1) = Format(Now, "m/d/yyyy h:mm")
    End If
  Else
    fr = 0
    On Error Resume Next
    fr = Application.Match(Cells(Target.Row, 1), Columns(1), 0)
    On Error GoTo 0
    If fr > 0 Then
      lc = Cells(fr, Columns.Count).End(xlToLeft).Column
      Cells(fr, lc + 1) = Format(Now, "m/d/yyyy h:mm")
      Target.ClearContents
    End If
  End If
  On Error Resume Next
  Me.Range("A1", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
  nr = Me.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
  Me.Cells(nr, 1).Select
  .EnableEvents = True
  .ScreenUpdating = True
End With
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then pre-test the macro before going into the field.
 
Upvote 0
Hiker,

Yes, i had pre existing data in Column A & B.

Your updated Macro Code working. Thanks a lot.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top