将多个条形码扫描到单个Excel单元格中，每个条形码分别在一行上 [英] Scan multiple barcodes into a single Excel cell, each on a separate line

问题描述

图书馆有一个Excel表格，用作要借给其他图书馆的书籍的装箱单. 每行代表一个被包装的盒子.该行的单元格具有诸如收件人，货件号，箱号等信息.

他们希望在其中一列中扫描将要​​放入该框中的几本书的条形码的获取编号"，以使每个扫描的编号都将显示在该单元格的新行上. >

但是，当他们扫描条形码时，Excel会移至下一行，因为对扫描仪进行了编程，可以使用CR/LF终止扫描的数据.

他们无法对扫描仪进行重新编程，因为它们被用于其他应用程序.

我们如何使Excel能够:

If we're in column C
    When Excel detects CR/LF
        If the cell was not empty
            Append a new line to the contents of the cell
        Append the scanned data to the contents of the cell
        Stay in that cell ready for the next scan

然后，如果需要，他们可以按箭头键离开单元格.

-先进

解决方案

大多数人都知道，如果允许宏代码(包括像 Worksheet_Change 这样的事件宏)运行，它将杀死.Undo缓冲区.但没有意识到在更改单元格值之前可以捕获并使用.Undo缓冲区.换句话说，您可以获取新值，强制输入.Undo，然后使用换行符将新值附加到旧值，例如Chr(10)作为分隔符.

右键单击工作表的名称选项卡，然后选择View Code.当VBE打开时，将以下内容粘贴到标题为 Book1-Sheet1(代码) 的窗格中.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("C2:C10")) Is Nothing Then
        On Error GoTo Fìn
        Application.EnableEvents = False
        Dim vNew As Variant
        vNew = Target.Value
        Application.Undo
        If Not IsEmpty(Target) Then
            Target = Target.Value & Chr(10) & vNew
        Else
            Target = vNew
        End If
        'Target.Offset(1,0).Activate
    End If
Fìn:
    Application.EnableEvents = True
End Sub

您没有提供任何详细信息，所以我将条形码输入范围想象为C2:C10.您应该能够修改该部分，使其更接近条形码输入的范围.

当您认为输入范围正确时，请点击 Alt + Q 返回工作表.

注意事项:我没有可使用的条形码扫描仪，但是我过去曾经使用过它们，并且它们始终模仿键盘输入(取决于条形码扫描仪软件).这适用于键盘输入，因此请确保您的扫描仪软件将硬返回附加到扫描输出上.如果您想使用该方法，则有一条注释行可以将一个单元格向下移动.

A library has an Excel sheet that's used as a packing list for books to be loaned to other libraries. Each row represents a single box being packed. The cells of that row have info such as Recipient, Shipment number, Box number, etc.

In one of the columns they'd like to scan the barcoded "Acquisition Number" of each of the several books that will go in that box, such that each scanned number will appear on a new line in that cell.

But when they scan a barcode, Excel moves to the next row, since the scanner is programmed to terminate the scanned data with CR/LF.

They cannot reprogram the scanners, as they are used for other apps.

How can we get Excel to:

If we're in column C
    When Excel detects CR/LF
        If the cell was not empty
            Append a new line to the contents of the cell
        Append the scanned data to the contents of the cell
        Stay in that cell ready for the next scan

Then they can press an arrow key, if required, to get out of the cell.

-- adTHANKSvance

解决方案

Most people are aware that macro code (including event macros like Worksheet_Change) will kill the .Undo buffer if allowed to run through but are unaware that the .Undo buffer can be caught and used before cell values are changed. In other words, you can grab the new value, force an .Undo and then append the new value to the old with a line feed e.g. Chr(10) as a delimiter.

Right-click your worksheet's name tab and choose View Code. When the VBE opens up, paste the following into the pane titled something like Book1 - Sheet1 (Code).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("C2:C10")) Is Nothing Then
        On Error GoTo Fìn
        Application.EnableEvents = False
        Dim vNew As Variant
        vNew = Target.Value
        Application.Undo
        If Not IsEmpty(Target) Then
            Target = Target.Value & Chr(10) & vNew
        Else
            Target = vNew
        End If
        'Target.Offset(1,0).Activate
    End If
Fìn:
    Application.EnableEvents = True
End Sub

You didn't supply any specifics so I imagined the barcode input range as C2:C10. You should be able to modify that portion to a closer approximation of the range that the barcodes are entered into.

When you think you have the input range right, tap Alt+Q to return to your worksheet.

Caveat: I don't have a barcode scanner to work with but I have used them in the past and they have always closely imitated keyboard input (depending upon the bar code scanner software). This works for keyboard input so make sure your scanner software appends a hard return to the scan output. There is a commented line to move one cell down if you ever want to embrace that method.

这篇关于将多个条形码扫描到单个Excel单元格中，每个条形码分别在一行上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！