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

Q

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?
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Dookadooka4,

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


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.
 
Upvote 0
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?
 
Upvote 0
Hi Hiker,


Below attachment is before.

Sheet1

ABCDEFGH
CodeNameTime InTime OutTime InTime OutTime InTime Out
MC10008Jacky
MC10080Rebecca
PC10001Rachel
PC10100Jeffrey
PC10108Steve
MC10988Rosy
PC10945Johnson
MC10884Jackson
MC10886Ravy

<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




And this will be the after.

Sheet1

ABCDEFGHIJ
CodeNameContactEmailTime InTime OutTime InTime OutTime InTime Out
MC10008Jacky
MC10080Rebecca
PC10001Rachel
PC10100Jeffrey
PC10108Steve
MC10988Rosy
PC10945Johnson
MC10884Jackson
MC10886Ravy

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 192px"><col style="WIDTH: 91px"><col style="WIDTH: 91px"><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
 
Upvote 0
qpywsqp,


Sample results with the updated Worksheet_Change Event:


Excel Workbook
ABCDEFG
1CodeNameContactEmailTime InTime OutTime In
2MC10008Jacky
3MC10080Rebecca2/4/2013 8:412/4/2013 8:43
4PC10001Rachel
5PC10100Jeffrey2/4/2013 8:42
6PC10108Steve
7MC10988Rosy
8PC10945Johnson
9MC10884Jackson
10MC10886Ravy
11QPYWSQP2/4/2013 8:422/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.
 
Upvote 0
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"?
 
Upvote 0
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"?
Click to expand...

Yes.
 
Upvote 0
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?
Click to expand...


Sorry, what "Macro" do you refer to?
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
Developing a Lot Tracking Inventory system with QR Codes/Barcodes and Updating Ability
Replies
0
Views
344
Easilyy
E
A
  • Question Question
Help with scanning barcodes into an excel invoice
2
Replies
12
Views
1K
RoryA
RoryA
SkywardPalm
  • Question Question
Slow Typing Response from Bluetooth Scanner
Replies
3
Views
1K
Candyman8019
C
D
  • Solved
Time stamp using VBA
Replies
5
Views
1K
data808
D
D
  • Question Question
Barcode scanning tool
Replies
0
Views
963
D3FCON
D

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

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