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

qpywsqp · Dec 2, 2012

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?

Dookadooka4 · Jan 17, 2013

Hiker95,

Wow this is exactly as what I am trying to do. We have an expo coming up and I need to set up a system to track the entrance and exit of our vendors. I am currently stumped and need some help with setting up something just like this.

hiker95 · Jan 17, 2013

Dookadooka4,

I am currently stumped and need some help with setting up something just like this.

Please do not post your questions in threads started by others - - this is known as thread hijacking.

Always start a new thread for your questions and, if you think it helps, provide a link to any other thread as a reference.

Start a new thread for your question and be sure to give it a search friendly title that accurately describes your need.

After you start your own New Post, send me a Private Message containing the link to your New Post, and I will have a look.

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.

Dookadooka4 · Jan 17, 2013

Sorry, no hijacking intended. Will post a new thread, thanks!

qpywsqp · Feb 1, 2013

Hi Hiker,

Im back here to enquiry more. If i would like to have column A to store the code and Column B, C, D to be others data, all this is pre existing data.

How can i change the code? Is that necessary to redo the who coding?

hiker95 · Feb 1, 2013

qpywsqp,

Is that necessary to redo the who coding?

Yes.

Please supply screenshots of before and after?

qpywsqp · Feb 3, 2013

Hi Hiker,

Below attachment is before.

Sheet1

	A	B	C	D	E	F	G	H
1	Code	Name	Time In	Time Out	Time In	Time Out	Time In	Time Out
2	MC10008	Jacky
3	MC10080	Rebecca
4	PC10001	Rachel
5	PC10100	Jeffrey
6	PC10108	Steve
7	MC10988	Rosy
8	PC10945	Johnson
9	MC10884	Jackson
10	MC10886	Ravy

Excel tables to the web >> Excel Jeanie HTML 4

And this will be the after.

Sheet1

	A	B	C	D	E	F	G	H	I	J
1	Code	Name	Contact	Email	Time In	Time Out	Time In	Time Out	Time In	Time Out
2	MC10008	Jacky
3	MC10080	Rebecca
4	PC10001	Rachel
5	PC10100	Jeffrey
6	PC10108	Steve
7	MC10988	Rosy
8	PC10945	Johnson
9	MC10884	Jackson
10	MC10886	Ravy

Excel tables to the web >> Excel Jeanie HTML 4

hiker95 · Feb 4, 2013

qpywsqp,

Sample results with the updated Worksheet_Change Event:

Excel Workbook

A

B

C

D

E

F

G

1

Code

Name

Contact

Email

Time In

Time Out

Time In

2

MC10008

Jacky

3

MC10080

Rebecca

2/4/2013 8:41

2/4/2013 8:43

4

PC10001

Rachel

5

PC10100

Jeffrey

2/4/2013 8:42

6

PC10108

Steve

7

MC10988

Rosy

8

PC10945

Johnson

9

MC10884

Jackson

10

MC10886

Ravy

11

QPYWSQP

2/4/2013 8:42

2/4/2013 8:43

12

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, 02/04/2013
' 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 <= 5 Then
      Cells(Target.Row, 5) = Format(Now, "m/d/yyyy h:mm")
    ElseIf lc > 4 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
      If lc < 5 Then
        Cells(fr, 5) = Format(Now, "m/d/yyyy h:mm")
      ElseIf lc > 4 Then
        Cells(fr, lc + 1) = Format(Now, "m/d/yyyy h:mm")
      End If
      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

Remember - you can only have one Worksheet_Change Event within a worksheet.

Then scan in the Code as before.

qpywsqp · Feb 4, 2013

Hi Hiker,

The code work perfect. May i ask whether the code have any maximum column capping?

As for the row, if i wanted to increase. Am i just change the value of "A2:A3000" to any number i want to be the end row as in like "A2:A5000"?

hiker95 · Feb 4, 2013

qpywsqp,

Thanks for the feedback.

Glad I could help.

Am i just change the value of "A2:A3000" to any number i want to be the end row as in like "A2:A5000"?

Yes.

hongdiha · Apr 28, 2013

qpywsqp said:
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?

Sorry, what "Macro" do you refer to?

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

qpywsqp

New Member

Dookadooka4

New Member

Excel Facts

hiker95

Well-known Member

Dookadooka4

New Member

qpywsqp

New Member

hiker95

Well-known Member

qpywsqp

New Member

hiker95

Well-known Member

qpywsqp

New Member

hiker95

Well-known Member

hongdiha

New Member

Similar threads

Forum statistics

Share this page

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

New Member

New Member

Excel Facts

Well-known Member

New Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock