RFID student attendance worksheet

[yvautrin]

Hello World!

My VBA/Macro skills being very poor, I am lost facing the problem I have to solve!

I need to setup a student class attendance system, using a USB RFID reader, and I was thinking about Excel, I found some stuff on the web, but nothinf fulfilling my needs....

What I need :

Student swipe their cards when walking in the classroom, a timestamp is created in a column "punch in", if they swipe card again after a certain delay (let's say 5 minutes), another stamp is added in a new column "punch out".

In the excel file, a worksheet containing students names/class/RFID tag number where the code/macro will do a lookup to attach student name to rfid_ID.

Not sure my explanations are clear....but your help will be appreciated!

Thanks for your support!

 

[C Moore]

It depends on what your RFID reader returns/interacts with Excel. I've seen a QR 'gun' return the ID and time recorded automatically. If it is simply an input of an ID each time you're most likely going to want to use a worksheet change event to trigger the macro.

 

[Fluff]

Cross posted Student attendance tracking RFID

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

 

[yvautrin]

It depends on what your RFID reader returns/interacts with Excel. I've seen a QR 'gun' return the ID and time recorded automatically. If it is simply an input of an ID each time you're most likely going to want to use a worksheet change event to trigger the macro.

Hi,
my rfid usb reader returns a 8 digit screen, like a "copy and paste"

 

[yvautrin]

Cross posted Student attendance tracking RFID

While we do allow Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered elsewhere.

Sorry for my mistake not following the rules....

I posted on an other Excel forum, with the link you have included....

School starts next week, and I need to set this up asap, that's why I did it....My apologize!

 

[Fluff]

No problem, just please remember to supply links should you cross post in future.

 

[C Moore]

There isn't an Excel add-in or anything from the manufacturer that can be applied? Copy/paste almost defeats the purpose of having the ability to scan.

 

[yvautrin]

it's not really copy paste, the usb reader acts like barcode scanner, it read the 8 digits of the rfid card and input them as a keyboard would do, for example, if i open a blank wordpad document, and scan a card, it will "insert" the digits in the document.

 

[NoSparks]

the usb reader acts like barcode scanner, it read the 8 digits of the rfid card and input them as a keyboard would do,

In that case, if you have an Excel spreadsheet open the 8 digits would/should be written into the active cell.
Set up a test workbook where you have all your scan IDs listed in column A below row 2.
Right click on the sheet tab, select 'View Code' and paste this macro into the editor window that opens.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim findString As String, fndScan As Range, lastCol As Long
   
'limit to a single cell
If Target.CountLarge <> 1 Then Exit Sub
'look at specific cell
If Target.Address <> "$B$2" Then Exit Sub
'check the cell is not empty
If Target.Value = "" Then Exit Sub
    
On Error GoTo ErrorHandler   'make sure events always get re-enabled

'OK got to here so work with what's in the cell
findString = Target.Value

'find this in column A
With Sheets("Sheet1")
    Set fndScan = .Range("A:A").Find(What:=findString, LookIn:=xlValues, LookAt:=xlWhole, _
             SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
    'if found fndScan will Not be Nothing it will be findstring
    If Not fndScan Is Nothing Then
        'just to show where found, remove or comment out after testing
        MsgBox findString & " was found at  " & fndScan.Address
        
        'determine the last column used on that row
        lastCol = .Cells(fndScan.Row, .Columns.Count).End(xlToLeft).Column
        
        'to prevent this procedure from calling itself when a cell changes
        Application.EnableEvents = False
        
        'check for a date/time in last column
        If IsDate(.Cells(fndScan.Row, lastCol)) Then
            'check it was more than 5 minutes ago (hours, minutes, seconds)
            If Now - TimeSerial(0, 5, 0) > .Cells(fndScan.Row, lastCol) Then
                .Cells(fndScan.Row, lastCol + 1) = Now
            End If
        Else
            'it's first time scanned
            .Cells(fndScan.Row, lastCol + 1).Value = Now
        End If
    
        'clear this scan ready for next
        .Range("B2").ClearContents
        .Range("B2").Select
        
        're-enable events
        Application.EnableEvents = True
    Else
        'fndScan is Nothing ie: findstring wasn't found
        MsgBox findString & "  was not found."
    End If

End With
Exit Sub

'make sure events always get turned back on in case of error
ErrorHandler:
MsgBox "Error #  " & Err.Number, 0, "Error"
Application.EnableEvents = True

End Sub

Save this test workbook as an .xlsm macro enabled workbook.
Select cell B2 and 'scan' in from the reader to see if it works.
The code should be commented enough to allow you to follow what's happening.
For example why the message box and how to stop it.

Hope this helps.

 

[yvautrin]

Thanks for trying to help me, really appreciating!

May be I was not clear in my explanations, here is how i see things:

First sheet of the workbook, i start by clicking in A2, then when i swipe a card, it reads the id, look in the table on sheet number 2 for name and class, and populate column B and C with student informations, then creates a time stamp in column D for clock-in, if the same card is swiped again in less than 2 minutes, nothin should happen, if same card is swiped after 2 minutes, another timestamp should be added in column E for clock-out.

Hope it makes more sense....?

Swiped ID	Name	Class	Clock-in	Clock-out
8528914​	A Smith	Y05	13/09/2020 11:29:06​