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?

 

[cyrilbrd]

...finally found out Macro able to solve my issue....Is that possible to done with Macro?

Hi, Have you a macro or are you looking for a macro?
If you found one please post here the code or the link.
Thanks.

 

[qpywsqp]

Hi. Im looking for a macro which apply on my issue. Thanks

 

[hiker95]

qpywsqp,

Welcome to the MrExcel forum.

Thanks for the Private Message.

1. What version of Excel are you using?

We could use the Worksheet_Change event for cells A1, C1, A8, C8.

2. Are you only looking for the code to execute on those 4 cells?


3. Can we have screenshots of the worksheet in question, before and after the scans?

Can you post the raw data worksheet, and, post the worksheet results (manually formatted by you) that you are looking for?
To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker
Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.

If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data scrubbed/removed/changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[qpywsqp]

Hi Hiker,

Thanks for your quick reply.

1. I am using 2010

2. Yes i am looking for a code to execute on the cell.

I would like to give you a clearer picture, so that you can help me further. My intention to create this excel is for a seminar attendance tracking. I need this excel to track delegate attendance " In & Out ". Now first delegate walk to the entrance of the hall and i'll scan the barcode on his tag, assume the barcode is " Jeffrey ". Column A1 will capture the name and C1 will date&time stamp for " In ". Continuous scan for delegate 2, 3, 4 and so on. Column A2, A3, A4 will capture name of delegate 2, 3, 4, C2, C3, C4 will capture date&time stamp for " In ".

Half way of the seminar delegate 2 walk out from the hall, at this time i'll need to scan his tag to date&time stamp for " Out " on D2. The delegate might away for 30 minutes and walk in back to the hall, i'll need to scan his tag again to date&time stamp for " In " on E2.

Delegate number may up to 2000.

Below with the screenshot. Hope to hear you soon.

Sheet1

A

B

C

D

E

F

G

H

I

J

K

L

Jeffrey

Steve

Jacky

Penny

Cazz

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 147px"><col style="WIDTH: 129px"><col style="WIDTH: 131px"><col style="WIDTH: 131px"><col style="WIDTH: 131px"><col style="WIDTH: 131px"><col style="WIDTH: 131px"><col style="WIDTH: 131px"><col style="WIDTH: 131px"><col style="WIDTH: 131px"><col style="WIDTH: 131px"><col style="WIDTH: 131px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]Name[/TD]

[TD="align: center"]Time In[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]Time Out[/TD]
[TD="align: center"]Time In[/TD]
[TD="align: center"]Time Out[/TD]

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

[TD="align: right"]12/4/2012 10:39[/TD]

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

[TD="align: right"]12/4/2012 10:40[/TD]
[TD="align: right"]12/4/2012 10:50[/TD]
[TD="align: right"]12/4/2012 11:40[/TD]

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

[TD="align: right"]12/4/2012 10:41[/TD]

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

[TD="align: right"]12/4/2012 10:41[/TD]

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

[TD="align: right"]12/4/2012 10:42[/TD]

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

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[hiker95]

qpywsqp,


Sample worksheet Sheet1:

Excel Workbook
	A	B	C	D	E	F
1	Name		Time In	Time Out	Time In	Time Out
2
3
4
5
6
7
Sheet1

After some delegates have come and gone:

Excel Workbook
	A	B	C	D	E	F
1	Name		Time In	Time Out	Time In	Time Out
2	Jeffrey		12/4/2012 16:22
3	Steve		12/4/2012 16:22	12/4/2012 16:23	12/4/2012 16:24
4	Jacky		12/4/2012 16:22
5	Penny		12/4/2012 16:22
6	Cazz		12/4/2012 16:22	12/4/2012 16:23
7
Sheet1

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/04/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
With Application
  .EnableEvents = False
  .ScreenUpdating = False
  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
  .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


When you scan a delegate in and out, make sure the active cell is the delegates cell in column A.

 

[qpywsqp]

Hi Hiker,

Sorry for late reply. I had tried the code you provided to me. But there is one thing is not working.

I scan few time of the barcode, it appear in different row. Instead of replace the code at Column A and the Time In and Time Out stamp at the respective column.


Sheet1

	A	B	C	D	E	F	G	H	I	J
Name		Time In	Time Out	Time In	Time Out	Time In	Time Out	Time In	Time Out

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 230px"><col style="WIDTH: 64px"><col style="WIDTH: 111px"><col style="WIDTH: 102px"><col style="WIDTH: 102px"><col style="WIDTH: 102px"><col style="WIDTH: 102px"><col style="WIDTH: 102px"><col style="WIDTH: 102px"><col style="WIDTH: 102px"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

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

[TD="align: right"]2012/12/14 18:32[/TD]

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

[TD="align: right"]2012/12/14 18:33[/TD]

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

[TD="align: right"]2012/12/14 18:33[/TD]

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

[TD="align: right"]2012/12/14 18:33[/TD]

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

[TD="align: right"]2012/12/14 18:33[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[hiker95]

qpywsqp,

When you scan a delegate in and out, make sure the active cell is the original/first delegates cell in column A, but, not a new row.

 

[qpywsqp]

Is that possible to have automatic search? Instead of making sure the same delegate and at the active cell?

 

[hiker95]

qpywsqp,

Is that possible to have automatic search? Instead of making sure the same delegate and at the active cell?

When you scan a delegate in and out, you could always do the scanning into cell A2.

If it is the first time you scan delegate1 into cell A2, the next available row below A2, A3 would display the delegate information, and the time in in cell C3.

Then cell A2 would be cleared, and the active cell will be cell A2.

If you scanned the same delegate into cell A2 again, the macro will search below cell A2 for the delegates information, and put the time stamp in cell D3.

Then cell A2 would be cleared, and the active cell will be cell A2, and waiting for the next scan of the next delegate.

Does this make sense?


Or, you can always scan into cell A2, and the information will be stored in another worksheet.