Counting Inventory with Barcode Scanner

[RapidFireGT]

Windows 7
Excel 2007
Wasp WWS800 Wireless Barcode Scanner

First off, I'm not even sure if this is possible.

I've searched through 12 pages of "barcode" results and didn't find much, so I'll ask in this thread. Now, what I am about to ask it most likely too heavy for a complete answer, so even if you can just point me in the right direction, I'd appreciate it greatly.

What I will be doing
We are a wholesaler distributor and send out 2-4 pallets every day on each route, and we have about 5 routes everyday. At the end of each day before pallets are loaded onto the trucks, we count the product on the pallets to make sure the orders have been filled accurately. We would like to use a barcode scanner to scan each product and then populate a list of what products and what quantities are on the pallets.

What I would like Excel to do
I would like to set up a template with 3 columns: barcode, product name, and quantity. I would like to scan an item, the barcode scanner then inputs the barcode number into the "Barcode" column, Excel then cross references this number to match it with its corresponding product name which would then be inserted into the "Product Name" column, and then Excel would increase the quantity by 1 in the "Quantity" column.

Basically, I want insert the product number into excel, which then matches it with a product name and starts counting the number of times I scan that particular item.

Here is a sample spreadsheet of what I imagine such spreadsheet would appear as:

Again, I'm not even sure if this is possible in Excel, and I assume it is too much work for you guys just to spit out the formulas to me to accomplish this. With that said, if you can even point me in the right direction to achieving this, I would appreciate it.

Thanks guys!

 

[mturner262]

I tried everything you suggested and never could get the macros to work. I am on a Mac by the way, not sure if that matters. Tonight I realized that I have excel 2003 on the windows side with parallels and it works perfectly there. The problem I'm running into with that is that everything is clunky and unstable in parallels, so I doubt I can trust it for work because it keeps shutting down on me.

I will figure this out on my end eventually, I just wanted to thank you so much for this! Our POS has no accommodations for counting inventory and I'm managing inventory for 25 stores across 3 states. When I get this working the way that I see it can, it will easily shave 2/3 of the time I have to spend in each store. Thanks again, you are a genius!

 

[brucef2112]

Yeah i agree its the M$ / Mac. You know M$ made it that way.

You may want to get a cheap $50 old laptop running XP just for this inventory thing.
Better than beating your head to fix something that Bill told his peeps to make sure it don't work correctly with that Mac thing.
I've actually had people just give me their old laptops. I use one of them just for this purpose. It had WinXP and Office 2003 on it.
I use solely as an inventory tool to collect the inventory, then put the file on a stick then over to my other computer, do whatever I need with it.


Sure beat the several thousands of dollars for a symbol inventory scanner and system. Unlike the Symbol, in a pinch, the laptop will also get you on the net and more. :}

 

[onbekend]

Hi,

First of all, big thanks to brucef2112 for sharing his sheet. There is just one minor thing i want to change, but can't figure it out myself.

What i want is on the inventory sheet to add a new column with the status (for example: on stock, in repair)
Instead of the price in the sheet "inv taken' i want this fields to be shown while scanning the inventory. This also means i want to get rid of the required field where you have to fill in the price whenever an item is not on the inventory sheet. I do like the required field though, but this needs to be changed to text.

Any ideas..?

 

[brucef2112]

Hi,
First of all, big thanks to brucef2112 for sharing his sheet. There is just one minor thing i want to change, but can't figure it out myself.
What i want is on the inventory sheet to add a new column with the status (for example: on stock, in repair)
Instead of the price in the sheet "inv taken' i want this fields to be shown while scanning the inventory. This also means i want to get rid of the required field where you have to fill in the price whenever an item is not on the inventory sheet. I do like the required field though, but this needs to be changed to text.
Any ideas..?

Take a look at this modified version. TakeInvWithStatus.xls
It functions similar to the original with the following changes.
For existing items found in the Inventory sheet.
It is added to the 'Inv taken' sheet, as before, but now will also include the 'Status' data for that item (found in the Status column next to the Description on the Inventory sheet).

For a New item (not found in your Inventory sheet)
It adds the barcode to the 'Inv taken' sheet, then prompts you for the item name and then the item's 'status'.
It also marks the item as "New" in another column (which was the price column) so you can reconcile these later with your source data.
ie "Why do I have this stuff on a shelf but it's not in my database?"

 

[pifoam]

Dear bruce2112,

I would like to know whether below item can be include into this version?? Thanks..

1. Automatic remove product ID/status/quantity from inventory list using barcode scanner
2. Automatic add in product ID/status/quantity into inventory list using barcode scanner
3. ok and cancel button is not functional while I'm trying to skip adding product description

 

[brucef2112]

Dear bruce2112,

I would like to know whether below item can be include into this version?? Thanks..

1. Automatic remove product ID/status/quantity from inventory list using barcode scanner
2. Automatic add in product ID/status/quantity into inventory list using barcode scanner
3. ok and cancel button is not functional while I'm trying to skip adding product description

