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]

Disregard the portion pertaining to formulas on the sheet 2 display, it is meaningless to the solution.

I inadvertently included it, sorry.

Howard

 

[zmunns]

Hey Howard

I'm looking to implement a project very similar to the discussion that's been going on in this thread for a long while, specifically the tool in and out problem. My company processes a large amount of inventory and currently struggles to find efficiency and improvement in tracking the inventory since they do most of the work manually. I recently offered up the idea of working with scanners and an excel that would show when and where an item was scanned in, when it passes through QA, when it gets shipped out, and when it arrives at its destination. I have an associated workbook of product sku's along with unique identifiers for the different scanning locations including receiving, QA, shipment, and delivery. My goal is to build a spreadsheet that, when the barcode is scanned into cell A1 and the location code is scanned into B1, it references the SKU and location list and automatically renders the item scanned, where it was scanned, and when it was scanned. I would need to be able to scan both the location code and product code and marry the data to show where the inventory is and if there are missing goods.

I've worked on a basic spreadsheet but am not sure how to marry the data and in a way that would make the most sense. I've attached what I've worked on and would greatly appreciate your help and/or thoughts.

https://drive.google.com/file/d/1KdoI4oi8Z17h0EkHV4JOBDkuaB2Uq-Kd/view?usp=sharing

Thanks

 

[L. Howard]

Just to let you know, I have no barcode scanner and have not worked with a scanner. All the solutions I have offered are based on a barcode that when scanned into an Excel cell will react the same way as if you typed some data into a cell and hit ENTER.

With that in mind, start by scanning a barcode into cell A1, and a location code into B1...
1. What sheet will this happen on, the name of the sheet?
2. Will both the barcode AND location code need to be scanned in before action takes place OR will each react to their individual scan?
3. What should happen and on what sheet/s when the scan in A1 takes place?
4. The same question for the location code in B1, what happens and what sheet/s?

In plain talk, tell what you want to appear or be listed or noted... etc. on what sheet, row, column and such.

I have the sheet you linked posted, with the four worksheets.
Probably will use formulas and VBA???

Howard

 

[zmunns]

Thanks Howard. I understand you don't work with scanners, it's more what happens in the excel worksheet after the scan has taken place

Since we will only have one scanner, the scans will come into the second worksheet named "Scan Output"

Each will react to their individual scan upon input

Once a scan is input into A1, it will be copied into the sheet "inventory scan" with the date and time stamp and then copied into the "inventory status" sheet along with the corresponding location scan that coincides with the date/time stamp with a variation of a couple seconds. The same happens to the location scans as they are input through the "scan output" sheet, transferred to the "location scan" sheet as raw data, then transposed to the "inventory status" sheet alongside the inventory it is associated with

Ultimately, what I'd like to happen, is for our warehouse team to scan both the inventory and where they're located, have those two data points rendered in excel. Once in excel, I'd like the two points to be date/time stamped, then merged to show what inventory was scanned and where it was scanned along with the time that it was scanned at

Here is an updated link with the addition of the "Scan Output" sheet

https://drive.google.com/file/d/1KdoI4oi8Z17h0EkHV4JOBDkuaB2Uq-Kd/view?usp=sharing

Thanks

Zach

 

[zmunns]

Another thought after talking to the team. For the location scan, there will be 4 separate locations assigned to different areas of the process. It would be nice to simply scan the location code once when starting the scanning of the inventory, then apply that code to the rest of the inventory scans for a set period of time or until the user identifies they've completed a set to avoid duplicated entry if at all possible.

 

[L. Howard]

Okay, I think I got most of that.

On sheet Data Input the Location Barcode numbers are all identical for the seven Location Names. Is this correct, or should they be different for each Location Name?

Howard

 

[zmunns]

They should be different for each location.

Thanks

 

[L. Howard]

