I need help formatting excel for barcode scanning?

I need help formatting excel for barcode scanning?
I use excel to scan in inventory starting with the product number then the serial number. Instead of all the barcodes being scanned into one column, I want to know if there is a way to get excel to take the first scanned item and assign it to one column and take the next item and assign it to the next column.

For example Instead of scanning this way

product 1
serial 1
product 2
serial 2
product 3
serial 3

I would like excel to scan this way
product 1 serial 1
product 2 serial 2
product 3 serial 3

Best answer:

You could try using a USB wand-type barcode reader that can be used in “keyboard” mode together with a spreadsheet and it is much the same as visually reading the code and manually typing it in – except much, much faster and much more accurate.

Then set up a few very simple macros that work with the barcode eg one would take the code and then jump to the inventory line associated with it, creating a new line if that code is not present or displaying the line if it is.

I’m not sure if this is exactly what you are after, but hope it helps.

Tags: , , , , ,

Under Forum

3 Comments for I need help formatting excel for barcode scanning?

  • 1. Bill F  |  March 18th, 2005 at 9:49 pm

    You could do data formatting in the barcode scanner. If the barcode symbologies for the product barcode and serial number barcode are different, you could do a data format in the scanner that adds a tab suffix to the product number and a carriage return suffix to the serial number. That way when you scan a product code the data gets entered followed by a tab which would take you to the adjacent field. When you scan a serial number the data gets entered followed by an enter, which would drop down to the next row.

    Most barcode scanners can be programmed this way. You don’t say which model you have so I can’t say if yours is capable or not. If you don’t have a users guide the best bet is to call the manufacturer of the scanner and ask them to help you.

    If you want to email me the make and model of your scanner, plus send me images of your barcodes I could help you further. Just contact me through my website listed below if you want further help.

  • 2. Gary E  |  March 18th, 2005 at 10:19 pm

    Assuming the input from each scan is placed in the currently selected cell, the following code would keep the correct cell selected for scanning into Columns 4 and 5.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Column
    Case 4
    Target.Offset(0, 1).Select
    Case 5
    Target.Offset(1, -1).Select
    End Select
    End Sub

    Change the values after Case to change the columns for special entry. The columns do not have to be adjacent, but if they are not, the offsets must be adjusted.

    This would work for scanning or typing values alternating product and serial numbers and would always show where the next entry would go.

    To place the code:

    Press Alt+F11 or right click on a worksheet tab and select View Code.

    Double click on the name of the worksheet where you want the code to work.

    Paste the code in the big window.

    Start typing values anywhere in the special columns and watch the selection move where where it should after each entry whether you use enter, tab, or any arrow key to exit the cell. And you can always select a different cell for your next entry.

  • 3. J  |  March 18th, 2005 at 10:51 pm

    Here’s what I was able to get away with to make this happen in Excel 2007

    - first I unlocked the cells I wanted to be able to edit by selecting columns A and B. (alt+h+o+e) protection tab and uncheck the locked field.
    - Then I made sure all other columns were locked by selecting all other columns. (alt+h+o+e) protection tab and check the locked field.
    - I then protected the sheet. (Alt+r+ps) in Excel 2007. I unselected the Select Locked cells choice at the top of the list.
    - Then I configured my spreadsheet to move right after pressing enter. (Alt+f+i) then Advanced and change direction to right.

    This will allow you to barcode the two fields then move to a new line.

Leave a Comment for I need help formatting excel for barcode scanning?

You must be logged in to post a comment.

Trackback this post  |  Subscribe to the comments via RSS Feed

Categories