How to create barcode in Excel spreadsheet using Microsoft Office 365 ProPlus

lisa.smith@sdcounty.ca.go · #1 11-18-2019, 10:30 AM

Just need to create barcode in spreadsheet using a combination of a couple other columns in the spreadsheet. Our company is using Microsoft Office 365 ProPlus. Thanks in advance for your help.

ArviLaanemets · #2 11-19-2019, 05:04 AM

I'm not sure how this will work as I made it and it was used some 10 or more years ago (MS Office 2010 and Windows XP probably), but at least it can give you some ideas.

I downloaded from some site barcode font (font file name was Code128b, in Format window it was named Code 128) and installed it on computer.

In Excel workbook module, I created a function

Code:

Public Function Code128X(parString As String) As String
    Dim varChar As String
    Dim varSum As Long
    Dim varCheck As Integer
    Dim varValue As Integer
    Dim i As Integer
    
    If Len(Trim(parString)) = 0 Then
        Code128X = ""
    Else
        ' as I did find out hard way, barcode readers don't read less than 3 character codes
        If Len(parString) < 3 Then
            parString = Right("   " & parString, 3)
        End If
        varSum = 104
        For i = 1 To Len(parString)
            varChar = Mid(parString, i, 1)
            varValue = Asc(varChar)
            If varValue = 128 Or varValue = 32 Then
                varValue = 0
            ElseIf varValue >= 33 And varValue <= 126 Then
                varValue = varValue - 32
            Else
                varValue = varValue - 50
            End If
            varSum = varSum + i * varValue
        Next i
        varSum = varSum Mod 103
        If varSum = 0 Then
        varCheck = 128
        ElseIf varSum >= 1 And varSum <= 94 Then
            varCheck = varSum + 32
        Else
            varCheck = varSum + 50
        End If
        Select Case varCheck
        Case 128
            varCheck = 8364
        Case 145
            varCheck = 8216
        Case 146
            varCheck = 8217
        Case 147
            varCheck = 8220
        Case 148
            varCheck = 8221
        Case 149
            varCheck = 8226
        Case 150
            varCheck = 8211
        Case 151
            varCheck = 8212
        Case 152
            varCheck = 732
        End Select
        Code128X = ChrW(353) & Replace(parString, " ", ChrW(8364)) & ChrW(varCheck) & ChrW(339)
    End If
End Function

I set the font for cell where I wanted the barcode to "Code 128", and entered there the formula

Code:

=Code128X($A$1)

(Assumed the value is read from cell $A$1).

NB! The rules for calculating check value and start/end characters depend on font you are using. It means with different barcode font you have to find those and adapt the function code.

Edit: Probably the font "Code 128" I see currently in my computer is not the one I used in past (Code128b), and is an later addition to default Windows fonts. This is probably the reason the old workbook can display only part of barcode lines currently.

Lewis-H · #3 11-29-2019, 07:34 AM

Have you tried doing this:
Click on the button "Insert" from the range of "ActiveX Controls" and then select "More controls" there. Select "ActiveBarcode" now and click "Ok". Now a barcode will be created inside your document. This will be a standard barcode of the EAN-128 type.
Hope this helps you!
Regards,
Lewis

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
create barcode from sequential field	optima	Word	1	05-24-2018 05:39 AM
Barcode scanner inventory check with excel 2016	laure	Excel	4	12-20-2016 07:10 PM
Would like to mail merge excel spreadsheet with word doc and create new page when a catagory changes	esherwood	Mail Merge	1	07-21-2014 09:55 PM
Create template that auto-fills from excel spreadsheet	m.buskirk5	Publisher	0	07-02-2012 03:09 PM
MICROSOFT barcode control -changing the barcode image in realtime	artner0112	Excel	1	12-18-2011 09:33 AM