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?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
...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.
 
Upvote 0
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.
 
Last edited:
Upvote 0
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

ABCDEFGHIJKL
1Name Time InTime OutTime InTime OutTime InTime OutTime InTime OutTime InTime Out
2Jeffrey 12/4/2012 10:39
3Steve 12/4/2012 10:4012/4/2012 10:5012/4/2012 11:40
4Jacky 12/4/2012 10:41
5Penny 12/4/2012 10:41
6Cazz 12/4/2012 10:42
7

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


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


Sample worksheet Sheet1:


Excel Workbook
ABCDEF
1NameTime InTime OutTime InTime Out
2
3
4
5
6
7
Sheet1





After some delegates have come and gone:


Excel Workbook
ABCDEF
1NameTime InTime OutTime InTime Out
2Jeffrey12/4/2012 16:22
3Steve12/4/2012 16:2212/4/2012 16:2312/4/2012 16:24
4Jacky12/4/2012 16:22
5Penny12/4/2012 16:22
6Cazz12/4/2012 16:2212/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


Code:
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.
 
Upvote 0
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

ABCDEFGHIJ
1Name Time InTime OutTime InTime OutTime InTime OutTime InTime Out
2489299027192.00 2012/12/14 18:32
34894299027192.00 2012/12/14 18:33
44894299027192.00 2012/12/14 18:33
54894299027192.00 2012/12/14 18:33
64894299027192.00 2012/12/14 18:33

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


Excel tables to the web >> Excel Jeanie HTML 4
 
Upvote 0
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.
 
Upvote 0
Is that possible to have automatic search? Instead of making sure the same delegate and at the active cell?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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