1 and 2 : So you scan a barcode, and by scanning it you want the program to figure out if you want to REMOVE entire product or if you want to ADD the entire product? Not happening because I know nothing about AI.

2 [more on this] How is a products ID, Status, and Quantity going to be automatically added to the table? The program only knows a barcode isn't in the master inventory list (stock table). A human will need to manually identify the product, and its status. (part of 3. kinda answers this. In short the human needs to enter the info.) And counting Quantity automatically, of course is what this program is designed to do; and does.

3: ie "Why do I have this stuff on a shelf but it's not in my database?" I'm not sure why you would skip a product that is not in your inventory data (stock table). Seems this would keep it from being found. In most inventory processes, if a product isn't in the stock table, someone would like to know about it, by identifying it by name and how many there are. Its sort of "Hey! I found something, but I don't know about it. Please tell me about it". So if its not found in the stock table, the program is set up to force the human to identify the item by description and status. Remember the program just knows it found a barcode not accounted for. A human must identify it. And once its been ID'd the first time, the program will then continue the count the Quantity of the 'new' item.

Get creative. Nothing stops the human from tricking the program by just typing "x" as the description and "1" as the status, to get past the prompts. After the inventory is done, the creative human could sort by description and find the 'x' records and easily delete all those records. This would ensuring nobody will know they are taking up room in the back of the warehouse until the next inventory!

I do. And I do not want to spend the rest of my life doing nothing but coding every possible, conceivable inventory scenario people ask for.
I created a simple inventory tool. Your creativity in working with the tool, or around it will save me hours of programing.
(ie a skilled creative craftsman knows a hammer isn't just for sinking nails)

For all others, if you have a feature request and I feel it useful to a broader audience I will consider it.

 

[pifoam]

1. Purpose of this inventory list which include barcode scanner function is to record massive new different electronic components?(Manufacturing part number, description and quantity) which have bought from outsource supplier.
2. I just want to make use of this barcode scanner to add in or remove components from the inventory list(As long there's need to issued components to relevant personal for their prototype build) or add in again the same components manufacturing part number if there left over quantity after this prototype build.
3. I think multiplier time of scanning on description, quantity and product ID barcode into inventory list is unavoidable.
4. My initial planning is to create multiple button on userfoam. Each button is meant for different prototype model(As long I click on the respective model, the system will automatic delete quantity from inventory list base on prototype bill of material) and above three question as well.

I need your valuable advise..Thanks

 

[brucef2112]

Your further explanation makes your needs a bit more clear to me.
This would involve additional user forms and additional programming logic.
This takes the scope of your project a bit beyond this simple inventory system.
The changes would be specific for only your need and would not enhance the basic inventory tool for others,
so these enhancements need to be your endeavor to customize the program for your needs.
Creating the additional user forms, and, what looks to me to be significant amount of code, is not something I can offer you without some efforts on your part.
I would be glad to offer basic help but you will need to make an attempt at adding these enhancements and changes to the program yourself.

Regards,
Burce

1. Purpose of this inventory list which include barcode scanner function is to record massive new different electronic components?(Manufacturing part number, description and quantity) which have bought from outsource supplier.
2. I just want to make use of this barcode scanner to add in or remove components from the inventory list(As long there's need to issued components to relevant personal for their prototype build) or add in again the same components manufacturing part number if there left over quantity after this prototype build.
3. I think multiplier time of scanning on description, quantity and product ID barcode into inventory list is unavoidable.
4. My initial planning is to create multiple button on userfoam. Each button is meant for different prototype model(As long I click on the respective model, the system will automatic delete quantity from inventory list base on prototype bill of material) and above three question as well.

I need your valuable advise..Thanks

 

[pifoam]

Dear bruce,

Before I step in further into implement user foam(muti- button for various model link to bill of material) . I would like to edit some function contain inside TakeInvWithStatus as I still new on VBA programming.
I found there other way to edit the TakeInvWithStatus by copy or cut the components information from sheet1 to sheet2(inventory list) after barcode scanning. Treat this as add components or remove components.
I still got problem on debugging the VBA and it keep on returning error. Would appreciate that you could help me on this? Thanks...

There no attachment icon available for me to upload after edit TakeInvWithStatus files?
Example:

'After the data is transferred to Sheet2 we can clean up the first sheet (Sheet1) using the following VBA code:
Sub deleteRowsTransferred()
Dim i, LastRow
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row

For i = 2 To LastRow
If Sheets("Sheet1").Cells(i, "E").Value = "" Then
Sheets("Sheet1").Cells(i, "E").EntireRow.Delete
End If
Next i
End Sub


Private Sub Workbook_Open()
Dim i, LastRow

LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row
Sheets("Sheet2").Range("A2:I500").ClearContents
For i = 2 To LastRow
If Sheets("Sheet1").Cells(i, "E").Value = "" Then
Sheets("Sheet1").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
Next i
End Sub

 

[pifoam]

Dear bruce,

Good thing that this TakeInventoryWithRunningScanSheet come along with Scans with DateTime Stamp which use to record down the date, time, product information and to trace who is the one using the software by recording the user name.