ByteScout BarCode Generator SDK – Microsoft Excel – Batch Generate Barcodes In Cells with VBA

Home
/
Articles
/
ByteScout BarCode Generator SDK – Microsoft Excel – Batch Generate Barcodes In Cells with VBA

printable version:
ByteScout-Barcode-SDK-Microsoft-Excel-Batch-Generate-Barcodes-In-Cells-with-VBA.pdf

How to batch generate barcodes in cells with VBA in Microsoft Excel with ByteScout Barcode SDK

Tutorial on how to batch generate barcodes in cells with VBA in Microsoft Excel

With this source code sample you may quickly learn how to batch generate barcodes in cells with VBA in Microsoft Excel. ByteScout Barcode SDK is the fully featured library to generate barcodes. Supports QR Code, Code 39, Code 128, UPC, GS1, GS-128, PDF417, Datamatrix and many other barcode types. Includes various options for barcode generation to ensure output quality, add barcodes to new or existing pdf files and images. It can batch generate barcodes in cells with VBA in Microsoft Excel.

You will save a lot of time on writing and testing code as you may just take the Microsoft Excel code from ByteScout Barcode SDK for batch generate barcodes in cells with VBA below and use it in your application. In order to implement the functionality, you should copy and paste this code for Microsoft Excel below into your code editor with your app, compile and run your application. This basic programming language sample code for Microsoft Excel will do the whole work for you to batch generate barcodes in cells with VBA.

Download free trial version of ByteScout Barcode SDK from our website with this and other source code samples for Microsoft Excel.

On-demand (REST Web API) version:
Web API (on-demand version)

On-premise offline SDK for Windows:
60 Day Free Trial (on-premise)

BarcodeGenerationCode_VB.txt

      ' IMPORTANT: This demo uses VBA so if you have it disabled please temporary enable
' by going to Tools - Macro - Security.. and changing the security mode to ""Medium""
' to Ask if you want enable macro or not. Then close and reopen this Excel document

' You should have evaluation version of the ByteScout SDK installed to get it working - get it from https://bytescout.com

' If you are getting error message like
' "File or assembly named Bytescout SDK, or one of its dependencies, was not found"
' then please try the following:
'
' - Close Excel
' - (for Office 2003 only) download and install this hotfix from Microsoft:
' http://www.microsoft.com/downloads/details.aspx?FamilyId=1B0BFB35-C252-43CC-8A2A-6A64D6AC4670&displaylang=en
'
' and then try again!
'
' If you have any questions please contact us at https://bytescout.com/support/ or at support@bytescout.com
                            

'==============================================
'References used
'=================
'Bytescout Barcode SDK
'
' IMPORTANT:
' ==============================================================
'1) Add the ActiveX reference in Tools -> References
'2) Loop through the values from the Column A for which barcode has to be generated
'3) Parse the value to Bytescout Barcode Object to generate the barcode using QR Code barcode type.
'4) Save the generated Barcode Image
'5) Insert the Barcode Image in the Column B
'6) Repeat the steps 3 to 5 till the last Value in Column A
'
'==================================================================

Option Explicit

' declare function to get temporary folder (where we could save barcode images temporary)
Declare Function GetTempPath _
Lib "kernel32" Alias "GetTempPathA" _
(ByVal nBufferLength As Long, _
ByVal lpBuffer As String) As Long
 
' function to return path to temporary folder
Public Function fncGetTempPath() As String
    Dim PathLen As Long
    Dim WinTempDir As String
    Dim BufferLength As Long
    BufferLength = 260
    WinTempDir = Space(BufferLength)
    PathLen = GetTempPath(BufferLength, WinTempDir)
    If Not PathLen = 0 Then
        fncGetTempPath = Left(WinTempDir, PathLen)
    Else
        fncGetTempPath = CurDir()
    End If
End Function


Sub Barcode_Click()

'Fetch the Worksheet
Dim mySheet As Worksheet
Set mySheet = Worksheets(1)                 'Barcode_Data Sheet

