+ Reply to Thread
Results 1 to 48 of 48

Barcode scanner to Excel

  1. #1
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Barcode scanner to Excel

    Hello all,
    I am new here and i will try to be succint.
    I am trying to use an Excel sheet for basic inventory .
    Also have a barcode scanner from Datalogic
    When inputing data to MS Word the scanner works great and goes to the next line after each scan.
    But in Excel it does not take the Enter command to pass to the next cell, instead all the codes are one after another in the same cell and only a key press ( Enter) will end the line.
    The barcode scanner is programmed with an Enter suffix and it works just fine in text editors.
    Could anyone help me with this issue because i really need to use it in Excel.
    Thank you in advance.

  2. #2
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    Welcome to the forum
    If you attach the text file to your reply will have a look for you. Probably just need to tell Excel which de-limiter to use.
    Click on reply, then GOAdvanced then look below for ManageAttachments

  3. #3
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Right now I am just testing so I do not have any document to attach.....just a blank excel sheet.
    The data I am trying to enter looks like this after the scan ( 160101.q4.003).
    Like I said in text editors it goes automatically to the next paragraph, so the (Enter) suffix is working.
    Thank you.

  4. #4
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    I do not want the excel file - I requested the TEXT file (ie the scanner output file)

  5. #5
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    I have attached the text file.
    Thanks
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    Thanks.
    What you have attached is a Word document, not a text file.
    Can you see if the scanner produces a text file (it may have extension .txt) - it would be better to import a text file directly into Excel (rather than needing to go via Word to convert)
    If there is a text file can you attach that to you reply


    Anyways, here is how to do it if there is no text file.
    Need to convert Word File to Text File and then import it to Excel

    1 Open file 160801.docx in Word
    2 click SAVE AS
    3 choose PLAIN TEXT (which will give extension .txt)
    4 Close Word
    5 Open Excel
    6 go to the sheet and cell where you want the data to start
    7 click on DATA tab
    8 click on GET EXTERNAL DATA
    9 click on FROM TEXT
    10 find the file and click on IMPORT
    11 the Text Import Wizard will open automatically
    12 select "Delimited" (usually defaults to this)
    13 click NEXT
    14 select "TAB"
    15 click on FINISH
    16 click on OK

    resultant file is attached
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    An alternative would be to open the file in word as before, select all the data, copy it to the clipboard and paste it into Excel

  8. #8
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Thank you so much for your help.
    Problem solved because I received a new scanner that works perfect in Excel.
    Still need some help for adding a button in Excel that inputs the same text every time it is clicked.
    The sheet I will create will be used by several people to input data with a scanner.
    The computer that is used does not have a keyboard, so i would like to add buttons that inputs the name of the person after using the scanner (one click to input name in selected cells)
    Thank you in advance.

  9. #9
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    I have attached a file to give you an idea of what exactly I am trying to accomplish and how i would like the book to look like.
    Any help or advice.....
    Thank you so much.
    https://drive.google.com/file/d/0BzP...ew?usp=sharing
    Last edited by codrinelu; 04-07-2016 at 05:06 PM.

  10. #10
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    Did you forget to attach your file?
    If a response has helped then please consider rating it by clicking on *Add Reputation below the post
    When your issue has been resolved don't forget to mark the thread SOLVED (click Thread Tools at top of thread)

  11. #11
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Now it is ok
    too large to attach.
    sorry

  12. #12
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    For the auto date insert I have the macro and also for the print button.
    Everything else is still in progress.

  13. #13
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    I looked at your file
    - it is already huge
    - it contains virtually no data
    - it has only 4 sheets

    So why so big?
    - 3 columns in all 4 sheets are formatted down to the last row (over 3million cells formatted X 4 sheets = 12 million)
    - one column in sheet "article1" contains a date in every row (over 1million dates!)

    Given the amount of data and macros contained in the file
    - it should currently be no bigger that 15kb
    - your file is over 6mb!

    Before the file becomes totally unworkable, I strongly suggest you either start again with a new file or amend the current file.
    - never format all the rows in a sheet - even if empty Excel ties up memory for all those rows
    - delete most of those dates
    Last edited by Kevin#; 04-08-2016 at 02:02 AM.

  14. #14
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    A new Template is available in attach if you want to take a look.
    Thank you.
    Attached Files Attached Files

  15. #15
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    Why are there 3 name buttons? ( Name1, Name2 and Name3)
    Why are there 3 identical sheets? (Article 1 to 3)
    How will the data in sheet "Rework" be created?

  16. #16
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    All data is created with a barcode scanner.
    This document will be used to facilitate the packing. In the end there will be 6 identical sheets for each type of article.
    Packing is done by several persons so each time they scan an article, or several, there must be a button to insert the name of the worker in the visa field.
    The rework sheet will be used by a controller that decides if the article is OK for packing or if it needs a rework.
    I wanted this to interact with the other sheets because it happened that an article was not conform but it was packed anyway.
    That way if I scan an article in the rework sheet, it will appear in red in the article sheet used by the worker, so it can put it away, pending rework.

  17. #17
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    That sounds good

    There will be a scan for every article packed and this will go into Article 1,2,3,4,5 or 6
    Are barcodes used only once or are they used every time the same article is packed?

    The reason I am asking, is that I am wondering how you are going to match the re-work items to the correct row in sheets Article 1-6 if the barcode appears more than once in the other sheet

  18. #18
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27
    Quote Originally Posted by Kevin# View Post
    That sounds good

    There will be a scan for every article packed and this will go into Article 1,2,3,4,5 or 6
    Are barcodes used only once or are they used every time the same article is packed?

    The reason I am asking, is that I am wondering how you are going to match the re-work items to the correct row in sheets Article 1-6 if the barcode appears more than once in the other sheet
    Article 1 is in fact the type of article.
    Each piece from the same type(article) has an unique id number so in article 1 for example there will be only unique numbers that never repeat even for the other types.
    Each piece we scan has an unique number

  19. #19
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    You could use something like this to conditionally format cells in columnA if barcode also appears in columnA sheet "Rework"

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    see attached workbook
    Attached Files Attached Files

  20. #20
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Works great
    Thank you so much.
    If you also have a solution for the name buttons, you would realy save my life.
    Thank you again for your time and work.

  21. #21
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    Let's run through the process with worker John

    1. John's scanner data is imported into the workbook
    2. Presumably it contains barcodes for anything up to 6 types of product
    3. The software puts the data in the correct worksheet ( could be 1 or up to 6 sheets)
    4. Presumably the date is added automatically in columnB in each sheet

    Now we want to add John's name to column C in all 6 sheets
    Is that correct?

  22. #22
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    John is scanning data into all the cells.
    When he completes all the scanning he selects the visa cells coresponding to the pieces he scanned and then he clicks the button to insert his name in all the visa cells.
    I need this because John does not have access to a keyboard to add his name to validate that he did the scan.
    He will select with the mouse all the cells he worked on and will click with the mouse on the button in Excel.

  23. #23
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    what i need is actually 3 letters to be entered in each selected cell everytime I click the name button.
    I said 3 letter because we abreviate names to 3 letters only. So John Stevens will add JST in the visa cells.

  24. #24
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    Thanks -
    Are we taking all the rows that have just been imported and putting the same (3 letter) name against them across all 6 sheets when we press the button?

  25. #25
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    No just the sheet in progress.
    If he enters data in article 1 sheet from a3 to a50 he selects with the mouse the cells from c3 to c50 and clicks the button to add his name in the selected cells.
    If after that he enters data in article 3 sheet he will do the same thing for this one.
    Thank you.

  26. #26
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    Create one macro like the one below for each name
    Assign that macro to the relevant button on each sheet
    Suggest you use the same naming convention for all the macros and then they will be listed together

    see attached workbook. Article 1 and Acticle 2 both have 2 working name buttons - one for JST and one for ABC
    Try adding data in both worksheets and then clicking one of the buttons

    (now signing off for today!)

    Please Login or Register  to view this content.
    End Sub
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    I don't have enough words to thank you for your work.
    Everything works as expected.
    Thank you so much.
    For the rework validation I will manually enter data at the end of each week, so if a piece was reworked will no longer appear in red in the article sheets, because I think is to much work to be done.
    Thank you,Thank you, Thank you.
    It is better than I have ever expected.

  28. #28
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    In sheet "Article 1", conditional formatting has been extended to include effect of entries in sheet "Rework" columns B and D
    Selected a different colour for column B so that you can see the difference - could just as easily be red

    You wanted to add "ok" to the cells if barcode value matched entry in column D - this is how this is achieved:

    - conditional formula for cells being formatted
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    - format applied under "Custom" in "Number Format"
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    (see attached workbook)
    Attached Files Attached Files
    Last edited by Kevin#; 04-09-2016 at 06:21 AM.

  29. #29
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Wow....
    It is great....
    I wanted the OK to be added in the rework A and Rework B columns so that the text doesn't match anymore with the columns in articles 1,2.... and it turns from red to normal....but I will work with what you sent me.......
    Again thank you so much for your time.
    You are great.

  30. #30
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    Easy mod - now it uses no colour if there is a match in column D.
    (Have not bothered with "ok" - if there is a value in column D you know it is "ok")

    Just so you understand, the sequence in which conditional formatting rules are applied determines which one is dominant. So
    - if there is a match in column D that formatting takes precedence over matches with columns A & B,
    - then if nothing in column D, if there is a match in column B, that takes precedence over match in column A
    Attached Files Attached Files

  31. #31
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Exactly what I wanted to look like.
    Great job....thank you so much.
    Now hopefully there will be much less errors in the packing department.
    Thank you for everything.

  32. #32
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    Glad all is working well for you and that you have everything you need.
    Please mark thread as "solved" ( top of thread - "Thread Tools")

  33. #33
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Sorry to bother you again,
    I have a new problem to solve.
    I would like a label to be printed each time I scan an article.......and the software for the label printing I am using can do that only if there is a trigger, like a text file for example.
    Can I create a .txt file after each entry in a cell.
    Thank you so much.

  34. #34
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    I forgot to tell you that I have already programmed the label software to go and search in my workbook the data to be printed, I just need the .txt file trigger.

  35. #35
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    Create a random text file
    This code creates a simple text file from whichever cells have been selected (Amend the file name and the path.)
    - basically it prints the "contents" of "selected" cells to a file
    - put it behind a button
    - click on the button and it will overwrite the old text file each time.

    I think that is what you asked for, but do not see how that triggers the label software.

    Please Login or Register  to view this content.

  36. #36
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Can it be done automatically? Without the need of a button? After each scan, excel goes to the next cell and that is the moment when I want a txt file to be created. The software I have can look into a folder and each time a txt file appears it launches a print command to the label printer and after that it deletes the txt file.

  37. #37
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Software name is bartender commander

  38. #38
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    What will it print?
    - I assume it prints what you have told it to print and not the text file that triggers the printing

    Triggering the generation of a text file automatically
    This can be done within a "change event" macro. But we need to be careful in our choice of what to use as the trigger to create the text file.

    When/how do the scanned barcodes get entered into the computer?
    - wirelessly at time scanned?
    OR
    - later in batches when operator goes to update his morning's work etc?
    (why I am asking - if PC is updated live we can run change event macro automatically after each one, but if done in a batch the input of several barcodes will be so quick that it will outpace the change event macro - and that may then not fire for every barcode)

    I am also wondering what would happen if we were to fire 10 text files in quick succession at the label folder - how would the software handle that?

  39. #39
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    I have managed to tell the software to go and search the data I am scanning so thi is what it will print. But for some reason I can tell it to prin only when a text doc appears regardless the text inside this file. The time between scans is usually from a few seconds 10-15 to a couple of minutes. I can instruct the operators to wait even more between scans if necessary.

  40. #40
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    This macro fires an instruction to create a text file when there is a change in column A
    ("CreateTextFileFromSelectedCells" is the name of the macro I posted earlier )


    Put this macro in the sheet modules (Article 1, Article 2 etc)
    Please Login or Register  to view this content.

  41. #41
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Thank you so much....everything works great.

  42. #42
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    I can not add reputation to your profile....tell me if you want me to open a new thread with the new question and then mark it as solved.......thank you again

  43. #43
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    No problem. Glad all is working as you want.

  44. #44
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Hi Kevin,
    one last request.......everything is working well......the trigger is sending the print command, but when printing it gets all the articles scanned in the respective column, instead of getting only the last one.
    Is there a way to export the contents of the cell to the txt file that is created?
    That way I can instruct the software to stop getting data from excel, and take it from the txt file instead.
    Thank you so much.

  45. #45
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    I am away from my PC for 10 days so cannot test anything. Replying from my tablet.
    Luckily, I think the file creation routine is already doing almost exactly what you want - it is printing the value of whichever cells are selected to the text file.
    So it should just be a case of getting VBA to select the correct cell to print to the text file.
    If we can work out which cell is selected after the scanned details have been input, then we can tell VBA to select a different cell relative to that cell, and that value will be written to the text file.

    Which cell do you want the text file to contain?
    Which cell's value does the text file currently contain? (Use Notepad to open it)

  46. #46
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Hello...hope you are back...
    The macro you sent me creates a .txt file after each entry in column A......what I need is to also put the data in column A (after each scan), into the .txt files created. The printing software deletes the .txt files after it collects the data that is contained inside. I only need to add the cell that contains the serial number after each scan. Something like.......if data entry into A1 to A.....then take new data and create a .txt file.
    Thank you so much......and sorry if i can not explain better.

  47. #47
    Registered User
    Join Date
    03-30-2016
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    27

    Re: Barcode scanner to Excel

    Hello...hope you are back...
    The macro you sent me creates a .txt file after each entry in column A......what I need is to also put the data in column A (after each scan), into the .txt files created. The printing software deletes the .txt files after it collects the data that is contained inside. I only need to add the cell that contains the serial number after each scan. Something like.......if data entry into A1 to A.....then take new data and create a .txt file.
    Thank you so much......and sorry if i can not explain better.

  48. #48
    Valued Forum Contributor
    Join Date
    01-03-2016
    Location
    Conwy, Wales
    MS-Off Ver
    2016
    Posts
    974

    Re: Barcode scanner to Excel

    Can you clarify
    1. "The macro you sent me creates a .txt file after each entry in column A......what I need is to also put the data in column A"
    If the data is already in column A why do you need to put it in column A?

    2." Something like.......if data entry into A1 to A.....then take new data and create a .txt file."
    Do you want a text file created of the NEW records added?
    How should the file be named? perhaps SheetName + date + time


    3. How will you use the text files? Or are they just for your audit trail?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using a Barcode Scanner to Input Data Into Excel
    By sjftcec in forum Excel General
    Replies: 7
    Last Post: 08-29-2019, 03:28 PM
  2. Urgent help needed with VBA in excel for barcode scanner worksheet
    By drowe005 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-02-2015, 10:31 PM
  3. Can Excel be used with a barcode scanner to track inventory?
    By polluxtroy12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-05-2013, 09:43 PM
  4. Using a barcode scanner with excel
    By Fred45 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-10-2013, 09:23 AM
  5. Trying to track inventory using a barcode scanner in Excel
    By ernht38 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-01-2013, 08:16 AM
  6. Barcode scanner in excel
    By amir8000 in forum Excel General
    Replies: 1
    Last Post: 06-03-2013, 08:30 PM
  7. Interfacing MS Excel with a BarCode Scanner
    By Big Jake in forum Excel General
    Replies: 1
    Last Post: 08-02-2009, 05:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1