Barcode Generation Tutorial
for Excel
IDAutomation provides a variety of products to implement barcode generation
capability into Microsoft Excel for various documents, label printing, inventory
control and more. Following are some of the most common implementations of our
products.
Self-checking barcode fonts such as
Codabar (numbers) and
Code 39 (numbers and uppercase letters) are the easiest to use in Excel when
encoding numbers and/or uppercase letters. Self-checking fonts have checking code built-in
so that manual calculation of check characters is not required. If these self-checking
barcode fonts cannot be used, refer to the Excel Barcode
Integration Guide for proper product suggestions.
IDAutomation's
self-checking barcode fonts may be generated as a calculated field in
Excel. The following tutorial demonstrates how this can be accomplished.
- To create a proper barcode with
a self-checking barcode font, start and stop characters must be added to the beginning and ending
of the data being encoded. These start and stop characters are easily added to IDAutomation's
Codabar and Code 39 fonts with the asterisk * character.
- The start/stop character may be easily added to the beginning
and ending of the data being encoded with a simple Excel formula
of ="*" & CELL & "*" where CELL
is the cell of the data being encoded in the barcode.

- After the formula is entered in the cell, select the correct
font and a barcode should appear. IDAutomation fonts with
XS heights are specifically designed for Excel to automatically
adjusts to the correct column height. The width of the column may
need to be sized to contain the entire symbol.

- Print and scan the barcode to verify the correct data is
encoded. If a scanner is needed to verify barcodes, IDAutomation
recommends the
IDAutomation
USB Barcode Scanner.
Tabs and returns may be added with Code 39 fonts (in
extended39
mode) between cells by inserting "$I" (for a tab) or "$M" (for a
return.) For example, the following formula encodes a tab between cells
A1 and B1: =("*"&A1&"$I"&B1&"*")
Learn more about
combining
multiple cells into a single barcode.
Creating a Column of Barcodes in a
Spreadsheet
IDAutomation's barcode fonts provide an easy method of barcoding
an entire Excel column. Code 39 barcodes are created in an Excel spreadsheet
in this example, with the IDAutomationC39
font that is included in the
Code 39 Barcode Font Package. Codabar fonts
may also be used to create smaller numeric-only barcodes.
For fonts other than Codabar and Code 39, refer
to IDAutomation's
VBA Macro Tutorial for Microsoft Office or the
Barcode Integration
Guide for Excel.
- In this example, barcodes are created in Column B from data in Column. Enter the formula
="*"&A2&"*" in cell B2 to append the
required asterisks to the text from cell A2:

- Select the cell containing the formula and choose Edit -
Copy. Highlight the entire column by selecting the rectangle
labeled "B" at the top of the spreadsheet and choose Edit - Paste.
The formulas may take some time to re-calculate if the spreadsheet
is large.

- With the entire
column that contains the formula selected, choose the IDAutomationHC39S
or IDAutomationHC39XS font included in the Code 39 Font Advantage
Package, or an appropriate font of your choice. IDAutomation
fonts with XS heights are designed to automatically adjust to the
smallest format and automatically adjust to the correct column height.
The barcodes will then appear in the entire column.

- With the entire column that contains the formula still selected, choose the
appropriate point size. In this example, 12 points is used. With the entire
column that contains the formula highlighted, choose the center
text option so the barcode is in the center of the field. Size the
column so it is wide enough to contain the generated symbol.

- Print and scan the barcode(s) to verify the correct data is encoded. If a
scanner is needed to verify barcodes, consider the
IDAutomation USB Barcode Scanner which dependably scans Code 39 barcodes
printed as small as 6 points.
ActiveX Barcode Controls For Dynamic Barcode Cells
IDAutomation's ActiveX Barcode Controls may be used in Excel on Windows
systems. The purchased or Demo version may be dragged, dropped and
sized on a spreadsheet. This control can only be linked to one cell. If an entire column of cells
should be linked, consider using barcode fonts as an alternative.
- In Microsoft Excel, select View - Toolbars - Control Toolbox.
- On the tool box dialog, select the More Controls icon:

- Select the barcode control from the list of available
ActiveX Controls. Select the control that was installed starting
with "IDAutomation," and drop it onto the spreadsheet.

- Draw the image for the control to appear
in the spreadsheet like a graphic image.

- Size the ActiveX Control as necessary. To modify properties like barcode height and symbology type, right-click
on the control and select properties.

- To link a cell to the data that will be encoded in the barcode,
enter the cell location in the "LinkedCell" property of the control. To
link an entire column of cells into one barcode, consider using barcode fonts as
an alternative.

- You are finished! Exit design mode by selecting the design mode icon:
- Print and scan the barcode(s) to verify the correct data is
encoded. If a scanner is needed to verify barcodes, consider the
IDAutomation USB Barcode Scanner.
Note:
Design mode must be used to edit the properties of the control. If problems are
encountered when editing the control properties, select the Design Mode
icon to enable.
Linking To Excel or CSV With Barcode Label Software
IDAutomation
Barcode Label Software is an easy-to-use WYSIWYG barcode label
printing application that prints barcodes, text and graphics
on standard laser and inkjet printers, as well as thermal transfer and direct
thermal printers. This software easily links to columns in Microsoft Excel or a CSV
text file as a data source for a text or barcode object, which allows
printing of labels from data in Excel.
- Download and install the
IDAutomation Barcode Label Software.
- Select Tools - Data Sets - Link External Data.
- Select the data type and choose Search to find the file.
- After the source path is found, select the appropriate table
if applicable.
- Select Link Data and a portion of the linked
data will be visible on the screen.

- When the data is linked, select Close.
- To link a column of data to a barcode or text object, click
the down arrow next to Value Type and select External DataSource,
then click the source column to link the value. The example window
will show what is linked.

- Increment the label number in the design screen to verify the
data is linked.
- After verifying alignment, select All Rows Data Source as the option
to print.
- When linking to the CSV or text files, the format should be
as follows:
CSV: "Column1","Column2","Column3"
TEXT: Column1,Column2,Column3
- Print and scan the barcode(s) to verify the correct data is encoded. If a
scanner is needed to verify barcodes, consider one of our
USB Barcode Scanners.
|