'temp path to save the Barcode images
Dim filePath As String
filePath = fncGetTempPath()            'Change the Path But should end with Backslash( \ )

'Prepare the Bytescout Barcode Object
'====================================
Dim myBarcode As New Bytescout_BarCode.Barcode

myBarcode.RegistrationName = "demo"         'Change the name for full version
myBarcode.RegistrationKey = "demo"          'Change the key for full version

'Barcode Settings
myBarcode.Symbology = SymbologyType_QRCode  ' QR Code barcode, you may change to other barcode types like Code 39, Code 128 etc

' set barcode image quality resolution
myBarcode.ResolutionX = 300                 'Resolution higher than 250 is good for printing
myBarcode.ResolutionY = 300                 'Resolution higher than 250 is good for printing

myBarcode.DrawCaption = True                'Showing Barcode Captions in the Barcode Image
myBarcode.DrawCaptionFor2DBarcodes = True   ' show captions for 2D barcodes like QR Code

' first clean the B column from old images (if any)
Dim Sh As Shape
With mySheet
   For Each Sh In .Shapes
       If Not Application.Intersect(Sh.TopLeftCell, .Range("B1:B50")) Is Nothing Then
         If Sh.Type = msoPicture Then Sh.Delete
       End If
    Next Sh
End With

' now generate new barcodes and insert into cells in the column B
' Repeat the steps for each row from 2 to 6
Dim myVal As Integer

For myVal = 2 To 6                          'change the code to all rows with values
    'Parse the Value from the Column A to Bytescout Barcode Object
    myBarcode.Value = mySheet.Cells(myVal, 1).Text
    'Fit the barcode into 80X30 mm rectangle
    myBarcode.FitInto_3 80, 30, 4           '4 refers to units of measurement as millimeter
    'Save the barcode image to a file in temporary folder
    myBarcode.SaveImage filePath & "myBarcode" & myVal & ".png"
    
    'Insert the Barcode image to the Column B and resize them to fit the cell.
    '==========================================================================
    With mySheet.Pictures.Insert(filePath & "myBarcode" & myVal & ".png")
        .ShapeRange.LockAspectRatio = True ' lock aspect ratio
        .Left = mySheet.Cells(myVal, 2).Left + 1 ' set left
        .Top = mySheet.Cells(myVal, 2).Top + 1 ' set right
        .PrintObject = True ' allow printing this object
        .Placement = xlMove ' set placement mode to move but do not resize with the cell
        .ShapeRange.ScaleHeight 1, True ' set height scale to 1 (no scale)
        .ShapeRange.ScaleWidth 1, True ' set width scale to 1 (no scale)
    End With
    
    
Next myVal ' move to next cell in the column

' Release the Barcode Object.
Set myBarcode = Nothing

End Sub

ON-PREMISE OFFLINE SDK

60 Day Free Trial or Visit ByteScout Barcode SDK Home Page

Explore ByteScout Barcode SDK Documentation

Explore Samples

Sign Up for ByteScout Barcode SDK Online Training

ON-DEMAND REST WEB API

Get Your API Key

Explore Web API Docs

Explore Web API Samples

Reference Error – README.txt

      ' IMPORTANT: This demo uses VBA so if you have it disabled please temporary enable
' by going to Tools - Macro - Security.. and changing the security mode to ""Medium""
' to Ask if you want enable macro or not. Then close and reopen this Excel document

' You should have evaluation version of the ByteScout SDK installed to get it working - get it from https://bytescout.com

' If you are getting error message like
' "File or assembly named Bytescout SDK, or one of its dependencies, was not found"
' then please try the following:
'
' - Close Excel
' - (for Office 2003 only) download and install this hotfix from Microsoft:
' http://www.microsoft.com/downloads/details.aspx?FamilyId=1B0BFB35-C252-43CC-8A2A-6A64D6AC4670&displaylang=en
'
' and then try again!
'
' If you have any questions please contact us at https://bytescout.com/support/ or at support@bytescout.com