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

[qpywsqp]

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?

 

[qpywsqp]

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?

 

[hiker95]

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?

 

[qpywsqp]

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

	A	B	C	D	E	F	G	H
Code	Name	Time In	Time Out	Time In	Time Out	Time In	Time Out
MC10008	Jacky
MC10080	Rebecca
PC10001	Rachel
PC10100	Jeffrey
PC10108	Steve
MC10988	Rosy
PC10945	Johnson
MC10884	Jackson
MC10886	Ravy

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</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

	A	B	C	D	E	F	G	H
Code	Name	Time In	Time Out	Time In	Time Out	Time In	Time Out
MC10008	Jacky
MC10080	Rebecca
PC10001	Rachel
PC10100	Jeffrey
PC10108	Steve
MC10988	Rosy
PC10945	Johnson
MC10884	Jackson
MC10886	Ravy

<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>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]2012/12/31 1:28[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[hiker95]

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.

 

[qpywsqp]

Hiker,

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

 

[hiker95]

qpywsqp,

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

 

[qpywsqp]

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

[TABLE="class: cms_table"]
<tbody>[TR="bgcolor: #CACACA"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]1[/TD]
[TD]Code[/TD]
[TD]Name[/TD]
[TD]Time In[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[TD]Time Out[/TD]
[TD]Time In[/TD]
[TD]Time Out[/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]2[/TD]
[TD]MC10008[/TD]
[TD]Jacky[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]3[/TD]
[TD]MC10080[/TD]
[TD]Rebecca[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]4[/TD]
[TD]PC10001[/TD]
[TD]Rachel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]5[/TD]
[TD]PC10100[/TD]
[TD]Jeffrey[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]6[/TD]
[TD]PC10108[/TD]
[TD]Steve[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]7[/TD]
[TD]MC10988[/TD]
[TD]Rosy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]8[/TD]
[TD]PC10945[/TD]
[TD]Johnson[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]9[/TD]
[TD]MC10884[/TD]
[TD]Jackson[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #CACACA, align: center"]10[/TD]
[TD]MC10886[/TD]
[TD]Ravy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



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.

 

[hiker95]

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

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.

 

[qpywsqp]

Hiker,

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

Your updated Macro Code working. Thanks a lot.

 

[hiker95]

qpywsqp,

You are very welcome. Glad I could help again.

Happy New Year.