Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!
Barcode Scanner to Simple Excel spreadsheet to generate a trip report for 12 items.
Hi there ,
I need a simple solution to get me started on this task.
I want to scan 12 widgets that have 4 pieces of information.
Each piece is separated by a space in the scan and after the last piece there is a carriage return.
Batch#: (which is a 8 digit number)
Lot #: which is one letter and 4 numbers ( J1234 )
Date: 10OCT2015 ( DDMMMYYYY )
Widget # ( up to 500 )
On one scan all this data populates the first active cell in excel, I would like the data to separate into each cell in next column.
Then when the last scan is complete which normally is 12 skids, but could be less, it would be sent to printer to generate a trip report.
Thanks so much.
Should be fun.
Chris
I need a simple solution to get me started on this task.
I want to scan 12 widgets that have 4 pieces of information.
Each piece is separated by a space in the scan and after the last piece there is a carriage return.
Batch#: (which is a 8 digit number)
Lot #: which is one letter and 4 numbers ( J1234 )
Date: 10OCT2015 ( DDMMMYYYY )
Widget # ( up to 500 )
On one scan all this data populates the first active cell in excel, I would like the data to separate into each cell in next column.
Then when the last scan is complete which normally is 12 skids, but could be less, it would be sent to printer to generate a trip report.
Thanks so much.
Should be fun.
Chris
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
Thank you so much Saurabh
I have QR codes already, and I have scanners too, I just need help with how to get the scanned qr code into excel.
I know how to get it into excel but, I need help with programing excel to generate a report after I make 12 scans.
Thanks so much.
Chris
I have QR codes already, and I have scanners too, I just need help with how to get the scanned qr code into excel.
I know how to get it into excel but, I need help with programing excel to generate a report after I make 12 scans.
Thanks so much.
Chris
I presume the text is stuffed into a single cell value and it seems to contain line separators like CR, LF or CRLF.
I would put this macro into the workbook (into a VBA module):
It would determine the line separator and return the given element.
That way you input the into a given cell and extract the parts as you like ... given
I would put this macro into the workbook (into a VBA module):
Function SplitMultiline(text As String, element As Integer) As Variant
On Error GoTo Fehler
Dim delim As String
delim = "#"
If InStr(1, text, vbLf) > 0 Then delim = vbLf
If InStr(1, text, vbCr) > 0 Then delim = vbCr
If InStr(1, text, vbCrLf) > 0 Then delim = vbCrLf
If delim = "#" Then GoTo Fehler
SplitMultiline = Split(text, vbLf)(element - 1)
GoTo Ende
Fehler:
SplitMultiline = CVErr(xlErrNum)
Ende:
End Function
It would determine the line separator and return the given element.
That way you input the into a given cell and extract the parts as you like ... given
Line1as cell content in A1, the formula
Line2
Line3
Line4
=SplitMultiline(A1;2)
Line2as result. Cells without a known delimiter or index out of range would return an Excel compatible error code.
When I had a simple ad-hoc scanning request, I used the Manatee barcode scanner app for Android on my Samsung tablet
https://play.google.com/store/apps/details?id=com.manateeworks.barcodescanners
This read the code that I wanted, and put it on the clipboard.
I could then Paste this into any preferred spreadsheet app on the tablet. I used WPS Office at the time, but it would work just as well with the Android version of Excel. I then emailed this into my work email address
If the question is more about how to split up a single string of data into the 4 areas required, that should be relatively straightforward using LEFT, MID and RIGHT functions if the data has consistent numbers of characters.
If it has varying lengths (such as if using single AND double digits for the day of the month, or month itself) for instance, you may need to use FIND to select an appropriate "marker" or delimiter character.
Can we get an example of the raw data, and perhaps preferred results to work with?
If the question is more to do with the automated submission after 12 entries, then it's macro-related... Again, an example would be helpful.
https://play.google.com/store/apps/details?id=com.manateeworks.barcodescanners
This read the code that I wanted, and put it on the clipboard.
I could then Paste this into any preferred spreadsheet app on the tablet. I used WPS Office at the time, but it would work just as well with the Android version of Excel. I then emailed this into my work email address
If the question is more about how to split up a single string of data into the 4 areas required, that should be relatively straightforward using LEFT, MID and RIGHT functions if the data has consistent numbers of characters.
If it has varying lengths (such as if using single AND double digits for the day of the month, or month itself) for instance, you may need to use FIND to select an appropriate "marker" or delimiter character.
Can we get an example of the raw data, and perhaps preferred results to work with?
If the question is more to do with the automated submission after 12 entries, then it's macro-related... Again, an example would be helpful.
Hi Dan,
This small project is moving right along, and I have made it over a few hurdles already.
We are using a BlueTooth wireless handled scanner. I was thinkning it would be great to have an ipad doing the scan then you could actually see a missed code or a double code on the ipad screen instead of waiting until you finish a bunch of scanning and go to the other side of the warehouse to check the computer. But at this time, we have what we have and we are moving forward with this system.
I have been able to split the code easily using "Data Tab- Split to Columns". I am using a Macro button to split it, as I wanted to wait until all the scans were in and then split them all together. I guess if I knew how to code the VB to split it on the fly after each code, that would work, but I am not that techy. But for now this works just fine.
Now my next challenge is the next worksheet where I need excel to change where the next active cell engages after a enter key. (scan). When you hit enter the default placement of the cursor is on cell below, or one cell beside (excel prefs.). It's either or. I need excel to let do 2 cell entries side by side then the third entry to drop down.
I have attached the help files.
Thanks for your help.
Chris
This small project is moving right along, and I have made it over a few hurdles already.
We are using a BlueTooth wireless handled scanner. I was thinkning it would be great to have an ipad doing the scan then you could actually see a missed code or a double code on the ipad screen instead of waiting until you finish a bunch of scanning and go to the other side of the warehouse to check the computer. But at this time, we have what we have and we are moving forward with this system.
I have been able to split the code easily using "Data Tab- Split to Columns". I am using a Macro button to split it, as I wanted to wait until all the scans were in and then split them all together. I guess if I knew how to code the VB to split it on the fly after each code, that would work, but I am not that techy. But for now this works just fine.
Now my next challenge is the next worksheet where I need excel to change where the next active cell engages after a enter key. (scan). When you hit enter the default placement of the cursor is on cell below, or one cell beside (excel prefs.). It's either or. I need excel to let do 2 cell entries side by side then the third entry to drop down.
I have attached the help files.
Thanks for your help.
Chris
Here is the location for the Question
http://www.experts-exchange.com/videos/1198/Excel-After-Enter-Cursor-Location.html
Thanks
Was a little trickier to upload a video than I thought.
Chris
114-warehouse-location-master.xlsx
http://www.experts-exchange.com/videos/1198/Excel-After-Enter-Cursor-Location.html
Thanks
Was a little trickier to upload a video than I thought.
Chris
114-warehouse-location-master.xlsx
Hmmm .... sounds like a task for VBA to me.
For such a scanning task I'd implement some kind of user form - fired up i.e. with a button - that catches the text (i.e. as simulated keyboard input) to asimple text box. The user probably could inspect the caught text and click/tap a button. The form code the splits the input, and drops the parts i.e. in the last selected field, the next filed to the right and a field 7 columns right of the initial field. Afterwards it selects the field i.e. 2 rows below the last field selected and ends. Do you think of such a thing ?
For such a scanning task I'd implement some kind of user form - fired up i.e. with a button - that catches the text (i.e. as simulated keyboard input) to asimple text box. The user probably could inspect the caught text and click/tap a button. The form code the splits the input, and drops the parts i.e. in the last selected field, the next filed to the right and a field 7 columns right of the initial field. Afterwards it selects the field i.e. 2 rows below the last field selected and ends. Do you think of such a thing ?
Unfortunatly, We have a handheld scanner on a forklift in the warehouse scanning every single skid that comes in, and then scans the location barcode off the racking. The forklift operator does not have access to the computer that will be sitting on a desk on the other side of the warehouse.
I need Excel to be smart enough to know when it is scanning a 20 digit code and to put it into the first active cell.
I need excel to know that the smaller 7 digit code (second scan) to be placed on the same row as the first scan made with the 20 digits.
Does that kind make sense?
Thanks
Chris
I need Excel to be smart enough to know when it is scanning a 20 digit code and to put it into the first active cell.
I need excel to know that the smaller 7 digit code (second scan) to be placed on the same row as the first scan made with the 20 digits.
Does that kind make sense?
Thanks
Chris
Sure ... the VBA code needs to be a bit more complicated, but that would be managable, I think.
I'm right that the current config is capable to complete the input into a field (aka send the code vial keyboard emulation, completed with [enter]) ?
The code would run e.g. that way:
Initially the active cell is a common, designated one, i.e. A1. The VBA code catches the event that's fired when a cell in the worksheet is changed (for concept, see i.e. here), i.e.
In the processing part, you have access to the content of A1. Since the codes differ in length, you could decide what to do by just inserting something like
I would place that part into a separate worksheet (and store the codes in another) to minimize the amount of triggered events ... if there are lots of codes, many cells are changed at once and every change triggers the event. Even with the filter above, that would have impact on the performance ... up to the point where it cripples the application.
For improved usage, I't recommend to use some database (at least Access) and a custom application to fill it with the bar codes. Not much more complexity, and easier development. Could be exported to Excel afterwards, if needed, without problems.
I'm right that the current config is capable to complete the input into a field (aka send the code vial keyboard emulation, completed with [enter]) ?
The code would run e.g. that way:
Initially the active cell is a common, designated one, i.e. A1. The VBA code catches the event that's fired when a cell in the worksheet is changed (for concept, see i.e. here), i.e.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A1") Then
'processing code here
End If
End Sub
In the processing part, you have access to the content of A1. Since the codes differ in length, you could decide what to do by just inserting something like
if len(target.Value) =7 then
' process rack position
else if len(target.Value) =20 then
' process skid code
else
' process unknown code (error ?)
end if
I would place that part into a separate worksheet (and store the codes in another) to minimize the amount of triggered events ... if there are lots of codes, many cells are changed at once and every change triggers the event. Even with the filter above, that would have impact on the performance ... up to the point where it cripples the application.
For improved usage, I't recommend to use some database (at least Access) and a custom application to fill it with the bar codes. Not much more complexity, and easier development. Could be exported to Excel afterwards, if needed, without problems.
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialIt's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
Barcode Add-In--1
Barcode Add-in -->2
Bar codes without add-in and using excel and word..
Bar code add-in-->3
Saurabh...