如何在excel工作表中生成条码

Question

只有一个简短的问题，如何在excel工作表中生成条形码？已在单元格中指定条形码文本。

(不打算批量生成条形码，否则可以在MS Word中完成。)

Answer 1

选择写入条形码文本的范围：

​

​

然后运行以下脚本：

Sub INSERT_BARCODE()
    Const BarcodeWidth As Integer = 156
    Dim ws As Worksheet, WdApp
    Set ws = ActiveSheet
    Set WdApp = CreateObject("Word.Application")
    With WdApp.Documents.Add
        .PageSetup.RightMargin = .PageSetup.PageWidth - .PageSetup.LeftMargin - BarcodeWidth
        .Fields.Add(Range:=.Range, Type:=-1, Text:="DISPLAYBARCODE " & CStr(Selection.Value) & " CODE39 \d \t", PreserveFormatting:=False).Copy
    End With
    ws.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False, DisplayAsIcon:=False
    WdApp.Quit SaveChanges:=False
    Set WdApp = Nothing
End Sub

​

​

注意：

• 随心所欲地更改编码规则。本例使用CODE39规则。有关最适合条形码的详细信息，请参阅https://msdn.microsoft.com/en-us/library/hh745901(v=office.12).aspx details.
• Adjust BarcodeWidth interger。

BR~

Answer 2

这在很大程度上超出了您所需的范围，但您可以根据需要从中提取部分。

Sub Call_Barcode_Service()

Dim strResource As String
Dim strSize As String
Dim iHgt As Integer
Dim iWth As Integer
Dim iGap As Integer
Dim PictureGrab As String
Dim lngLastRow As Long

strSize = UCase(InputBox("How Big?", "Small, Medium or Large?", "L"))

Select Case strSize

    Case Is = "S"
        iWth = 150
        iHgt = 45
        iGap = 3


    Case Is = "M"
        iWth = 150
        iHgt = 60
        iGap = 4

    Case Is = "L"
        iWth = 240
        iHgt = 75
        iGap = 5

    Case Else
        iWth = 250
        iHgt = 75
        iGap = 5

End Select


Set sel = Selection.SpecialCells(xlTextValues)

Set news = Worksheets.Add()
news.Name = "Barcodes"
Set op = news.Range("A1")

For Each acc In sel
strResource = acc.Value

PictureGrab = "http://www.barcodesinc.com/generator/image.php?code=" & strResource & "&style=197&type=C128B&width=" & iWth & "&height=" & iHgt & "&xres=1&font=1"

Set sh = ActiveSheet.Shapes.AddShape(msoShapeRectangle, op.Left, op.Top, iWth, iHgt)

With sh

.Name = strResource
.Line.Visible = False
.Fill.UserPicture PictureGrab

End With

Set op = op.Offset(iGap + 1, 0).Range("A1")

Next

Range("G1").Select

End Sub

Answer 3

我对宏使用word：

Sub Macro1()
'
' Macro1 Macro
' Test Barcode
'
Dim codei As String
Dim codej As String

    For I = 1 To 1 '0
    For J = 1 To 2 '0
        codei = CStr(I)
        While Len(codei) < 2
           codei = "0" & codei
        Wend
        codej = CStr(J)
        While Len(codej) < 4
           codej = "0" & codej
        Wend
        codei = codei & codej
        Selection.Fields.Add Range:=Selection.Range, Type:=wdFieldEmpty, _
        PreserveFormatting:=False
    Selection.TypeText Text:="DisplayBarcode " & codei & " Code128 \t"
    Selection.TypeParagraph
    Selection.Fields.Update
    Next J
    Next I
End Sub