Just to note, on the workbook you linked, a couple of the sheet names had trailing spaces in the name tabs. I assumed they were there by mistake and removed them. The trailing (or leading) spaces can be a troublesome issue if the code is written not knowing they exist.



Try this change_event code which you will copy to the sheet Scan Outputs module.
The code will run if either cell A2 or B2 on sheet Scan Outputs is changed, which would be whenever you scan a barcode into either.

Here is a link to your slightly modified workbook using the code below. (sheet order & name tab spaces)
https://www.dropbox.com/s/f2t4b4x6o87x04f/Barcode Inventory Worksheet DRP_BOX.xlsm?dl=0

Howard

Option Explicit

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

Dim LRow As Long
Dim aRng As Range, rngFnd As Range
Dim myFnd As String

myFnd = Target

  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

Select Case Target.Address

  Case Is = "$A$2"
  
  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, 5).Copy Sheets("Inventory Scan").Range("A" & Rows.Count).End(xlUp)(2)
       Sheets("Inventory Scan").Range("A" & Rows.Count).End(xlUp).Offset(, 5) = Format(Now(), "yyyy-mm-dd hh:mm:ss")
       
       rngFnd.Offset(, 1).Resize(1, 4).Copy Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp)(2)
       Sheets("Inventory Status").Range("A" & Rows.Count).End(xlUp).Offset(, 4) = Format(Now(), "yyyy-mm-dd hh:mm:ss")
       
      Else
        MsgBox "No match found."
           
      End If
    End With
  
  
  Case Is = "$B$2"
 
    With Sheets("Data Input")
      LRow = Sheets("Data Input").Cells(Rows.Count, "H").End(xlUp).Row
   
      Set rngFnd = Sheets("Data Input").Range("H3:H" & LRow).Find(What:=myFnd, _
                     LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
                     SearchDirection:=xlNext, MatchCase:=False)
      If Not rngFnd Is Nothing Then
    
         rngFnd.Resize(1, 2).Copy Sheets("Location Scan").Range("A" & Rows.Count).End(xlUp)(2)
         Sheets("Location Scan").Range("A" & Rows.Count).End(xlUp).Offset(, 2) = Format(Now(), "yyyy-MM-dd hh:mm:ss")
        Else
        MsgBox "No match found."
 
      End If
    End With
  
  
  Case Else
    '
End Select
End Sub

 

[zmunns]

Thanks Howard. This works great. The only thing I'm seeing that I could use your help on is when a scan takes place, the resulting "location" in the "inventory status" page is not the destination it was scanned, rather the destination it has listed in the data input tab. Also, when scanning the codes in, can the cell automatically revert back to A2 once it has been recorded?

This is a huge help. Thank you so much

Zach

 

[L. Howard]

The only thing I'm seeing that I could use your help on is when a scan takes place, the resulting "location" in the "inventory status" page is not the destination it was scanned, rather the destination it has listed in the data input tab.

Not understanding "the location destination scanned" snafu.

There are two separate scans that take place, cell A2 and cell B2 on sheet Scan Outputs.
So when you say "... when a scan takes place" we should know in which cell is the scan taking place.

Using the workbook I linked and the original phony data on the sheets I supplied...

If I scan the number 1 in cell A2, then I get this on sheet Inventory Scan, A3 to F3.
1 / Style # 3 / Shirt / L / Atlanta / date-time

And I get this on sheet Inventory Status A2 to E2.
Style # 3 / Shirt / L / Atlanta / date-time

Then if I scan L1 into the B2, then I get this on sheet Location Scan, A3 to C3.
L1 / Receiving / date-time

What should change to correct the location error. What sheet, what cell, what location should show where on what sheet?

...can the cell automatically revert back to A2 once it has been recorded?

I presume when you scan into cell A2 the scan acts as if ENTER has been hit and A3 is the activecell. And you want A2 to be the activecell after a scan?
Do you want B2 to do the same or ALWAYS go to back cell A2 after EVERY scan in A2 and B2?

Howard