Custom Barcode Inventory System Using Excel

[Nikkolas Camp]

So, I'm wanting to build a custom inventory spreadsheet to better track out material in house. We have a process to tracking our inventory, it being tracked in total square yards. We currently do our inventory the old fashion way, that being with our trusty pencil and paper, read all tags starting with identifying product, amount of product on one skid, and lastly the total square yards on pallet. We record that information and move on to the next product. We have a decent size warehouse, but even if it were far smaller I'm sure you can imagine how tedious this process is. So what I am capable of doing to creating barcodes containing all information in regards to the material.

Ex. Product ID, Lot No., Width, Length, Rolls per Pallet, Rolls per Shipment, Sq. Yds., PO No., Date Received, and lastly supplier.

I want to create a spreadsheet where all I've listed above is a column; and when I scan said barcode, excel inputs all this information in each cell corresponding to the information the scanner has read. Example below.

So in short, I want excel to be able to input all information provided by the barcode in the proper column once scanned. I hope this is enough information to help the community help me get to my goal.

Hope to hear from you all soon,
Nikkolas C.

 

[Sequoyah]

Hi Nikkolas,
here is my new attempt, I cannot read the 128 code with my scanner but with the QR code it seems to me that it works. I updated the code using the asterisk as a separator instead of a fixed length used in the previous version. The code is obtained with the macro recorder, with an adaptation to make the range dynamic and locate the first free row in the table.

FileDropper

Sub DoAction()
    
Dim tbl             As ListObject
Dim LastRow         As Integer
Dim Rng             As Range
    
Set tbl = ActiveSheet.ListObjects("Table1")

'Find last row in column A
LastRow = tbl.Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

'writes the code in the first free cell
tbl.DataBodyRange(LastRow, 1) = frmBarcode.TextBox1

'Split the text usink * as separator
Set Rng = tbl.DataBodyRange(LastRow, 1)
Rng.TextToColumns Destination:=Rng, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="*", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1)), _
        TrailingMinusNumbers:=True

'clear textbox
frmBarcode.TextBox1 = ""

End Sub

 

[Nikkolas Camp]

Hi Nikkolas,
here is my new attempt, I cannot read the 128 code with my scanner but with the QR code it seems to me that it works. I updated the code using the asterisk as a separator instead of a fixed length used in the previous version. The code is obtained with the macro recorder, with an adaptation to make the range dynamic and locate the first free row in the table.

I am currently playing around with the new file. So far, I'm very impressed and would love to learn the process. Ill reply soon with results and an idea I had for this venture soon.

 

[Nikkolas Camp]

So this morning, I had a chance to play with the file you provided @Sequoyah and it works decently well as long as you don't scan too fast. Every once in a while it will RETURN and move to the next row entering some data in the wrong position, though Like I just stated, I think it was due to me scanning the item too fast. As I played around with it I decided to make it a bit easier and knock out 70% of the columns to reduce it down to only Product ID, Rolls/Skid, and SqYds. Our inventory is all accounted by square yards, so I wanted to possibly make this process a bit easier eliminating the columns that were irrelevant.

All this leads to my new question, and Im going to try my best to put this into words so please feel free to ask questions if I need to better explain. Is it possable to scan a QC label that would in turn tell excel to:
A) LOCATE the product ID on a spreadsheet
B) Then enter the data in the prober cells (Rolls/Skid and SqYds)
C) And If more than 1 of the same items is scanned, would add the value both Rolls/Skid and SqYds without creating a new line item

We have a premade spreadsheet for our monthly inventory check, so the plan is to implement your Macro formula to the actual spreadsheet once you teach me what you can with the program itself.

 

[Sequoyah]

Hi,
wait time after scan is now set to one second, try setting it to two seconds in this line of code (in the userform) and the scanning speed problem should be solved. Let me know if it works.

DoTime = Now() + TimeValue("00:00:02")

Probably for at least a couple of days I won't be able to post in the forum due to personal engagements, see you soon

 

[Nikkolas Camp]

Hi,
wait time after scan is now set to one second, try setting it to two seconds in this line of code (in the userform) and the scanning speed problem should be solved. Let me know if it works.

DoTime = Now() + TimeValue("00:00:02")

Probably for at least a couple of days I won't be able to post in the forum due to personal engagements, see you soon

I cant seem to find the line of code you are referring to nor can I figure where to put it. Thanks for everything thus far! Talk to you in a few(ish) days!

 

[Sequoyah]

Hi Nikkolas,

to view or edit the code you need to access the VBA editor, just press Alt + F11 keys together. Then you can click on the userform to view the code associated with the textbox or click on the Module1 funder the VBA project.

I thought of a different solution that does not require splitting the code into columns. Here is the link to new file FileDropper

First scan your codes in the first column of the Barcode sheet, pressing the button will create a new sheet with the summary of the items and the sum of the square yards. Do not manually delete, move or rename sheets otherwise the macro will not work. Barcode readers can usually work in inventory mode, all scanned data will be stored directly into the buffer memory of the device so you can upload them to computer in batches.