Page 1 of 5 123 ... LastLast
Results 1 to 10 of 43

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

This is a discussion on Scan barcode to excel with date & time stamp in & out. within the Excel Questions forums, part of the Question Forums category; Hi all. I have research around the internet and finally found out Macro able to solve my issue. But im ...

  1. #1
    New Member
    Join Date
    Dec 2012
    Posts
    26

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

    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?

  2. #2
    Board Regular cyrilbrd's Avatar
    Join Date
    Feb 2012
    Posts
    2,982

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

    Quote Originally Posted by qpywsqp View Post
    ...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.
    V14.4.3
    V14.0.7128.5

  3. #3
    New Member
    Join Date
    Dec 2012
    Posts
    26

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

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

  4. #4
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    10,742

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

    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 by hiker95; Dec 3rd, 2012 at 07:26 AM.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  5. #5
    New Member
    Join Date
    Dec 2012
    Posts
    26

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

    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
    1 Name Time In Time Out Time In Time Out Time In Time Out Time In Time Out Time In Time Out
    2 Jeffrey 12/4/2012 10:39
    3 Steve 12/4/2012 10:40 12/4/2012 10:50 12/4/2012 11:40
    4 Jacky 12/4/2012 10:41
    5 Penny 12/4/2012 10:41
    6 Cazz 12/4/2012 10:42
    7


    Excel tables to the web >> Excel Jeanie HTML 4
    Last edited by qpywsqp; Dec 3rd, 2012 at 09:58 PM. Reason: Wrong attachment. Add in Screenshot.

  6. #6
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    10,742

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

    qpywsqp,


    Sample worksheet Sheet1:


    Sheet1

     ABCDEF
    1Name Time InTime OutTime InTime Out
    2      
    3      
    4      
    5      
    6      
    7      


    Excel tables to the web >> Excel Jeanie HTML 4




    After some delegates have come and gone:


    Sheet1

     ABCDEF
    1Name Time InTime OutTime InTime Out
    2Jeffrey 12/4/2012 16:22   
    3Steve 12/4/2012 16:2212/4/2012 16:2312/4/2012 16:24 
    4Jacky 12/4/2012 16:22   
    5Penny 12/4/2012 16:22   
    6Cazz 12/4/2012 16:2212/4/2012 16:23  
    7      


    Excel tables to the web >> Excel Jeanie HTML 4




    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.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  7. #7
    New Member
    Join Date
    Dec 2012
    Posts
    26

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

    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
    1 Name Time In Time Out Time In Time Out Time In Time Out Time In Time Out
    2 489299027192.00 2012/12/14 18:32
    3 4894299027192.00 2012/12/14 18:33
    4 4894299027192.00 2012/12/14 18:33
    5 4894299027192.00 2012/12/14 18:33
    6 4894299027192.00 2012/12/14 18:33


    Excel tables to the web >> Excel Jeanie HTML 4

  8. #8
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    10,742

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

    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.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

  9. #9
    New Member
    Join Date
    Dec 2012
    Posts
    26

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

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

  10. #10
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    10,742

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

    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.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007

Page 1 of 5 123 ... LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com