Barcode scanning with Excel

[Chief1904]

My office is trying to go paperless with our hunting program and issue ID cards with bar codes. I'm trying to have a barcode scanner be able to scan the ID card and pull up their information in excel or access. How can I do this? My boss doesn't want to spend $12,000 on a full setup of software and hardware. Any help would be great!

 

[L. Howard]

Replace the entire code with this. Me thinks it does what you want.
Seems to fill in the headers on the sheets as named, with the data from the Data Input sheet.

Howard

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("$A$2")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Dim LRow As Long, LRowX As Long
Dim aRng As Range, rngFnd As Range, rngFndX As Range
Dim myFnd As String, myFndX As String

myFnd = Target
myFndX = Target.Offset(, 1)

  If myFnd = "" Then
    Exit Sub
  ElseIf IsNumeric(myFnd) Then
    myFnd = Val(myFnd) '/ converts a "text" number to a value
  Else
    '/ is text and that is okay
End If

   With Sheets("Data Input")

    LRow = Sheets("Data Input").Cells(Rows.Count, "A").End(xlUp).Row
   
    Set rngFnd = Sheets("Data Input").Range("A2:A" & LRow).Find(What:=myFnd, _
                     LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, MatchCase:=False)
                     
    If Not rngFnd Is Nothing Then
  
       rngFnd.Resize(1, 6).Copy Sheets("Inventory Scan").Range("A" & Rows.Count).End(xlUp)(2)
       Sheets("Inventory Scan").Range("A" & Rows.Count).End(xlUp).Offset(, 6) = Format(Now(), "yyyy-mm-dd hh:mm:ss")
       
       rngFnd.Offset(, 1).Resize(1, 5).Copy Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp)(2)
       Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp).Offset(, 6) = Format(Now(), "yyyy-mm-dd hh:mm:ss")
       
      Else
        MsgBox "No " & myFnd & " match found."
           
      End If
      
      
           LRowX = Sheets("Data Input").Cells(Rows.Count, "N").End(xlUp).Row
   
      Set rngFndX = Sheets("Data Input").Range("N3:N" & LRowX).Find(What:=myFndX, _
                     LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, MatchCase:=False)

   If Not rngFndX Is Nothing Then
    
         rngFndX.Resize(1, 2).Copy Sheets("Location Scan").Range("A" & Rows.Count).End(xlUp)(2)
         rngFndX.Offset(, 1).Copy Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp).Offset(, 5)
         Sheets("Location Scan").Range("A" & Rows.Count).End(xlUp).Offset(, 2) = Format(Now(), "yyyy-MM-dd hh:mm:ss")

       Else
        MsgBox "No " & myFndX & "match found."
 
      End If
    End With
  
[A2].Activate
End Sub

 

[Bond00]

Hi Chief,

I'll make some assumptions on a possible solution to your scan-in and get info-back.

Scan the ID card number into A1.

Elsewhere on the Excel sheet you have a data base (table if you will) where all the ID card numbers of the hunters are listed, lets say column F.
In columns G, H, I, J, K are five items (you can have more if needed) of info that relate to the ID number in F. (Name, Address, Telephone No., Expire date of ID card, Blood Type, whatever...)

In the cells where you want to return that info you can use a VLOOKUP formula that will look something like this:

=VLOOKUP(A1,$F$1:$K$50,{2,3,4,5,6},0)

So the formula will take the value in A1 and look for it in column F and when a match is noted, returns the information in columns G, H, I, J, K.

Is this some what like what you are after?

I don't know anything about Access.

Regards,
Howard

Can i see an example of this? i think it would help me a lot in what im trying to do here: https://www.mrexcel.com/forum/excel...-into-cells-based-lookup-partial-keyword.html

 

[L. Howard]

Can i see an example of this? i think it would help me a lot in what im trying to do here: https://www.mrexcel.com/forum/excel...-into-cells-based-lookup-partial-keyword.html

Go to page 8 in this thread, and see Posts # 79, 80 (and 81 on page 9). There is an HTML example of the array entered VLOOKUP that looks like it could work with the data in you mention in your linked workbook, (where the headers are in the same row and across several columns).

Howard

 

[Bond00]

cool thanks, i figured it out

 

[zmunns]

Thanks the output works perfectly. The only other issue is now that I've updated the code and it only identifies about 30 of the needed Input #'s. If that can be expanded to encompass all the data points that are in the system, it would be delightful. Also after updating the code, the A2.activate code doesn't function ie when a number is input, it goes to A3 as opposed to doing what it did before which was automatically revert back to A2.

Thanks

 

[L. Howard]

The only other issue is now that I've updated the code and it only identifies about 30 of the needed Input #'s. If that can be expanded to encompass all the data points that are in the system, it would be delightful.

Also after updating the code, the A2.activate code doesn't function ie when a number is input, it goes to A3 as opposed to doing what it did before which was automatically revert back to A2.

I see there are about 3100 +- SKU in column A of the Data Input sheet. What do you mean by not getting "encompassed"?
On the example workbook from your link I have been using drop downs in A2 and B2 to simulate a barcode scan-in. The A2 drop down only has a few of the data points to use as examples. You will be using an actual barcode scan-in in these two cells, remembering to scan into B2 first, then A2 second. The code will process the two entries as it now does, which you say is working perfect.

Discard the drop downs in both cells and use the barcode scanner. If the A2 cell scanned-in number is in column A of Data Inputs then you are in business, else you will get a message alert that there is no match.

The return to A2 works on my copy of the workbook you linked (with the revised code as in Post #101 ).

Howard

 

[zmunns]

Thanks for your help with everything Howard. I do have one issue. We're using the tool now and it's working well. However, when "scanning" the barcodes in, it's spitting out a "not found" error despite the barcode value being in column A of the data input spreadsheet. I'm not sure if I'm doing something wrong, but would appreciate your help

Thanks

 

[L. Howard]

You would need to verify that the barcode value and the value in column A are EXACTLY the same. Maybe a leading or trailing space in column A values. You can check that with a formula in a non used cell where you would enter =[the cell in column A] = [the cell of the barcode scan] would look something like this =F1=B2. Should return TRUE if exact match.

Also, the values in column A "may" be numbers stored as text, while the scanned value is converted to a number by this portion of the code.

If myFnd = "" Then
    Exit Sub
  ElseIf IsNumeric(myFnd) Then
    myFnd = Val(myFnd) '/ converts a "text" number to a value
  Else
    '/ is text and that is okay
End If

Howard

 

[L. Howard]

...it's spitting out a "not found" error despite the barcode value being in column A of the data input spreadsheet. I'm not sure if I'm doing something wrong, but would appreciate your help

Thanks

Just wondering if you found the problem?

Howard

 

[zmunns]

It is still spitting out the error message... I ran the accuracy test and the outputs are the same yet when the number is plugged into the "scan output" sheet, it is not liking it. Do you have any ideas to fix this?

Thanks

Zach